Chapter 20
Adding Procedural Capabilities with Persistent Stored Modules
In This Chapter
Tooling up compound statements with atomicity, cursors, variables, and conditions
Regulating the flow of control statements
Doing loops that do loops that do loops
Retrieving and using stored procedures and stored functions
Assigning privileges, creating stored modules, and putting stored modules to good use
Some of the leading practitioners of database technology have been working on the standards process for years. Even after a standard has been issued and accepted by the worldwide database community, progress toward the next standard doesn’t slow down. A seven-year gap separated the issuance of SQL-92 and the release of the first component of SQL:1999. During the intervening years, ANSI and ISO issued an addendum to SQL-92, called SQL-92/PSM (Persistent Stored Modules). This addendum formed the basis for a part of SQL:1999 with the same name. SQL/PSM defines a number of statements that give SQL flow of control structures comparable to the flow of control structures available in full-featured programming languages. It enables you to use SQL to perform tasks that programmers previously were forced to use other tools for. Can you imagine what your life would have been like in the caveman times of 1992, when you’d have to repeatedly swap between SQL and its procedural host language just to do your work?
Compound Statements
Throughout this book, SQL is represented as a nonprocedural language that deals with data a set at a time rather than a record at a time. With the addition of the facilities covered in this chapter, however, this statement is not as true as it used to be. Although SQL still deals with data a set at a time, it is becoming more procedural.
Archaic SQL (defined by SQL-92) doesn’t follow the procedural model — where one instruction follows another in a sequence to produce a desired result — so early SQL statements were standalone entities, perhaps embedded in a C++ or Visual Basic program. With these early versions of SQL, posing a query or performing other operations by executing a series of SQL statements was discouraged because these complicated activities resulted in a performance penalty in the form of network traffic. SQL:1999 and all following versions allow compound statements, made up of individual SQL statements that execute as a unit, easing network congestion.
All the statements included in a compound statement are enclosed between a BEGIN keyword at the beginning of the statement and an END keyword at the end of the statement. For example, to insert data into multiple related tables, you use syntax similar to the following:
void main {
EXEC SQL
BEGIN
INSERT INTO students (StudentID, Fname, Lname)
VALUES (:sid, :sfname, :slname) ;
INSERT INTO roster (ClassID, Class, StudentID)
VALUES (:cid, :cname, :sid) ;
INSERT INTO receivable (StudentID, Class, Fee)
VALUES (:sid, :cname, :cfee)
END ;
/* Check SQLSTATE for errors */
}
This little fragment from a C program includes an embedded compound SQL statement. The comment about SQLSTATE deals with error handling. If the compound statement doesn't execute successfully, an error code is placed in the status parameter SQLSTATE. Of course, placing a comment after the END keyword doesn't correct the error. The comment is placed there simply to remind you that in a real program, error-handling code belongs in that spot. (I discuss error handling in detail in Chapter 21.)
Atomicity
Compound statements introduce a possibility for error that you don’t face when you construct simple SQL statements. A simple SQL statement either completes successfully or doesn’t, and if it doesn’t complete successfully, the database is unchanged. This is not necessarily the case when a compound statement creates an error.
Consider the example in the preceding section. What if the INSERT to the STUDENTS table and the INSERT to the ROSTER table both took place, but because of interference from another user, the INSERT to the RECEIVABLE table failed? A student would be registered for a class but would not be billed. This kind of error can be hard on a university's finances.
The concept that is missing in this scenario is atomicity. An atomic statement is indivisible — it either executes completely or not at all. Simple SQL statements are atomic by nature, but compound SQL statements are not. However, you can make a compound SQL statement atomic by specifying it as such. In the following example, the compound SQL statement is safe by introducing atomicity:
void main {
EXEC SQL
BEGIN ATOMIC
INSERT INTO students (StudentID, Fname, Lname)
VALUES (:sid, :sfname, :slname) ;
INSERT INTO roster (ClassID, Class, StudentID)
VALUES (:cid, :cname, :sid) ;
INSERT INTO receivable (StudentID, Class, Fee)
VALUES (:sid, :cname, :cfee)
END ;
/* Check SQLSTATE for errors */
}
By adding the keyword ATOMIC after the keyword BEGIN, you ensure that either the entire statement executes, or — if an error occurs — the entire statement rolls back, leaving the database in the state it was in before the statement began executing. Atomicity is discussed in detail in Chapter 15 in the course of the discussion of transactions.
You can find out whether a statement executed successfully. Read the section “Conditions,” later in this chapter, for more information.
Variables
Full computer languages such as C and BASIC have always offered variables, but SQL didn’t offer them until the introduction of SQL/PSM. A variable is a symbol that takes on a value of any given data type. Within a compound statement, you can declare a variable, assign it a value, and use it in a compound statement.
After you exit a compound statement, all the variables declared within it are destroyed. Thus, variables in SQL are local to the compound statement within which they are declared.
Here’s an example:
BEGIN
DECLARE prezpay NUMERIC ;
SELECT salary
INTO prezpay
FROM EMPLOYEE
WHERE jobtitle = 'president' ;
END;
Cursors
You can declare a cursor within a compound statement. You use cursors to process a table’s data one row at a time. (See Chapter 19 for details.) Within a compound statement, you can declare a cursor, use it, and then forget it because the cursor is destroyed when you exit the compound statement. Here’s an example of this usage:
BEGIN
DECLARE ipocandidate CHARACTER(30) ;
DECLARE cursor1 CURSOR FOR
SELECT company
FROM biotech ;
OPEN CURSOR1 ;
FETCH cursor1 INTO ipocandidate ;
CLOSE cursor1 ;
END;
Conditions
When people say that a person has a condition, they usually mean that something is wrong with that person — he or she is sick or injured. People usually don’t bother to mention that a person is in good condition; rather, they talk about people who are in serious condition or, even worse, in critical condition. This idea is similar to the way programmers talk about the condition of an SQL statement. The execution of an SQL statement leads to a successful result, a questionable result, or an outright erroneous result. Each of these possible results corresponds to a condition.
Every time an SQL statement executes, the database server places a value into the status parameter SQLSTATE. SQLSTATE is a five-character field. The value that is placed into SQLSTATE indicates whether the preceding SQL statement executed successfully. If it did not execute successfully, the value of SQLSTATE provides some information about the error.
The first two of the five characters of SQLSTATE (the class value) give you the major news as to whether the preceding SQL statement executed successfully, returned a result that may or may not have been successful, or produced an error. Table 20-1 shows the four possible results.
Table 20-1 SQLSTATE Class Values
|
Class |
Description |
Details |
|
|
Successful completion |
The statement executed successfully. |
|
|
Warning |
Something unusual happened during the execution of the statement, but the DBMS can’t tell whether there was an error. Check the preceding SQL statement carefully to ensure that it is operating correctly. |
|
|
Not Found |
No data was returned as a result of the execution of the statement. This may or may not be good news, depending on what you were trying to do with the statement. You may be hoping for an empty result table. |
|
Other |
Exception |
The two characters of the class code, plus the three characters of the subclass code, comprise the five characters of |
Handling conditions
You can have your program look at SQLSTATE after the execution of every SQL statement. What do you do with the knowledge that you gain?
If you find a class code of 00, you probably don't want to do anything. You want execution to proceed as you originally planned.
If you find a class code of 01 or 02, you may want to take special action. If you expected the "Warning" or "Not Found" indication, then you probably want to let execution proceed. If you didn't expect either of these class codes, then you probably want to have execution branch to a procedure that is specifically designed to handle the unexpected, but not totally unanticipated, warning or not found result.
If you receive any other class code, something is wrong. You should branch to an exception-handling procedure. Which procedure you choose to branch to depends on the contents of the three subclass characters, as well as the two class characters of SQLSTATE. If multiple different exceptions are possible, there should be an exception-handling procedure for each one because different exceptions often require different responses. You may be able to correct some errors or find workarounds. Other errors may be fatal; no one will die, but you may end up having to terminate the application.
Handler declarations
You can put a condition handler within a compound statement. To create a condition handler, you must first declare the condition that it will handle. The condition declared can be some sort of exception, or it can just be something that’s true. Table 20-2 lists the possible conditions and includes a brief description of what causes each type of condition.
Table 20-2 Conditions That May Be Specified in a Condition Handler
|
Condition |
Description |
|
|
Specific |
|
|
|
|
|
|
|
|
|
The following is an example of a condition declaration:
BEGIN
DECLARE constraint_violation CONDITION
FOR SQLSTATE VALUE '23000' ;
END ;
This example is not realistic, because typically the SQL statement that may cause the condition to occur — as well as the handler that would be invoked if the condition did occur — would also be enclosed within the BEGIN…END structure.
Handler actions and handler effects
If a condition occurs that invokes a handler, the action specified by the handler executes. This action is an SQL statement, which can be a compound statement. If the handler action completes successfully, then the handler effect executes. The following is a list of the three possible handler effects:
CONTINUE: Continue execution immediately after the statement that caused the handler to be invoked.
EXIT: Continue execution after the compound statement that contains the handler.
UNDO: Undo the work of the previous statements in the compound statement and then continue execution after the statement that contains the handler.
If the handler can correct whatever problem invoked the handler, then the CONTINUE effect may be appropriate. The EXIT effect may be appropriate if the handler didn't fix the problem, but the changes made to the compound statement do not need to be undone. The UNDO effect is appropriate if you want to return the database to the state it was in before the compound statement started execution. Consider the following example:
BEGIN ATOMIC
DECLARE constraint_violation CONDITION
FOR SQLSTATE VALUE '23000' ;
DECLARE UNDO HANDLER
FOR constraint_violation
RESIGNAL ;
INSERT INTO students (StudentID, Fname, Lname)
VALUES (:sid, :sfname, :slname) ;
INSERT INTO roster (ClassID, Class, StudentID)
VALUES (:cid, :cname, :sid) ;
END ;
If either of the INSERT statements causes a constraint violation, such as trying to add a record with a primary key that duplicates a primary key already in the table, SQLSTATE assumes a value of '23000', thus setting the constraint_violation condition to a true value. This action causes the handler to UNDO any changes that have been made to any tables by either INSERT command. The RESIGNAL statement transfers control back to the procedure that called the currently executing procedure.
If both INSERT statements execute successfully, execution continues with the statement following the END keyword.
The ATOMIC keyword is mandatory whenever a handler's effect is UNDO. This is not the case for handlers whose effect is either CONTINUE or EXIT.
Conditions that aren’t handled
In the example in the preceding section, consider this possibility: What if an exception occurred that returned an SQLSTATE value other than '23000'? Something is definitely wrong, but the exception handler that you coded can't handle it. What happens now?
Because the current procedure doesn't know what to do, a RESIGNAL occurs. This bumps the problem up to the next higher level of control. If the problem isn't handled there, it continues to be elevated to higher levels until either it is handled or it causes an error condition in the main application.
The idea that I want to emphasize here is that if you write an SQL statement that may cause exceptions, then you should write exception handlers for all such possible exceptions. If you don’t, you will have more difficulty isolating the source of a problem when it inevitably occurs.
Assignment
With SQL/PSM, SQL gained a function that even the lowliest procedural languages have had since their inception: the ability to assign a value to a variable. Essentially, an assignment statement takes the following form:
SET target = source ;
In this usage, target is a variable name, and source is an expression. Several examples include the following:
SET vfname = 'Joss' ;
SET varea = 3.1416 * :radius * :radius ;
SET vWIMPmass = NULL ;
Flow of Control Statements
Since its original formulation in the SQL-86 standard, one of the main drawbacks that has prevented people from using SQL in a procedural manner has been its lack of flow of control statements. Until SQL/PSM was included in the SQL standard, you couldn’t branch out of a strict sequential order of execution without reverting to a host language like C or BASIC. SQL/PSM introduces the traditional flow of control structures that other languages provide, thus allowing SQL programs to perform needed functions without switching back and forth between languages.
IF…THEN…ELSE…END IF
The most basic flow of control statement is the IF…THEN…ELSE…END IF statement. This statement, roughly translated from computerese, means IF a condition is true, then execute the statements following the THEN keyword. Otherwise, execute the statements following the ELSE keyword. For example:
IF
vfname = 'Joss'
THEN
UPDATE students
SET Fname = 'Joss'
WHERE StudentID = 314159 ;
ELSE
DELETE FROM students
WHERE StudentID = 314159 ;
END IF
In this example, if the variable vfname contains the value 'Joss', then the record for student 314159 is updated with 'Joss' in the Fname field. If the variable vfname contains any value other than 'Joss', then the record for student 314159 is deleted from the STUDENTS table.
The IF…THEN…ELSE…END IF statement is great if you want to choose one of two actions based on the value of a condition. Often, however, you want to make a selection from more than two choices. At such times, you should probably use a CASE statement.
CASE…END CASE
CASE statements come in two forms: the simple CASE statement and the searched CASE statement. Both kinds allow you to take different execution paths based on the values of conditions.
Simple CASE statement
A simple CASE statement evaluates a single condition. Based on the value of that condition, execution may take one of several branches. For example:
CASE vmajor
WHEN 'Computer Science'
THEN INSERT INTO geeks (StudentID, Fname, Lname)
VALUES (:sid, :sfname, :slname) ;
WHEN 'Sports Medicine'
THEN INSERT INTO jocks (StudentID, Fname, Lname)
VALUES (:sid, :sfname, :slname) ;
WHEN 'Philosophy'
THEN INSERT INTO skeptics (StudentID, Fname, Lname)
VALUES (:sid, :sfname, :slname) ;
ELSE INSERT INTO undeclared (StudentID, Fname, Lname)
VALUES (:sid, :sfname, :slname) ;
END CASE
The ELSE clause handles everything that doesn't fall into the explicitly named categories in the THEN clauses.
You don't need to use the ELSE clause — it's optional. However, if you don't include it, and the CASE statement's condition is not handled by any of the THEN clauses, SQL returns an exception.
Searched CASE statement
A searched CASE statement is similar to a simple CASE statement, but it evaluates multiple conditions rather than just one. For example:
CASE
WHEN vmajor
IN ('Computer Science', 'Electrical Engineering')
THEN INSERT INTO geeks (StudentID, Fname, Lname)
VALUES (:sid, :sfname, :slname) ;
WHEN vclub
IN ('Amateur Radio', 'Rocket', 'Computer')
THEN INSERT INTO geeks (StudentID, Fname, Lname)
VALUES (:sid, :sfname, :slname) ;
WHEN vmajor
IN ('Sports Medicine', 'Physical Education')
THEN INSERT into jocks (StudentID, Fname, Lname)
VALUES (:sid, :sfname, :slname) ;
ELSE
INSERT INTO skeptics (StudentID, Fname, Lname)
VALUES (:sid, :sfname, :slname) ;
END CASE
You avoid an exception by putting all students who are not geeks or jocks into the SKEPTICS table. Because not all nongeeks and nonjocks are skeptics, this may not be strictly accurate in all cases. If it isn't, you can always add a few more WHEN clauses.
LOOP…ENDLOOP
The LOOP statement allows you to execute a sequence of SQL statements multiple times. After the last SQL statement enclosed within the LOOP…ENDLOOP statement executes, control loops back to the first such statement and makes another pass through the enclosed statements. The syntax is as follows:
SET vcount = 0 ;
LOOP
SET vcount = vcount + 1 ;
INSERT INTO asteroid (AsteroidID)
VALUES (vcount) ;
END LOOP
This code fragment preloads your ASTEROID table with unique identifiers. You can fill in other details about the asteroids as you find them, based on what you see through your telescope when you discover them.
Notice the one little problem with the code fragment in the preceding example: It is an infinite loop. No provision is made for leaving the loop, so it will continue inserting rows into the ASTEROID table until the DBMS fills all available storage with ASTEROID table records. If you’re lucky, the DBMS will raise an exception at that time. If you’re unlucky, the system will merely crash.
For the LOOP statement to be useful, you need a way to exit loops before you raise an exception. That way is the LEAVE statement.
LEAVE
The LEAVE statement works just like you might expect it to work. When execution encounters a LEAVE statement embedded within a labeled statement, it proceeds to the next statement beyond the labeled statement. For example:
AsteroidPreload:
SET vcount = 0 ;
LOOP
SET vcount = vcount + 1 ;
IF vcount > 10000
THEN
LEAVE AsteroidPreload ;
END IF ;
INSERT INTO asteroid (AsteroidID)
VALUES (vcount) ;
END LOOP AsteroidPreload
The preceding code inserts 10,000 sequentially numbered records into the ASTEROID table and then passes out of the loop.
WHILE…DO…END WHILE
The WHILE statement provides another method of executing a series of SQL statements multiple times. While a designated condition is true, the WHILE loop continues to execute. When the condition becomes false, looping stops. For example:
AsteroidPreload2:
SET vcount = 0 ;
WHILE
vcount< 10000 DO
SET vcount = vcount + 1 ;
INSERT INTO asteroid (AsteroidID)
VALUES (vcount) ;
END WHILE AsteroidPreload2
This code does exactly the same thing that AsteroidPreload did in the preceding section. This is just another example of the often-cited fact that with SQL, you usually have multiple ways to accomplish any given task. Use whichever method you feel most comfortable with, assuming your implementation allows both.
REPEAT…UNTIL…END REPEAT
The REPEAT loop is very much like the WHILE loop, except that the condition is checked after the embedded statements execute rather than before. For example:
AsteroidPreload3:
SET vcount = 0 ;
REPEAT
SET vcount = vcount + 1 ;
INSERT INTO asteroid (AsteroidID)
VALUES (vcount) ;
UNTIL X = 10000
END REPEAT AsteroidPreload3
Although you can perform the same operation three different ways (with LOOP, WHILE, and REPEAT), you will encounter some instances when one of these structures is clearly better than the other two. Have all three methods in your bag of tricks so that when a situation like this arises you can decide which one is the best tool available for the situation.
FOR…DO…END FOR
The SQL FOR loop declares and opens a cursor, fetches the rows of the cursor, executes the body of the FOR statement once for each row, and then closes the cursor. This loop makes processing possible entirely within SQL, instead of switching out to a host language. If your implementation supports SQL FOR loops, you can use them as a simple alternative to the cursor processing described in Chapter 19. Here's an example:
FOR vcount AS Curs1 CURSOR FOR
SELECT AsteroidID FROM asteroid
DO
UPDATE asteroid SET Description = 'stony iron'
WHERE CURRENT OF Curs1 ;
END FOR
In this example, you update every row in the ASTEROID table by putting 'stony iron' into the Description field. This is a fast way to identify the compositions of asteroids, but the table may suffer some in the accuracy department. Perhaps you'd be better off checking the spectral signatures of the asteroids and then entering their types individually.
ITERATE
The ITERATE statement provides a way to change the flow of execution within an iterated SQL statement. The iterated SQL statements are LOOP, WHILE, REPEAT, and FOR. If the iteration condition of the iterated SQL statement is true or not specified, then the next iteration of the loop commences immediately after the ITERATE statement executes. If the iteration condition of the iterated SQL statement is false or unknown, then iteration ceases after the ITERATE statement executes. For example:
AsteroidPreload4:
SET vcount = 0 ;
WHILE
vcount< 10000 DO
SET vcount = vcount + 1 ;
INSERT INTO asteroid (AsteroidID)
VALUES (vcount) ;
ITERATE AsteroidPreload4 ;
SET vpreload = 'DONE' ;
END WHILE AsteroidPreload4
Execution loops back to the top of the WHILE statement immediately after the ITERATE statement each time through the loop until vcount equals 9999. On that iteration, vcount increments to 10000, the INSERT performs, the ITERATE statement ceases iteration, vpreload is set to 'DONE', and execution proceeds to the next statement after the loop.
Stored Procedures
Stored procedures reside in the database on the server rather than execute on the client — where all procedures were located before SQL/PSM. After you define a stored procedure, you can invoke it with a CALL statement. Keeping the procedure located on the server rather than on the client reduces network traffic, thus speeding performance. The only traffic that needs to pass from the client to the server is the CALL statement. You can create this procedure in the following manner:
EXEC SQL
CREATE PROCEDURE ChessMatchScore
( IN score CHAR (3),
OUT result CHAR (10) )
BEGIN ATOMIC
CASE score
WHEN '1-0' THEN
SET result = 'whitewins' ;
WHEN '0-1' THEN
SET result = 'blackwins' ;
ELSE
SET result = 'draw' ;
END CASE
END ;
After you have created a stored procedure like the one in this example, you can invoke it with a CALL statement similar to the following statement:
CALL ChessMatchScore ('1-0', :Outcome) ;
The first argumentis an input parameter that is fed to the ChessMatchScore procedure. The second argument is an embedded variable that accepts the value assigned to the output parameter that the ChessMatchScore procedure uses to return its result to the calling routine. In this case, it returns 'white wins'.
SQL:2011 has added a couple of enhancements to stored procedures. The first of these is the introduction of named arguments. Here’s the equivalent of the preceding call, with named arguments:
CALL ChessMatchScore (result => :Outcome,score =>'1-0'),
Because the arguments are named, they can be written in any order without a danger of them being confused.
The second enhancement added in SQL:2011 is the addition of default input arguments. You can specify a default argument for the input parameter. After you do that, you don't need to specify an input value in the CALL statement; the default value is assumed. (Of course, you would want to do this only if the default value were in fact the value you wanted to send to the procedure.)
Here’s an example of that usage:
EXEC SQL
CREATE PROCEDURE ChessMatchScore
( IN score CHAR (3)DEFAULT '1-0',
OUT result CHAR (10) )
BEGIN ATOMIC
CASE score
WHEN '1-0' THEN
SET result = 'whitewins' ;
WHEN '0-1' THEN
SET result = 'blackwins' ;
ELSE
SET result = 'draw' ;
END CASE
END ;
You can now call this procedure thusly with the default value:
CALL ChessMatchScore (:Outcome) ;
Of course, you would only want to do this if the default value was in fact the value you wanted to send to the procedure.
Stored Functions
A stored function is similar in many ways to a stored procedure. Collectively, the two are referred to as stored routines. They are different in several ways, including the way in which they are invoked. A stored procedure is invoked with a CALL statement, and a stored function is invoked with a function call, which can replace an argument of an SQL statement. The following is an example of a function definition, followed by an example of a call to that function:
CREATE FUNCTION PurchaseHistory (CustID)
RETURNS CHAR VARYING (200)
BEGIN
DECLARE purch CHAR VARYING (200)
DEFAULT '' ;
FOR x AS SELECT *
FROM transactions t
WHERE t.customerID = CustID
DO
IF a <>''
THEN SET purch = purch || ', ' ;
END IF ;
SET purch = purch || t.description ;
END FOR
RETURN purch ;
END ;
This function definition creates a comma-delimited list of purchases made by a customer that has a specified customer number, taken from the TRANSACTIONS table. The following UPDATE statement contains a function call to PurchaseHistory that inserts the latest purchase history for customer number 314259 into her record in the CUSTOMER table:
SET customerID = 314259 ;
UPDATE customer
SET history = PurchaseHistory (customerID)
WHERE customerID = 314259 ;
Privileges
I discuss the various privileges that you can grant to users in Chapter 14. The database owner can grant the following privileges to other users:
The right to DELETE rows from a table
The right to INSERT rows into a table
The right to UPDATE rows in a table
The right to create a table that REFERENCES another table
The right of USAGE on a domain
SQL/PSM adds one more privilege that can be granted to a user — the EXECUTE privilege. Here are two examples:
GRANT EXECUTE on ChessMatchScore to TournamentDirector ;
GRANT EXECUTE on PurchaseHistory to SalesManager ;
These statements allow the tournament director of the chess tournament to execute the ChessMatchScore procedure, and the sales manager of the company to execute the PurchaseHistory function. People lacking the EXECUTE privilege for a routine aren't able to use it.
Stored Modules
A stored module can contain multiple routines (procedures and/or functions) that can be invoked by SQL. Anyone who has the EXECUTE privilege for a module has access to all the routines in the module. Privileges on routines within a module can't be granted individually. The following is an example of a stored module:
CREATE MODULE mod1
PROCEDURE MatchScore
( IN score CHAR (3),
OUT result CHAR (10) )
BEGIN ATOMIC
CASE result
WHEN '1-0' THEN
SET result = 'whitewins' ;
WHEN '0-1' THEN
SET result = 'blackwins' ;
ELSE
SET result = 'draw' ;
END CASE
END ;
FUNCTION PurchaseHistory (CustID)
RETURNS CHAR VARYING (200)
BEGIN
DECLARE purch CHAR VARYING (200)
DEFAULT '' ;
FOR x AS SELECT *
FROM transactions t
WHERE t.customerID = CustID
DO
IF a <>''
THEN SET purch = purch || ', ' ;
END IF ;
SET purch = purch || t.description ;
END FOR
RETURN purch ;
END ;
END MODULE ;
The two routines in this module (a procedure and a function) don’t have much in common, but they don’t need to. You can gather related routines into a single module, or you can stick all the routines you’re likely to use into a single module, regardless of whether they have anything in common.