Key Elements of a SQL Procedural Language

In this section we introduce the main elements of a SQL procedural language.

Variables and assignment

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.

Cross-References 

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.

PL/SQL (Oracle)

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:

v_prodcount NUMBER;

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 UDB

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.

Transact-SQL (MS SQL Server)

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

or:

set @v_prodcount = 0

Modularity, subprograms, and block structure

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;

Passing parameters

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.

Note 

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.

PL/SQL

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 ) ... 

Transact-SQL

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)   ...

DB2 IBM SQL

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) ) ...

start sidebar
Passing Parameters by Name and by Value

Most procedural languages implement two main types of parameters: by value and by reference. The main difference is that if you send a variable from the calling module as a by value parameter, a copy of this variable is actually sent, so whatever happens to it inside the subroutine does not affect the original variable value. However, if you send the variable by reference, the memory address of this variable is passed to the subprogram, so it could actually be changed. The following example (written in pseudocode) illustrates the concept:

CREATE PROCEDURE POWER_V (x INTEGER BYVALUE) -- The parameter x is passed by value AS BEGIN   x := x * x; END;     CREATE PROCEDURE POWER_R (x INTEGER BYREFERENCE) -- The parameter x is passed by reference AS BEGIN   x := x * x; END;     CREATE PROCEDURE SQUARE (x INTEGER) AS -- Declare local variable y of type integer DECLARE y INTEGER; BEGIN  -- Assign variable y the value of x  y := x;  -- Display the value of variable y  PRINT (y);  -- Call procedure POWER_V.  EXECUTE POWER_V(y);  -- The value of y does not change  PRINT (y);  -- Call procedure POWER_R.  EXECUTE POWER_R(y);  -- The variable y was passed by reference  -- so its value actually changes (squared)  PRINT (y); END;     EXECUTE SQUARE(5); 5 5 25

All the "big three" databases allow you to pass parameters by value or by reference, with by value being a default.

Both Oracle and DB2 have two types of by reference arguments, OUT and IN OUT (INOUT in DB2). The first one means you can not pass a value using the argument (you would usually pass NULL instead), but the nested program could assign a value to it that is readable by the main module. The second type means you could actually send a value to the subprogram, that it would use in its calculations, optionally change it, and send it back to the calling module. MS SQL Server has only one type of by reference variable called OUTPUT that roughly corresponds to IN OUT.

The syntax varies between implementations:

Oracle 9i

<variable_name> [IN | OUT IN OUT] <datatype>

For example:

my_value1 IN OUT NUMBER; my_value2 OUT VARCHAR2; my_value3 IN DATE;

Note that the last declaration could simply be

my_value3 DATE;

DB2 UDB 8.1

[IN | OUT INOUT]  <variable_name> <datatype>

For example:

INOUT my_value1 INTEGER; OUT my_value2 VARCHAR(30); IN my_value3 DATE;

The last statement is an equivalent to the following simple line:

my_value3 DATE;

MS SQL Server 2000

@<variable_name> <datatype> [OUTPUT]

For example:

@ my_value1 INTEGER OUTPUT @ my_value3 DATETIME

There is no alternative for the last statement; @my_value3 DATETIME INPUT is invalid syntax.

end sidebar

Conditional execution

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.

PL/SQL

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;

DB2 UDB

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;

Transact-SQL

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   ...
Cross-References 

The CASE function, another example of conditional execution, is discussed in Chapter 10.

Repeated execution

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.

PL/SQL

start example
WHILE <condition> LOOP   <statement1>;   [<statement2>;]... END LOOP; 
end example

DB2 UDB

start example
WHILE <condition> DO   <statement1>;   [<statement2>;]... END WHILE; 
end example

Transact-SQL

start example
WHILE <condition>   [BEGIN]     <statement1>     [ BREAK ]     [<statement2>]...     [ CONTINUE ]   [END]
end example

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.

Note 

Oracle and DB2 UDB use the EXIT and LEAVE/ITERATE keywords, respectively, for similar purposes. See vendor documentation for more information.

Cursors

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

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>

OPEN

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:

OPEN <cursor_name>

FETCH

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>,...
Note 

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.

CLOSE

The CLOSE statement deallocates memory, releases locks, and makes the cursor's result set undefined. The syntax is

CLOSE <cursor_name>

The cursor can be reopened with the OPEN statement.

Note 

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:

DEALLOCATE <cursor_name>

Cursor examples

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.

Note 

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.

PL/SQL

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

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.

Note 

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 
Note 

More detailed information on how to create stored procedures is given later in this chapter.

Error handling

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.

PL/SQL

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.

DB2 UDB

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 @ 

Transact-SQL

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.)




SQL Bible
Microsoft SQL Server 2008 Bible
ISBN: 0470257040
EAN: 2147483647
Year: 2005
Pages: 208

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net