Chapter 6
Understanding SQL’s Procedural Capabilities
IN THIS CHAPTER
Using embedded SQL statements
Working with compound statements
Creating flow of control statements
Working with stored procedures
Executing SQL statements with triggers
Taking advantage of stored functions
Granting privileges
Upping the stakes with stored modules
In its original incarnation, SQL was conceived as a data sublanguage, the only purpose of which was to interact with relational databases. It was considered acceptable to embed SQL statements within procedural language code written in some full-featured language to create a fully functional database application. For a long time, however, users wanted SQL to have procedural capabilities so that there would be less need to switch back and forth between SQL and some other language in data-driven applications. To solve this problem, vendors started putting procedural capabilities in their implementations of SQL. These nonstandard extensions to the language ended up inhibiting cross-platform portability until several procedural capabilities were standardized with a new section of the ANSI/ISO standard in 1996. That new section is called Persistent Stored Modules (SQL/PSM), although it covers quite a few things in addition to stored modules.
Embedding SQL Statements in Your Code
In Book 5, Chapter 3, I discuss embedding SQL statements in applications written in one of several procedural languages. Even with the new procedural capabilities that were added to SQL with SQL/PSM, embedding is still necessary, but switches between languages are much less frequent. In its current version (SQL:2016), the ANSI/ISO SQL standard still describes a language that isn’t computationally complete.
Introducing Compound Statements
SQL was originally conceived 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 isn’t as true as it used to be. SQL has become more procedural, although it still deals with data a set at a time. Because classic SQL (that defined by SQL-92) doesn’t follow the procedural model — one instruction follows another in a sequence to produce a desired result — early SQL statements were stand-alone entities, perhaps embedded in a C++ or Visual Basic program. With these early versions of SQL, users typically didn’t pose a query or perform some other operation by executing a series of SQL statements. If users did execute such a series of statements, they suffered a performance penalty. Every SQL statement executed requires a message to be sent from the client where the user is located to the server where the database is located; then a response must be sent in the reverse direction. This network traffic slows operations as the network becomes congested.
SQL:1999 and all following versions allow compound statements, made up of individual SQL statements that execute as a unit. This capability eases network congestion, because all the individual SQL statements in the compound statement are sent to the server as a unit and executed as a unit, and a single response is sent back to the client.
All the statements included in a compound statement are enclosed between a BEGINEND keyword at the end of the statement. To insert data into multiple related tables, for example, 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. Placing a comment after the END keyword doesn’t correct any errors, however. The comment is placed there simply to remind you that in a real program, error-handling code belongs in that spot. Error handling is described in detail in Book 4, Chapter 4.
Atomicity
Compound statements introduce a possibility for error that doesn’t exist for simple SQL statements. A simple SQL statement either completes successfully or doesn’t. If it doesn’t complete successfully, the database is unchanged. This is not necessarily the case for a compound statement.
Consider the example in the preceding section. What if both the INSERT into the STUDENTS table and the INSERT into the ROSTER table took place, but because of interference from another user, the INSERT into the RECEIVABLE table failed? A student would be registered for a class but wouldn’t be billed. This kind of error can be hard on a university’s finances. The concept that’s 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. You can make a compound SQL statement atomic, however, by specifying it as such. In the following example, the compound SQL statement is made 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 can 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.
Variables
One feature that full computer languages such as C and BASIC offer that SQL didn’t offer until SQL/PSM is variables. Variables are symbols that can take on a value of any given data type. Within a compound statement, you can declare a variable and assign it a value. Then you can use the variable in the compound statement. When 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 Book 3, Chapter 5 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:
BEGIN
DECLARE ipocandidate CHAR(30) ;
DECLARE cursor1 CURSOR FOR
SELECT company
FROM biotech ;
OPEN CURSOR1 ;
FETCH cursor1 INTO ipocandidate ;
CLOSE cursor1 ;
END;
Assignment
With SQL/PSM, SQL finally gains 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 are
SET vfname = 'Brandon' ;
SET varea = 3.1416 * :radius * :radius ;
Following the Flow of Control Statements
Since its original formulation in the SQL-86 standard, one of the main drawbacks that 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 means that if a condition is true, the statements following the THEN keyword should be executed. Otherwise, the statements following the ELSE keyword should be executed. Here’s an example:
IF
vfname = 'Brandon'
THEN
UPDATE students
SET Fname = 'Brandon'
WHERE StudentID = 314159 ;
ELSE
DELETE FROM students
WHERE StudentID = 314159 ;
END IF
In this example, if the variable vfname contains the value Brandon, the record for student 314159 is updated with Brandon in the Fname field. If the variable vfname contains any value other than Brandon, the record for student 314159 is deleted from the students table.
The IF…THEN…ELSE…END IF statement is great if you want to take one of two actions, based on the value of a condition. Often, however, you want to make a selection among more than two choices. At such times, you probably should 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, as in this example:
CASE vmanufacturer
WHEN 'General Motors'
THEN INSERT INTO DOMESTIC (VIN, Make, Model)
VALUES (:vin, :make, :model) ;
WHEN 'Ford'
THEN INSERT INTO DOMESTIC (VIN, Make, Model)
VALUES (:vin, :make, :model) ;
WHEN 'Chrysler'
THEN INSERT INTO DOMESTIC (VIN, Make, Model)
VALUES (:vin, :make, :model) ;
WHEN 'Studebaker'
THEN INSERT INTO DOMESTIC (VIN, Make, Model)
VALUES (:vin, :make, :model) ;
ELSE INSERT INTO FOREIGN (VIN, Make, Model)
VALUES (:vin, :make, :model) ;
END CASE
The ELSE clause handles everything that doesn’t fall into the explicitly named categories in the THEN clauses.
The ELSE clause is optional, but if it isn’t included and the CASE statement’s condition isn’t 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. Here’s an example:
CASE
WHEN vmanufacturer IN ('General Motors','Ford')
THEN INSERT INTO DOMESTIC (VIN, Make, Model)
VALUES (:vin, :make, :model) ;
WHEN vmake IN ('Chrysler','Dodge','Plymouth')
THEN INSERT INTO DOMESTIC (VIN, Make, Model)
VALUES (:vin, :make, :model) ;
WHEN vmodel IN ('Avanti','Lark')
THEN INSERT INTO DOMESTIC (VIN, Make, Model)
VALUES (:vin, :make, :model) ;
ELSE INSERT INTO FOREIGN (VIN, Make, Model)
VALUES (:vin, :make, :model) ;
END CASE
You prevent an exception by putting all cars that aren’t domestic into the FOREIGN table. Because a car that doesn’t meet any of the stated conditions may still be domestic, this practice may not be strictly accurate in all cases. If it isn’t, you can always add another WHEN clause.
LOOP … END LOOP
The LOOP statement allows you to execute a sequence of SQL statements multiple times. After the last SQL statement enclosed within the LOOP…END LOOP 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’s an infinite loop. No provision is made for leaving the loop, so it will continue inserting rows into the asteroid table until the database management system (DBMS) fills all available storage with asteroid table records. If you’re lucky, the DBMS raises an exception at that time. If you’re unlucky, the system merely crashes.
For the LOOP statement to be useful, you need a way to exit loops before you raise an exception: the LEAVE statement.
LEAVE
The LEAVE statement works just like you might expect it to. When execution encounters a LEAVE statement embedded within a labeled statement, it proceeds to the next statement beyond the labeled statement, as in this 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 asteroids table and then passes out of the loop.
WHILE … DO … END WHILE
The WHILE statement provides another method for 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, as in this 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 does in the preceding section. This is just another example of the oft-cited fact that with SQL, you usually have multiple ways to accomplish any given task. Use whichever method you feel most comfortable with, assuming that your implementation allows it.
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. Here’s an example:
AsteroidPreload3:
SET vcount = 0 ;
REPEAT
SET vcount = vcount + 1 ;
INSERT INTO asteroid (AsteroidID)
VALUES (vcount) ;
UNTIL X = 10000
END REPEAT AsteroidPreload3
Although I perform the same operation three ways in the preceding examples (with LOOP, WHILE, and REPEAT), you will encounter some instances in which one of these structures is clearly better than the other two. It’s good to have all three methods in your bag of tricks so that when a situation like this arises, you can decide which tool is the best one 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 simple alternatives to the cursor processing described in Book 3, Chapter 5. 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' in the Description field. This method is a fast way to identify the compositions of asteroids, but the table may suffer some in the accuracy department. Some asteroids are carbonaceous chondrites, and others are nickel–iron. 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, 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, iteration ceases after the ITERATE statement executes. Here’s an 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.
Using 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 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 ForeignOrDomestic
( IN manufacturer CHAR (20),
OUT origin CHAR (8) )
BEGIN ATOMIC
CASE manufacturer
WHEN 'General Motors' THEN
SET origin = 'domestic' ;
WHEN 'Ford' THEN
SET origin = 'domestic' ;
WHEN 'Chrysler' THEN
SET origin = 'domestic' ;
WHEN 'Studebaker' THEN
SET origin = 'domestic' ;
ELSE
SET origin = 'foreign' ;
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 ForeignOrDomestic ('Toyota', origin) ;
The first argument is the input parameter fed to the ForeignOrDomestic procedure. The second argument is the output parameter that the procedure uses to return its result to the calling routine. In this case, it returns foreign.
Working with Triggers
Triggers are useful tools that you can use to execute SQL statements whenever certain changes are made in a database table. They’re analogous to actions that occur in event-driven programming in modern procedural languages. If a predefined change is made in a database table, that event causes an associated trigger to fire, which in turn causes an SQL statement or block of SQL statements to execute. The triggered statement could cause another trigger to fire, as well as performing its stated action. There’s no limit to the number of levels of nesting you can use for triggers.
One reason you may want to use a trigger is to create an audit trail. If a particular change is made in a table, you may want to record that fact in a log file somewhere. A trigger could cause an SQL statement to make a log entry. Another application of a trigger might be to maintain consistency among tables in a database. A particular change in one table might fire a trigger that causes corresponding changes to be made in other tables. You can even use a trigger to affect something outside the database. If a new row is inserted into an ORDERS table, for example, you could fire a trigger that wakes up and sets into motion a robot that starts to build the ordered product.
Here’s the Backus-Naur Form (BNF) syntax for the statement that creates a trigger:
<trigger definition> ::=
CREATE TRIGGER <trigger name>
<trigger action time> <trigger event>
ON <table name>[REFERENCING old or new values alias list]
<triggered action>
<trigger action time> ::=
BEFORE
| AFTER
<trigger event> ::=
INSERT
| DELETE
| UPDATE [ OF <trigger column list> ]
<trigger column list> ::= <column name list>
<triggered action> ::=
[ FOR EACH { ROW | STATEMENT } ]
[ WHEN <left paren> <search condition> <right paren> ]
<triggered SQL statement>
<triggered SQL statement> ::=
<SQL procedure statement>
| BEGIN ATOMIC
{ <SQL procedure statement> <semicolon> }…
END
<old or new values alias list> ::=
OLD [ ROW ][ AS ] <old values correlation name>
| NEW [ ROW ][ AS ] <new values correlation name>
| OLD TABLE [ AS ] <old values table alias>
| NEW TABLE [ AS ] <new values table alias>
<old values correlation name> ::= <correlation name>
<new values correlation name> ::= <correlation name>
<old values table alias> ::= <identifier>
<new values table alias> ::= <identifier>
Trigger events
Three different SQL statements — INSERT, DELETE, and UPDATE — can cause a trigger to fire. A referential action can also cause a trigger to fire. If a referential-integrity constraint is violated, for example, a trigger could fire, which would then cause some appropriate action to take place. The optional REFERENCING clause enables you to refer to table values before the trigger action takes place when the OLD keyword is used, as well as to refer to table values after the trigger action takes place when the NEW keyword is used.
In the CREATE TRIGGER statement, a table name is specified. If the trigger event is an INSERT, only an insert operation on the specified table causes the trigger to fire. Similarly, if the trigger event is a DELETE, only a delete operation on the specified table causes the trigger to fire. If the trigger event is an UPDATE on one or more columns in a table, only an UPDATE on those columns of the specified table causes the trigger to fire.
Trigger action time
A trigger can fire either immediately before the trigger event or immediately after it, as specified by the BEFORE or AFTER keyword.
It may seem odd that a trigger could fire before the event that causes it has even occurred. That’s the magic of working in the computer world rather than the real world. The trigger is set up well in advance. It is only waiting for the triggering event to occur, but what is the triggering event? It is the execution of an SQL statement. Before a statement can be executed, it must be fetched and interpreted. Once it is interpreted, the DBMS knows whether it will cause a trigger to fire. If the trigger has been set up with the BEFORE keyword, it can be made to fire before the SQL statement that constitutes the triggering event is executed.
Triggered actions
There are two kinds of triggers:
- Row-level: A row-level trigger is one whose triggered SQL statement is executed for every row modified by the triggering statement.
- Statement-level: A statement-level trigger is one whose triggered SQL statement is executed only once, each time the triggering statement is executed.
The default triggered action is FOR EACH STATEMENT, if neither FOR EACH ROW nor FOR EACH STATEMENT is specified. The WHEN clause in a triggered action enables you to specify a condition. The trigger fires only if the condition evaluates to TRUE.
Triggered SQL statement
The triggered SQL statement can either be a single SQL statement or a BEGIN ATOMIC…END block containing multiple SQL statements. Here’s an example:
CREATE TRIGGER notify
AFTER INSERT ON MEMBERS
FOR EACH STATEMENT
BEGIN ATOMIC
CALL send_email ('President', 'New member') ;
INSERT INTO CHANGE_LOG
VALUES ('MEMBERS', :vfirstname, :vlastname) ;
END ;
Whenever a new row is inserted into the MEMBERS table, an email message is sent to the organization’s president, informing her of the new member. At the same time, a new row is inserted into the CHANGE_LOG table, which records all insertions, deletions, and updates to any table in the database.
Using Stored Functions
A stored function is similar in many ways to a stored procedure. Collectively, the two are referred to as stored routines. They’re different in several ways, including the ways in which they’re 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.
Here’s an example of a function definition, followed by an example of a call to that function:
CREATE FUNCTION Engine (test_engine_ID Integer)
RETURNS NUMERIC (5,2)
BEGIN ATOMIC
DECLARE vdisplacement NUMERIC (5,2)
DEFAULT '' ;
SET vdisplacement = (SELECT Displacement FROM FORD
WHERE EngineID = test_engine_ID);
RETURN vdisplacement;
END ;
This function definition returns the displacement of the Ford engine whose engineID is supplied as input. The following SET statement contains a function call to Engine that retrieves the displacement of the engine identified by EngineID = 4004:
SET displace = Engine (EngineID)
WHERE EngineID = 4004 ;
Passing Out Privileges
The various privileges that you can grant users are discussed in Book 1, Chapter 4. The database owner can grant the following privileges to other users:
- The right to
DELETErows from a table - The right to
INSERTrows into a table - The right to
UPDATErows in a table - The right to create a table that
REFERENCESanother table - The right of
USAGEon a domain
SQL/PSM adds one more privilege that can be granted to a user: EXECUTE. Here are two examples:
GRANT EXECUTE on ForeignOrDomestic to SalesManager ;
GRANT EXECUTE on Engine to Mechanic ;
These statements allow the sales manager of the used-car dealership to execute the ForeignOrDomestic procedure and any mechanic in the shop to execute the Engine function. People who lack the EXECUTE privilege for a routine aren’t able to use the routine.
Using Stored Modules
A stored module can contain multiple routines (procedures 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. Following is an example of a stored module:
CREATE MODULE mod1
CREATE PROCEDURE ForeignOrDomestic
( IN manufacturer CHAR (20),
OUT origin CHAR (8) )
BEGIN ATOMIC
CASE manufacturer
WHEN 'General Motors' THEN
SET origin = 'domestic' ;
WHEN 'Ford' THEN
SET origin = 'domestic' ;
WHEN 'Chrysler' THEN
SET origin = 'domestic' ;
WHEN 'Studebaker' THEN
SET origin = 'domestic' ;
ELSE
SET origin = 'foreign' ;
END CASE
END ;
CREATE FUNCTION Engine (test_engine_ID Integer)
RETURNS NUMERIC (5,2)
BEGIN ATOMIC
DECLARE vdisplacement NUMERIC (5,2)
DEFAULT '' ;
SET vdisplacement = (SELECT Displacement FROM FORD
WHERE EngineID = test_engine_ID);
RETURN vdisplacement;
END ;
END MODULE ;
The two routines in this module (ForeignOrDomestic and Engine) don’t have much in common, but they don’t have to. You can gather related routines into a single module, or you can stick all the routines that you’re likely to use in a single module, regardless of whether they have anything in common.