In this section we introduce the main elements of a SQL procedural language.
Variables can be thought of as storage units for values. They can be of any data type supported by your RDBMS. For example, NUMBER and VARCHAR2 are valid data types for variable declaration in Oracle PL/SQL, but MONEY or SMALLDATETIME are not.
Data types are discussed in Chapter 3.
The declaration syntax is also slightly different for different vendors. The sample declaration for integer type variable v_prodcount is shown below.
Variables are declared in the declaration section of the programming unit (between the header and the word BEGIN). All statements in PL/SQL end with semicolon:
The assignment can be done on the declaration, but is usually done in the program body; the assignment operator in PL/SQL is a colon followed by an equals sign (:=).
v_prodcount := 0;
DB2 procedural language uses the DECLARE keyword for variable declarations. A declaration is done in the stored program body rather than in the declaration section; the statement ends with a semicolon:
DECLARE v_prodcount INTEGER;
The keyword SET is used for variable assignment:
SET v_prodcount = 0;
The values stored in variables can be retrieved and/or changed during the execution scope of the programming unit.
The keyword DECLARE is also used in Transact-SQL to declare variables. All local variables must be prefixed with the at-sign (@); global variables are prefixed with the double at-sign (@@).
declare @v_prodcount INTEGER
The variable assignment can be done in two different ways:
select @v_prodcount = 0
set @v_prodcount = 0
Modularity is the important feature of the procedural languages that allows developers to improve code reusability and to hide the complexity of the execution of a specific operation behind a name. There are two levels of modularity: the ability to create sequential logical blocks within a single program and the aptitude of a program to call another program, which in its turn can call yet another program, and so on.
The first level of modularity enables you to structure your program and handle errors appropriately (error handling is discussed later in this chapter). This is especially convenient in PL/SQL where each block can have its own EXCEPTION section:
BEGIN -- Main program ... <statement>,... BEGIN -- Nested block level 1 <statement>,... BEGIN -- Nested block level 2 <statement>,... EXCEPTION <error_handling_statement>,... END; -- Nested block level 2 <statement>,... EXCEPTION <error_handling_statement>,... END; -- Nested block level 1 <statement>,... EXCEPTION <error_handling_statement>,... END; -- Main program
The other vendors' procedural SQL extensions also have the idea of nested blocks that may be used to implement a similar principle as well as to write large programs with complex functionality. Refer to vendor-specific documentation for more details.
Here is the example of the second level of modularity invoking separate modules. Stored procedure A inserts records into tables BB and CC by calling procedures B and C, each of which inserts records into the corresponding table as shown using PL/SQL-like pseudocode:
CREATE PROCEDURE b (field1 INTEGER, field2 INTEGER, field3 INTEGER) AS BEGIN ... INSERT INTO bb VALUES (field1, field2, field3); ... END;
CREATE PROCEDURE c (field1 INTEGER, field2 INTEGER, field3 INTEGER) AS BEGIN ... INSERT INTO cc VALUES (field1, field2, field3); ... END;
CREATE PROCEDURE a (field1 INTEGER, field2 INTEGER, field3 INTEGER) AS BEGIN b(field1, field2, field3); c(field1, field2, field3); END;
Another procedural programming feature closely tied with modularity is the ability to accept parameters and/or pass them to another programming module. Parameters (sometimes also called arguments) are in a way similar to variables. The main difference is that the variables are local to the program whereas parameters are passed into the program upon the module invocation. For example, a stored procedure or user-defined function could have zero or more parameters that are later used for certain actions determined in the program logic. Parameter names and data types are defined in the header part of the programming unit.
Parameters can be passed to a stored procedure or to a user-defined function, but not to a trigger because of the triggers' event-driven nature.
The main difference between PL/SQL and other proprietary procedural SQL extensions is that you cannot specify data type precision, scale, or length in the parameter declaration. For example, v_prodnum VARCHAR2(10) or v_prodprice DECIMAL(10,2) would produce an error. The parameters are enclosed by parentheses.
The declaration section of the SP_PRODUCTADD procedure written in PL/SQL is shown here:
CREATE PROCEDURE sp_productadd ( v_prodid NUMBER, v_prodprice NUMBER, v_prodnum VARCHAR2, v_proddesc VARCHAR2, v_prodstatus CHAR, v_prodbrand VARCHAR2, v_prodpltwid NUMBER, v_prodpltlen NUMBER, v_prodnetwgt NUMBER, v_prodshipwgt NUMBER ) ...
The parameters in Transact-SQL are prefixed with at-signs in the same way the variables are; you do not enclose them in parentheses:
CREATE PROCEDURE sp_productadd @v_prodid INTEGER, @v_prodprice MONEY, @v_prodnum VARCHAR (10), @v_proddesc VARCHAR (44), @v_prodstatus CHAR, @v_prodbrand VARCHAR (20), @v_prodpltwid DECIMAL(5, 2), @v_prodpltlen DECIMAL(5, 2), @v_prodnetwgt DECIMAL(10, 3), @v_prodshipwgt DECIMAL(10, 3) ...
The syntax is practically identical to that of PL/SQL except you have to specify the length or precision and scale when required:
CREATE PROCEDURE sp_productadd ( v_prodid INTEGER, v_prodprice INTEGER, v_prodnum VARCHAR(44), v_proddesc VARCHAR(10), v_prodstatus CHAR(1), v_prodbrand VARCHAR(20), v_prodpltwid DECIMAL(5,2), v_prodpltlen DECIMAL(5,2), v_prodnetwgt DECIMAL(10,3), v_prodshipwgt DECIMAL(10,3) ) ...
Sometimes you want your program to perform different actions based on different conditions (usually resulting from the previous program code execution). For example, you might want to multiply the amounts retrieved from a table by different factors depending on how large the amounts are.
The main conditional statement in all three proprietary procedural SQL extensions is one or another flavor of the IF ... ELSE construct in form:
IF <condition1> THEN <perform action 1> [ELSE IF <condition2> <perform action2> ] ... [ELSE IF <conditionN> <perform actionN>] ] ... [ELSE <perform default action> ] END IF;
Here <condition...> is a Boolean expression that evaluates to TRUE or FALSE.
The syntax is slightly different from the generic one listed previously. The main difference is you use the keyword ELSIF instead of ELSE IF:
IF v_prodcount = 0 THEN INSERT INTO product ... ELSIF v_prodcount = 1 THEN ... UPDATE product ... ELSE ... END IF;
The syntax resembles PL/SQL except the ELSEIF keyword is used instead of ELSIF:
IF (v_prodcount) = 0 THEN INSERT INTO product ... ELSEIF (v_prodcount = 1) THEN ... UPDATE product ... ELSE ... END IF;
The keyword THEN is not used in Transact-SQL; END is only used to end the program or a logical block, not to terminate the conditional statements. The syntax here illustrates the conditional constructs usage in Transact-SQL:
IF @v_prodcount = 0 INSERT INTO product ... ELSE IF @v_prodcount = 1 ... UPDATE product... ELSE ...
The CASE function, another example of conditional execution, is discussed in Chapter 10.
Sometimes you want to repeat a certain block of code more than once within the same execution path. For example, you might want your program to read a line from a file, parse it into tokens, assign their values to variables, and insert a row of data into a table, repeating this operation until the end of file is reached. You may also want to read user input some predetermined number of times, or perform some calculations based on your variables until a certain result is obtained, and so on. The programming constructs that allow you to perform the repeated execution are called loops.
Most programming languages have three types of loops.
WHILE loop repeats a predefined action while some condition remains TRUE.
REPEAT UNTIL loop repeats a block of code until some condition becomes TRUE.
FOR loop repeats the execution some predefined number of times.
All three types of loops can generally be simulated using just one syntax, for example, you can simulate both REPEAT UNTIL and FOR loops using a WHILE loop. Only Oracle PL/SQL has all three types of loops; however, all our "big three" databases have WHILE loop, so we are going to discuss it in this chapter. For more information on how to simulate other loops using the WHILE loop refer to vendor documentation.
WHILE <condition> LOOP <statement1>; [<statement2>;]... END LOOP;
WHILE <condition> DO <statement1>; [<statement2>;]... END WHILE;
WHILE <condition> [BEGIN] <statement1> [ BREAK ] [<statement2>]... [ CONTINUE ] [END]
The two keywords BREAK and CONTINUE are important. BREAK causes an exit from the WHILE loop; any statements after the END keyword are executed. CONTINUE causes the WHILE loop to restart, ignoring any statements after the CONTINUE keyword.
Oracle and DB2 UDB use the EXIT and LEAVE/ITERATE keywords, respectively, for similar purposes. See vendor documentation for more information.
Cursor is a special programming construct that allows you to create a named working area and access its stored information. The main advantage of cursors is the ability to work with individual rows one-by-one rather than with the record set as a whole. For example, all DML statements work with record sets, so you could change "all or nothing". If your update statement is changing hundreds of thousands rows, just one single row could cause the whole statement to fail and roll back all the changes if it violates a column constraint, resulting in serious time losses. Fortunately, cursors are able to handle such situations working with each row individually. A logic that combines cursors, loops, and conditional statements could generate a warning, store the unsuccessfull row information in a special table, and continue processing. Also, cursors give you flexibility on commits and rollbacks (you can commit after each row, after ten rows, or after every five hundred rows), which sometimes can be very useful to save system memory space; you can employ conditional logic and perform calculations on certain values before they are used in your DML statements; using cursors, you are able to update multiple tables with the same values, and much more.
Different RDBMS vendors implement cursors in different ways. Both syntax and functionality vary, which makes it difficult to talk about some generic cursor. SQL99 standards require a cursor to be scrollable, that is, you should be able to move back and forth from one record in the record set to another, but until recently only a few RDBMS vendors (notably MS SQL Server) implemented such functionality. The main reason is that a scrollable cursor is a huge resource waste and not every system can afford it. It is a known fact that many MS SQL Server developers are explicitly warned against using cursors unless it is absolutely necessary, whereas for PL/SQL programmers cursor use is an integral part of their everyday work.
In spite of all the differences, all cursor implementations have some common features. In general, the main operations you can perform on a cursor are DECLARE, OPEN, FETCH, and CLOSE.
DECLARE associates a cursor name with a certain SELECT statement and defines a memory structure to hold the appropriate columns (that could be of different data types). The simplified SQL99 syntax is
DECLARE <cursor_name> [SENSITIVE | INSENSITIVE] [SCROLL] CURSOR FOR <select_statement> [FOR [READ ONLY | UPDATE [OF <column_name>,...]]
INSENSITIVE means that a temporary copy of data based on the <select_statement> is made, so the cursor fetches are not sensitive to any DML changes of underlying tables that may happen afterwards; the default is SENSITIVE, which means every subsequent fetch uses the actual data at this particular moment of time.
SCROLL specifies the cursor as scrollable; that is, additional FETCH options (discussed later) are available.
READ ONLY prevents a cursor from changing data while UPDATE specifies updatable columns within the cursor.
The same syntax is valid in Transact-SQL (even though it has many additional advanced cursor features not mentioned in this book); the Oracle PL/SQL cursor declaration is quite different:
CURSOR <cursor_name> [<parameter_list>] [RETURN <return_type>] IS <select_statement>;
As you can see, the keyword DECLARE is omitted; and the IS keyword is used instead of FOR; also PL/SQL cursors are not scrollable, but can optionally take parameters and return values.
DB2 cursors also do not allow scrolling; other than that the syntax is similar to the SQL99 one:
DECLARE <cursor_name> CURSOR FOR <select_statement>
The OPEN statement executes the underlying query and identifies the result set consisting of all rows that meet the conditions specified on the cursor declaration. The basic syntax is consistent between all three RDBMS:
FETCH retrieves the current row (for nonscrollable cursors) or a specific row (for scrollable cursors), parses the row, and puts the column values into predefined set of variables. The Transact-SQL syntax is
FETCH [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n ]FROM <cursor_name> INTO @<variable_name>,...
The PL/SQL and DB2 syntax for nonscrollable cursors is
FETCH <cursor_name> INTO <variable_name>,...
The number of columns in the SELECT clause of the <select_statement> specified upon the cursor declaration must exactly match the number of variables specified in the INTO clause of the FETCH statement.
The cursor rows of nonscrollable cursors are usually fetched in a loop until the last row is processed; the scrollable cursors can be used as a part of a more complicated processing logic.
The CLOSE statement deallocates memory, releases locks, and makes the cursor's result set undefined. The syntax is
The cursor can be reopened with the OPEN statement.
In addition to the four main cursor operations described in this section, Transact-SQL requires all cursors to be deallocated using the DEALLOCATE statement that releases all data structures comprising the cursor:
As we mentioned before, cursors are used in row-by-row operations and are usually fetched within loop structures. The following examples are very simple; our main goal here is to show how to declare, open, fetch, and close a cursor with emphasis on the implementation differences.
The procedure SP_PRINTCUSTPHONE declares and opens the cursor cur_custphone based on a query retrieving records from the V_CONTACT_LIST view and then fetches them one by one and prints them to the standard output.
The term "standard output" identifies the device to which the program sends its output; usually, by default it's the display of your monitor. The standard output could be redirected into an OS file.
Each PL/SQL cursor has four attributes associated with it that can be appended to the cursor name to return certain information. The arguments are as follows:
%FOUND. TRUE if the last FETCH returned a row; FALSE otherwise.
%ISOPEN. TRUE if the cursor is open; FALSE otherwise.
%NOTFOUND. The logical opposite of %FOUND.
%ROWCOUNT. The number of rows fetched so far.
The following example uses %FOUND to determine when to exit the loop (when it yields FALSE there are no more rows to fetch):
CREATE OR REPLACE PROCEDURE SP_PRINTCUSTPHONE -- The procedure displays customer names and phone numbers IS -- Declare local variables to fetch cursor values into v_custname VARCHAR(50); v_phone VARCHAR(20); -- Declare cursor CURSOR cur_custphone IS SELECT name, phone_number FROM v_contact_list WHERE contact_type = 'customer'; BEGIN -- Open cursor OPEN cur_custphone; -- Fetch cursor for the first time FETCH cur_custphone INTO v_custname, v_phone; -- Fetch records in loop until the last one is reached WHILE cur_custphone%FOUND LOOP FETCH cur_custphone INTO v_custname, v_phone; -- Enable standard output DBMS_OUTPUT.ENABLE; -- Put line into the standard output DBMS_OUTPUT.PUT_LINE(v_custname || v_phone); END LOOP; -- Close cursor CLOSE cur_custphone; END SP_PRINTCUSTPHONE; /
Transact-SQL uses the so-called cursor global functions to obtain information about the cursor. The two most commonly used are @@FETCH_STATUS, which returns 0 (zero) if the last fetch was successful or a negative number otherwise, and @@CURSOR_ROWS, which returns the number of qualifying rows currently in the last cursor opened.
As we mentioned before, Transact-SQL cursors can be scrollable. Unlike PL/SQL or DB2 cursors, where you only can go to the next record in the record set while previous rows become unavailable unless you close and reopen the cursor, Transact-SQL gives you the option to navigate the entire record set contained in the cursor — in a way similar to how you would use your compact disk player buttons when jumping between the songs on the CD.
The following example uses @@FETCH_STATUS to determine when the loop is to be terminated; the cursor is not declared as scrollable to be consistent with the previous examples:
CREATE PROCEDURE SP_PRINTCUSTPHONE -- The procedure displays customer names and phone numbers AS -- Declare local variables to fetch cursor values into DECLARE @v_custname VARCHAR(50) DECLARE @v_phone VARCHAR(20) -- Declare cursor DECLARE cur_custphone CURSOR FOR SELECT name, phone_number FROM v_contact_list WHERE contact_type = 'customer' BEGIN -- Open cursor OPEN cur_custphone -- Fetch cursor for the first time FETCH cur_custphone INTO @v_custname, @v_phone -- Fetch records in loop until the last one is reached WHILE @@fetch_status = 0 BEGIN FETCH cur_custphone INTO @v_custname, @v_phone PRINT @v_custname + @v_phone END -- Close cursor CLOSE cur_custphone DEALLOCATE cur_custphone END
More detailed information on how to create stored procedures is given later in this chapter.
The error handling varies a lot between the three procedural SQL extensions discussed in this book. Oracle and DB2 use structured error handling to deal with abnormal conditions during the program execution. When an error occurs, RDBMS raises an exception (syntax is quite different between Oracle and DB2). Exceptions can be predefined (for example, one of the most common ones in Oracle — NO_DATA_FOUND occurs when a SELECT ... INTO statement returns no rows) or user-defined. MS SQL Server allows you to raise, trap, and handle errors inside the program body.
The error handling is a quite complicated process. This chapter gives you just the very basics; the vendor-specific documentation is the best source if you need to know more.
All PL/SQL programming blocks have an optional EXCEPTION section where the program control is passed if an error occurs. Oracle has a set of predefined exceptions. We already mentioned NO_DATA_FOUND; another common one is TOO_MANY_ROWS, which occurs if a single-row subquery returns more than one row; the generic one to be raised for any type of error is OTHERS. You could also declare and instantiate your own exceptions in the declaration section of the program and raise them in the program body:
CREATE PROCEDURE ... AS ... /* To handle error conditions that have no predefined name, you must use the pragma EXCEPTION_INIT. A pragma is a compiler directive that is processed at compile time. */ my_exception EXCEPTION; PRAGMA EXCEPTION_INIT(my_exception, -400); ... BEGIN ... IF <special_condition> RAISE my_exception; ... EXCEPTION WHEN no_data_found THEN <conditions to handle no_data_found> WHEN too_many_rows THEN <conditions to handle too_many_rows > WHEN my_exception THEN <conditions to handle my_exception > WHEN others THEN <conditions to handle all other exceptions > END;
The conditions to handle a specific exception can be quite different. For example, you might want to rollback (or commit) a transaction and exit the program; or in some situations you don't want any action at all but simply to continue the execution; you also might want to display (or write into a table or OS file) an error message, and so on.
When an error occurs in DB2, the behavior of your SQL program is determined by condition handlers. The three general conditions in DB2 are NOT FOUND, SQLEXCEPTION, and SQLWARNING. If a statement in your SQL program issues an SQLWARNING or NOT FOUND condition and a handler for the appropriate condition has been declared, the control is passed to the corresponding handler; otherwise DB2 passes control to the next statement in the program body. The situation is different when SQLEXCEPTION condition is issued by a statement. The control is still passed to the appropriate handler if the handler is declared; if not, the program execution is terminated and the control is passed to the calling module.
DB2 has three main handler types: CONTINUE, EXIT, and UNDO. CONTINUE means the program execution continues with the next statement after the one that caused the error (after the special error-handling statements are complete); EXIT tells the program to go to the last statement in the program; and UNDO is in a way similar to EXIT, but in addition all DML statements in the program are rolled back:
CREATE PROCEDURE ... ... LANGUAGE SQL BEGIN ATOMIC ... DECLARE [CONTINUE | EXIT | UNDO] HANDLER FOR <condition> [<error-handling statements>] ... END @
In Transact-SQL errors are usually handled using the conditional statements (IF ... ELSE) described above. The way to handle critical problems is to use RAISERROR statement that terminates the program and returns control to the calling module; otherwise if you want to continue the program execution, the GOTO statement can be used to point to the block of statements to execute:
CREATE PROCEDURE ... AS ... BEGIN ... IF <special_condition> RAISERROR(<paramters_for_reiserror>) ... IF <another_special_condition> GOTO <label> ... <label> <error_handling_statements> ... END
The special global unary function @@error returns the error number for the last Transact-SQL statement executed. It is set to 0 if the statement executed successfully. If an error occurs, an error message number is returned. (See MS SQL Server documentation for error message numbers and descriptions.)