Chapter 14: Stored Procedures, Triggers, and User-Defined Functions
Chapter 14: Stored Procedures, Triggers, and User-Defined Functions
Overview
As was mentioned before, SQL is a nonprocedural programming language by definition. That means it lacks procedural constructs, specifically, the ability to perform operations in optionally named hierarchical logical blocks that can accept and return values, perform iterations, execute conditional statements, and so on.
| Note |
Most programming languages are procedural. A procedural program is a list of step-by-step instructions that tell the computer what to do; for example, repeatedly read user's input, multiply it by some predefined constant, and store the result in a database table. A procedural program can also have error handling — for example, if the value is nonnumeric, do not try to multiply it but rather display an error message; or multiply the input by different values depending on how large the input was. Also, instead of storing the result into a table a procedural program can pass it to a different program (subprogram) that in its order might perform more calculations and pass the results to yet another programming module, and so on. |
First RDBMS implementations did not have procedural language capabilities — all procedural database processing was done using embedded programming. All major procedural languages that were popular back then (C, COBOL, Pascal, etc.) had (and still have) special extensions (precompilers) that allow the programmer to embed SQL statements directly into programming language code. The work of precompiles is to translate SQL into appropriate language constructs that can be later compiled into binary code.
| Cross-References |
Embedded programming is discussed in Chapter 15. |
However, while relational databases became increasingly sophisticated and more internal control was delegated to RDBMS, the idea arose to store procedural programming modules inside RDBMS in compiled (binary) form. The problem was that ANSI/ISO standards specified no (or almost none) guidelines for such elements. As a result, each vendor implemented its own version of internal RDBMS procedural modules.
SQL99 added persistent stored routines (PSR) and triggers to SQL standards when all major RDBMS vendors already had their own procedural languages. Oracle's procedural SQL extension is called PL/SQL; MS SQL Server uses Transact-SQL; and DB2 UDB introduced its own version that does not seem to have an official name, but is sometimes referred to as T-SQL or IBM SQL.
| Note |
DB2 UDB did not have its own procedural SQL extension until version 7.1. Instead, it provided capabilities to write stored routines using your choice of C or COBOL and, later, Java. Java can also be used as an alternative to PL/SQL in Oracle to be used for writing procedural code. |
Even though the basic syntax elements of these three languages are similar, the advanced features differ significantly. It is impossible to cover all three syntaxes in depth in the scope of this book, so we are going to explain the common basic features and encourage you to refer to vendor-specific documentation for more details.
| Note |
PSR includes stored procedures, user-defined functions, and special constructs called modules that can contain procedures, functions, and shared variable declarations for them. Modules are known in Oracle as packages; they are not implemented in MS SQL Server and DB2 UDB and are not covered in this book. |
The two main forms of RDBMS procedural routines are stored procedures and triggers that embody two different procedural programming approaches — linear and event-driven, correspondingly. A user-defined function can be envisioned as a special case of a stored procedure, and a module is just a number of stored procedures and functions bundled together.
