The development of DBMS stored procedures and triggers has been largely driven by DBMS vendors and the competitive dynamics of the database industry. Sybase’s initial introduction of stored procedures and triggers in SQL Server triggered a competitive response, and by the mid-1990s, many of the enterprise-class systems had added their own proprietary procedural extensions to SQL. Stored procedures were not a focus of the SQL2 standard, but became a part of the standardization agenda after the 1992 publication of the SQL2 standard. The work on stored procedure standards was split off from the broader object-oriented extensions that were proposed for SQL3, and was focused on a set of procedural extensions to the SQL2 language.
The result was a new part of the SQL standard, published in 1996 as SQL/Persistent Stored Modules (SQL/PSM), International Standard ISO/IEC 9075-4. The actual form of the standard specification is a collection of additions, edits, new paragraphs, and replacement paragraphs to the 1992 SQL2 standard (ISO/IEC 9075:1992). In addition to being a modification of the SQL2 standard, SQL/PSM was also drafted as a part of the planned follow-on standard, which was called SQL3 during its drafting. The development of the follow-on standard took longer than expected, but SQL/PSM eventually took its place as Part 4 of the SQL3 standard, officially known as SQL:1999. The SQL Call-Level Interface (CLI) standard, described in Chapter 19, was treated the same way; it is now Part 3 of the SQL:1999 standard. When the SQL:1999 standard was published, selected parts of SQL/PSM were moved to the core SQL/Foundation specification (Part 1), because they are also used by other parts of the standard.
The SQL/PSM standard published in 1996 addressed only stored procedures; it explicitly did not provide a specification of a trigger facility for the ISO SQL standard. The standardization of trigger functions was considered during the development of the SQL2 and SQL/PSM standards, but the standards groups determined that triggers were too closely tied to other object-oriented extensions proposed for SQL3. The SQL:1999 standard that resulted from the SQL3 work finally provided an ANSI/ISO standard trigger facility.
The publication of the SQL/PSM and SQL:1999 standards lagged the first commercial implementation of stored procedures and triggers by many years. By the time the standard was adopted, most enterprise DBMS vendors had responded to user enthusiasm and competitive pressure by introducing stored procedure and trigger capabilities in their products. Unlike some other SQL extensions where IBM’s clout and a DB2 implementation had set a de facto standard, the major DBMS vendors implemented stored procedures and triggers in different, proprietary ways, and in some cases, competed with one another based on unique features of their implementations. As a result, the ANSI/ISO standardization of stored procedures and triggers has had little impact on the DBMS market to date. It’s reasonable to expect that ANSI/ISO implementations will find their way into major DBMS products over time, but as a complement to, rather than a replacement for, the proprietary implementations.
1. The SQL/PSM Stored Procedures Standard
The capabilities specified in the SQL/PSM standard parallel the core features of the proprietary stored procedure capabilities of today’s DBMS systems. They include SQL language constructs to:
- Define and name procedures and functions written in the extended SQL language
- Invoke (call) a previously defined procedure or function
- Pass parameters to a called procedure or function, and obtain the results of its execution
- Declare and use local variables within the procedure or function
- Group a block of SQL statements together for execution
- Conditionally execute SQL statements (IF.. .THEN.. .ELSE)
- Repeatedly execute a group of SQL statements (looping)
The SQL/PSM standard specifies two types of SQL-invoked routines. A SQL-procedure is a routine that does not return a value. It is called with a CALL statement:
CALL ADD_CUST(‘XYZ Corporation’, 2137, 30000.00, 50000.00, 103, ‘Chicago’)
As with the proprietary stored procedure languages illustrated in the previous examples throughout this chapter, SQL/PSM stored procedures accept parameters passed via the CALL statement. SQL/PSM stored procedures can also pass data back to their caller via output parameters, again mirroring the capabilities of the proprietary stored procedure languages. SQL/PSM also supports combined input/output parameters, like some of the proprietary languages.
A SQL-function does return a value. It is called just like a built-in SQL function within a value expression:
SELECT COMPANY
FROM CUSTOMERS
WHERE GET_TOT_ORDS(CUST_NUM) > 10000.00
SQL/PSM restricts SQL-functions to only return a single value through the function-call mechanism. Output parameters and input/output parameters are not allowed in SQL-functions.
SQL routines are objects within the SQL-92 or SQL-99 database structure. SQL/PSM allows the creation of routines within a SQL-92 or SQL-99 schema (a schema-level routine) where it exists along with the tables, views, assertions, and other objects in the scheme. It also allows the creation of routines within a SQL2 module, which is the SQL procedure model carried forward from the SQL1 standard.
1.1. Creating a SQL Routine
Following the practice of most DBMS brands, the SQL/PSM standard uses the CREATE PROCEDURE and CREATE FUNCTION statements to specify the definitions of stored procedures and functions. Figure 20-18 shows simplified syntax for each of these statements. In addition to the capabilities shown in the figure, the standard provides a capability to define external stored procedures, specifying the language they are written in, whether they can or cannot read or modify data in the database, their calling conventions, and other characteristics.
1.2. Flow-of-Control Statements
The SQL/PSM standard specifies the common programming structures that are found in most stored procedure dialects to control the flow of execution. Figure 20-19 shows the conditional branching and looping syntax. Note that the SQL statement lists specified for each structure consist of a sequence of SQL statements, each ending with a semicolon. Thus, explicit block structures are not required for simple multistatement sequences that appear in an IF…THEN…ELSE statement or a LOOP statement. The looping structures provide a great deal of flexibility for loop processing. There are forms that place the test at the top of the loop and at the bottom of the loop, as well as a form that provides infinite looping and requires the explicit coding of a test to break loop execution. Each of the program control structures is explicitly terminated by an END flag that matches the type of structure, making programming debugging easier.
1.3. Cursor Operations
The SQL/PSM standard extends the cursor manipulation capabilities specified in the SQL2 standard for embedded SQL into SQL routines. The DECLARE CURSOR, OPEN, FETCH, and CLOSE statements retain their roles and functions. Instead of using application program host variables to supply parameter values and to receive retrieved data, SQL routine parameters and variables can be used for these functions.
The SQL/PSM standard does introduce one new cursor-controlled looping structure, shown in Figure 20-20. Like the similar structures in the Oracle and Informix dialects described in the “Cursor-Based Repetition” section earlier in this chapter, it combines the cursor definition and the OPEN, FETCH, and CLOSE statements into a single loop definition that also specifies the processing to be performed for each row of retrieved query results.
1.4. Block Structure
Figure 20-21 shows the block structure specified by the SQL/PSM standard. It is quite a comprehensive structure, providing the following capabilities:
- Labels the block of statements with a statement label
- Declares local variables for use within the block
- Declares local user-defined error conditions
- Declares cursors for queries to be executed within the block
- Declares handlers to process error conditions that arise
- Defines the sequence of SQL statements to be executed
These capabilities resemble some of those described earlier in the “Statement Blocks” section of this chapter for the Informix and Oracle dialect stored procedure dialects.
Local variables within SQL/PSM procedures and functions (actually, within statement blocks) are declared using the DECLARE statement. Values are assigned using the SET statement. Functions return a value using the RETURN statement. Here is a statement block that might appear within a stored function, with examples of these statements:
try_again:
begin
/* Declare some local variables */
declare msg_text varchar(40);
declare tot_amt decimal(16,2);
/* Get the order total */
set tot_amt = get_tot_ords();
if (tot_amt > 0)
then
return (tot_amt);
else
return (0.00);
end if
end try_again
1.5. Error Handling
The block structure specified by the SQL/PSM standard provides fairly comprehensive support for error handling. The standard specifies predefined conditions that can be detected and handled, including:
- SQLWARNING. One of the warning conditions specified in the SQL2 standard
- NOT FOUND. The condition that normally occurs when the end of a set of query results is reached with a FETCH statement
- SQLSTATE values. A test for specific SQLSTATE error codes
- User-defined conditions. A condition named by the stored procedure
Conditions are typically defined in terms of SQLSTATE values. Rather than using numerical SQLSTATE codes, you can assign the condition a symbolic name. You can also specify your own user-defined condition:
declare bad_err condition for sqlstate ‘12345’;
declare my_err condition;
Once the condition has been defined, you can force the condition to occur through the execution of a SQL routine with the SIGNAL statement:
signal bad_err;
signal sqlstate ‘12345’;
To handle error conditions that may arise, SQL/PSM allows you to declare a condition handler. The declaration specifies the list of conditions that are to be handled and the action to be taken. It also specifies the type of condition handling. The types differ in what happens to the flow of control after the handler is finished with its work:
- CONTINUE type. After the condition handler completes its work, control returns to the next statement following the one that caused the condition. That is, execution continues with the next statement.
- EXIT type. After the condition handler completes its work, control returns to the end of the statement block containing the statement that caused the condition. That is, execution effectively exits the block.
- UNDO type. After the condition handler completes its work, all modifications are undone to data in the database caused by statements within the same statement block as the statement causing the error. The effect is the same as if a transaction had been initiated at the beginning of the statement block and was being rolled back.
Here are some examples that show the structure of the handler definition:
/* Handle SQL warnings here, then continue */
declare continue handler for sqlwarning
call my_warn_routine();
/* Handle severe errors by undoing effects */
declare undo handler for user_disaster
begin
/* Do disaster cleanup here */
…
end;
Error handling can get quite complex, and it’s possible for errors to arise during the execution of the handler routine itself. To avoid infinite recursion on errors, the normal condition signaling does not apply during the execution of a condition handler. The standard allows you to override this restriction with the RESIGNAL statement. It operates just like the SIGNAL statement, but is used exclusively within condition- handler routines.
1.5. Routine Name Overloading
The SQL/PSM standard permits overloading of stored procedure and function names. Overloading is a common attribute in object-oriented systems and is a way to make stored routines more flexible in handling a wide variety of data types and situations. Using the overloading capability, several different routines can be given the same routine name. The multiple routines defined with the same name must differ from one another in the number of parameters that they accept or in the data types of the individual parameters. For example, you might define these three stored functions:
create function combo(a, b)
a integer;
b integer;
returns integer;
as return (a+b)
create function combo(a, b, c)
a integer;
b integer;
c integer;
returns integer;
as return (a+b+c)
create procedure combo(a, b)
a varchar(255);
b varchar(255);
returns varchar(255);
as return (a || b)
The first COMBO function combines two integers by adding them and returns the sum. The second COMBO function combines three integers the same way. The third COMBO function combines two character strings by concatenating them. The standard allows both of these functions named COMBO to be defined at the same time within the database. When the DBMS encounters a reference to the COMBO function, it examines the number of arguments in the reference and their data types, and determines which version of the COMBO function to call. Thus, the overloading capability allows a SQL programmer to create a family of routines that logically perform the same function and have the same name, even though the specifics of their usage for different data types is different. In object-oriented terms, overloading is sometimes called polymorphism, meaning literally that the same function can take many different forms.
To simplify the management of a family of routines that share an overloaded name, the SQL/PSM standard has the concept of a specific name: a second name that is assigned to the routine that is unique within the database schema or module. It uniquely identifies a specific routine. The specific name is used to drop the routine, and it is reflected in the Information Schema views that describe stored routines. The specific name is not used to call the routine; that would defeat the primary purpose of the overloaded routine name. Support for specific names or some similar mechanism is a practical requirement for any system that permits overloading or polymorphism for objects and provides a capability to manage them by dropping or changing their
definitions, since the system must be able to determine which specific object is being modified.
1.6. External Stored Procedures
The bulk of the SQL/PSM standard is concerned with the extensions to the SQL language that are used to define SQL-procedures and SQL-functions. Note, however, that the method used to invoke a SQL-procedure (the CALL statement) or a SQL-function (a reference to the function by name within a SQL statement) are not particular to procedures defined in the SQL language. In fact, the SQL/PSM standard provides for external stored procedures and functions, written in some other programming language such as C or Pascal. For external procedures, the CREATE PROCEDURE and CREATE FUNCTION statements are still used to define the procedure to the DBMS, specifying its name and the parameters that it accepts or returns. A special clause of the CREATE statement specifies the language in which the stored procedure or function is written, so that the DBMS may perform the appropriate conversion of data types and call the routine appropriately.
1.7. Other Stored Procedure Capabilities
The SQL/PSM standard treats SQL-procedures and SQL-functions as managed objects within the database, using extensions to the SQL statements used to manage other objects. You use a variation of the DROP statement to delete routines when they are no longer needed, and a variation of the ALTER statement to change the definition of a function or procedure. The SQL2 permissions mechanism is similarly extended with additional privileges. The EXECUTE privilege gives a user the ability to execute a stored procedure or function. It is managed by the GRANT and REVOKE statements in the same manner as other database privileges.
Because the stored routines defined by SQL/PSM are defined within SQL2 schemas, many routines can be defined in many different schemas throughout the database. When calling a stored routine, the routine name can be fully qualified to uniquely identify the routine within the database. The SQL/PSM standard provides an alternative method of searching for the definition of unqualified routine names through a new PATH concept.
The PATH is the sequence of schema names that should be searched to resolve a routine reference. A default PATH can be specified as part of the schema header in the CREATE SCHEMA statement. The PATH can also be dynamically modified during a SQL session through the SET PATH statement.
The SQL/PSM standard also lets the author of a stored procedure or function give the DBMS some hints about its operation to improve the efficiency of execution. One example is the ability to define a stored routine as DETERMINISTIC or NOT DETERMINISTIC. A DETERMINISTIC routine will always return the same results when it is called with the same parameter values. If the DBMS observes that a DETERMINISTIC routine is called repeatedly, it may choose to keep a copy of the results that it returns. Later, when the routine is called again, the DBMS does not need to actually execute the routine; it can simply return the same results that it returned the last time.
Another form of hint tells the DBMS whether an external stored procedure or function reads database contents and whether it modifies database contents. This not only allows the DBMS to optimize database access, but can also impose a security restriction on external routines from other sources. Other hints determine whether a SQL-function should be called if one of its parameters has a NULL value, and control how the DBMS selects the specific function or procedure to be executed when overloading is used.
2. Trigger Standards in SQL:1999
Triggers were addressed for standardization as part of the SQL3 effort, which led to the eventual publication of the SQL:1999 AnSi/ISO standard. By that time, many commercial DBMS products had already implemented triggers, and the standard synthesized the specific capabilities that had proven useful in practice. Like the commercial products, ANSI/ISO standard triggers are defined for a single, specific table. The standard permits trigger definitions only on tables, not on views.
The proprietary SQL Server, Oracle and Informix trigger mechanisms shown in the examples throughout this chapter provide a context for examining the ANSI/ISO standard mechanism. The standard does not provide any radical departure from the capabilities already described for the various DBMS products. Here is how the standard compares to them:
- Naming. The standard treats triggers as named objects within the database.
- Types. The standard provides INSERT, DELETE, and UPDATE triggers; UPDATE triggers can be associated with the update of a specific column or group of columns.
- Timing. The standard provides for triggers that operate before a database update statement or after the statement.
- Row-level or statement-level operation. The standard provides for both statement-level triggers (executed once per database-updating statement) and row-level triggers (executed repeatedly for each row of the table that is modified).
- Aliases. Access to the “before” and “after” values in a modified row or table is provided via an alias mechanism, like the table aliases used in the FROM clause.
You use the SQL:1999 CREATE TRIGGER statement, shown in Figure 20-22, to define a trigger. The statement clauses are familiar from the proprietary trigger examples throughout the earlier sections of this chapter.
One very useful extension provided by the standard is the WHEN clause that can be specified as part of a triggered action. The WHEN clause is optional, and it operates like a WHERE clause for determining whether a triggered action will be carried out. When DBMS executes the particular type of statement specified in the trigger definition, it evaluates the search condition specified in the WHEN clause. The form of the search condition is exactly like the search condition in a WHERE clause, and it will produce either a TRUE or FALSE result. The triggered action is carried out only if the result is TRUE.
To provide security for triggers, the SQL:1999 standard establishes a new TRIGGER privilege that may be granted for specific tables to specific users. With this privilege, a user may establish a trigger on the table. The owner of a table is always allowed to establish triggers on the table.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.