SQL PL Stored Procedures

The CALL, ALLOCATE, and ASSOCIATE keywords are all used when retrieving results from another stored procedure. The CALL statement is used to invoke the stored procedure:

CALL PROCEDURE_NAME (PARM1, PARM2, …)

If the stored procedure returns a result set (cursor), the calling program must ALLOCATE a cursor to process the rows, and ASSOCIATE the cursor to the result set that was returned.

For instance, the stored procedure EMPR is called by another stored procedure. The result set is opened through the use of a cursor that was associated with it:

CALL EMPR;
ASSOCIATE RESULT SET LOCATOR (LOC1) WITH PROCEDURE EMPR;
ALLOCATE C1 CURSOR FOR RESULT SET LOC1;

An OPEN is not required when reading the contents of the cursor. The EMPR stored procedure has already returned an open cursor for processing.

During execution, a stored procedure may need to build a SQL statement dynamically and then submit it for processing. This dynamic SQL execution can be accomplished through the use of the PREPARE and EXECUTE statements.

The PREPARE statement is used to check the statement syntax and convert it to a form that can be submitted to DB2 for execution. The EXECUTE statement submits the statement for execution in the database. The following example illustrates this concept:

DECLARE STMT VARCHAR(200);
SET STMT = 'CREATE TABLE T_EMP(EMP CHAR(6), SALARY INT)';
PREPARE S1 FROM STMT;
EXECUTE S1;

The STMT variable can be changed and a new statement executed. The PREPARE step can be avoided with the use of the EXECUTE IMMEDIATE statement:

DECLARE STMT VARCHAR(200);
SET STMT = 'CREATE TABLE T_EMP(EMP CHAR(6), SALARY INT)';
EXECUTE IMMEDIATE STMT;

The SQL PL code should include an exception handler in the event that the SQL that was submitted was incorrect.

If a statement is to be executed multiple times with slightly different values, it may be more efficient to use parameter markers. Parameter markers are question marks (?) inserted into portions of the SQL and act as placeholders. These are meant to be used for select, insert, update, and delete statements. For instance, the following SQL statement will delete an employee with an employee number that has yet to be defined:

DELETE FROM EMPLOYEE WHERE EMPNO = ?;

This statement can be prepared once, and then executed subsequent times with different values for the employee number.

DECLARE EMPNO CHAR(6);
SET STMT = ' DELETE FROM EMPLOYEE WHERE EMPNO = ?';
PREPARE S1 FROM STMT;
EMPNO = '111111';
EXECUTE S1 USING EMPNO;
EMPNO = '222222';
EXECUTE S1 USING EMPNO;

This is a much more efficient process than continually preparing a new SQL statement.

The DECLARE CURSOR statement defines a cursor that will be subsequently used to OPEN and FETCH records. The DECLARE statement includes the SQL statement that will be used to retrieve data:

DECLARE cursor-name CURSOR [WITH HOLD]
  [WITH RETURN [TO CALLER | TO CLIENT]]
  FOR SELECT-STATEMENT

WITH HOLD specifies what happens to the cursor when a COMMIT WORK occurs. Normally a cursor will be closed when a COMMIT occurs, but when WITH HOLD is specified, it will remain open for further processing. This is useful when large amounts of data need to be processed and the stored procedure wants to "save" the work that was done to this point in time.

The WITH RETURN clause is optional and specifies that the cursor will be returned to the calling program (TO CALLER) or directly to the client (TO CLIENT).

Once a cursor has been defined, it can subsequently be opened for processing. The OPEN statement includes the name of the cursor and the parameter marker values if any exist:

OPEN CURSOR-NAME

When the statement has been opened, the FETCH statement can be used to retrieve values from the rows that are returned:

FETCH FROM CURSOR-NAME INTO VAR1, VAR2, …

Once processing is complete, the cursor can be closed:

CLOSE CURSOR-NAME

The following code illustrates the use of all of the elements described in this section.

CREATE PROCEDURE FIND_SALARY()
  LANGUAGE SQL
BEGIN
  DECLARE EOF INT DEFAULT 0;
  DECLARE STMT VARCHAR(200);
  DECLARE R_EMPNO CHAR(6);
  DECLARE R_SALARY DEC(9,2);
  DECLARE C1 CURSOR FOR
    SELECT EMPNO, SALARY FROM EMPLOYEE
      WHERE SALARY <= 50000;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET EOF = 1;

  SET STMT = 'DELETE FROM T_RESULTS';
  EXECUTE IMMEDIATE STMT;
  SET STMT = 'INSERT INTO T_RESULTS VALUES (?,?)';
  PREPARE S1 FROM STMT;
  OPEN C1;
  WHILE EOF = 0 DO
    FETCH FROM C1 INTO R_EMPNO, R_SALARY;
    EXECUTE S1 USING R_EMPNO, R_SALARY;
  END WHILE;
  CLOSE C1;
END

This code selects all of the records in the EMPLOYEE table (CURSOR C1) and places them into the T_RESULTS table. Before the records are inserted into this table, the T_RESULTS contents are deleted (first EXECUTE IMMEDIATE) and then an INSERT statement is prepared with two parameter markers. As each record is read from the answer set, the prepared insert statement is executed with the values returned from the EMPLOYEE table. Note how the CONTINUE HANDLER has been set up to return a value for EOF that indicates that the end of records has been reached.

Before a stored procedure can access a database, a connection must be established by the client application. A SQL PL stored procedure cannot connect to a database itself; it relies on the application program to do this for it.

After the connection has been established, the program or stored procedure can issue SQL statements that manipulate data (SELECT, INSERT, UPDATE, or DELETE), define and maintain database objects (CREATE, DROP), and initiate control operations (GRANT, COMMIT, or ROLLBACK). These statements are considered as parts of a transaction. A transaction is a sequence of SQL statements (possibly with intervening program logic) that the database manager treats as a whole. An alternative term that is often used for transaction is unit of work. To ensure the consistency of data at the transaction level, the system makes sure that either all operations within a transaction are completed or none are completed.

A transaction begins implicitly with the first executable SQL statement and ends with either a COMMIT or a ROLLBACK statement or when the program ends. A commit makes the changes performed during the current transaction permanent, and a rollback restores the data to the state it was in prior to beginning the transaction. Usually a COMMIT or ROLLBACK is left to the application program, not the stored procedure.

Developers can also create intermediate SAVEPOINTS within their SQL so that they can selectively ROLLBACK to a prior point in their code. This feature allows for greater flexibility when working with modular code and stored procedures. Savepoints are useful within stored procedures where portions of work may need to be rolled back due to an error. A SAVEPOINT definition is similar to the following:

SAVEPOINT SAVEPOINT-NAME ON ROLLBACK RETAIN CURSORS;

The use of a SAVEPOINT is best illustrated with the following example:

INSERT INTO EMP VALUES ('111111','Fred','A20');
SAVEPOINT SP1 ON ROLLBACK RETAIN CURSORS;
INSERT INTO EMP VALUES ('222222','Paul','A31');
ROLLBACK TO SAVEPOINT SP1;

This example inserts the first employee (Fred) into the EMP table, followed by 'Paul'. The ROLLBACK statement causes the second INSERT to be rolled back so that only the first record remains.

The CASE statement selects a suitable execution path based on multiple conditions. There are two forms of the CASE statement available:

CASE SEARCHED CASE STATEMENT
  WHEN VALUE THEN …
  ELSE
END CASE

CASE
  WHEN VAR = VALUE THEN
  ELSE
END CASE

For instance, applying logic to a record based on which department an employee works for can be easily accomplished with the CASE statement.

CASE WORKDEPT                 CASE
  WHEN 'A00' THEN …             WHEN WORKDEPT='A00' THEN
  WHEN 'B01' THEN …             WHEN WORKDEPT='B01' THEN
  ELSE …                        ELSE …
END CASE                      END CASE

The ELSE clause is executed when none of the other WHEN clauses is selected. If none of the WHEN clauses is selected and the ELSE clause is missing, DB2 will raise an error condition.

The order of the conditions for the CASE expression is very important. DB2 will process the first condition first, then the second, and so on. If you do not pay attention to the order in which the conditions are processed, you might execute the same condition for every value entering the CASE statement. For example, if you coded a condition like "AGE<=65" before "AGE<45", all the data that is lower than 65, even 40 or 30, will branch into the first WHEN clause.

GOTO

The GOTO statement is used to branch to a label within an SQL procedure.

GOTO label_name;

The label must exist or else an error will be generated at procedure creation time. The use of the GOTO generates a lot of debate in the programming community. There are good arguments for not using the GOTO because of the poor maintainability and debugging of code. However, the GOTO can be used effectively to skip over large quantities of code to enter an error processing section. Coding this with IF/THEN/ELSE blocks may be awkward or create very complex logic structures.

There are some restrictions on the use of the GOTO within some control structures:

• If the GOTO statement is defined in a FOR statement, the label must be defined inside the same FOR statement, excluding a nested FOR statement or nested compound statement 
   
• If the GOTO statement is defined in a compound statement, the label must be defined inside the same compound statement, excluding a nested FOR statement or nested compound statement 
   
• If the GOTO statement is defined in a handler, the label must be defined in the same handler, following the other scope rules 
   
• If the GOTO statement is defined outside of a handler, the label must not be defined within a handler. 
   
• If the label is not defined within a scope that the GOTO statement can reach, an error is returned

LOOP

LOOP is a very simple control structure that continues to execute the statements within the loop until a RETURN or LEAVE statement is encountered. A LOOP example is shown below:

FETCH_LOOP:
  LOOP
    FETCH C1 INTO V_SALARY;
    IF V_SALARY = 0 THEN
       LEAVE FETCH_LOOP;
    END IF;
    SET V_COUNTER = V_COUNTER + 1;
  END LOOP FETCH_LOOP;

The beginning of a loop can have a label associated with it. The label can be used in a LEAVE statement to guarantee which LOOP statement is exited. This is particularly important in nested LOOP structures where it may not be clear where processing will continue after the LEAVE statement. In this example, the LEAVE statement will begin execution of statements after the END LOOP.

In addition to the LEAVE statement, the ITERATE statement can also be used to change the order of execution in the LOOP. The ITERATE will return execution to the first statement in the LOOP.

REPEAT

The REPEAT statement executes a statement or group of statements until a search condition is true. This is similar to a WHILE statement except that the test is done at the end of the loop rather than at the beginning. The REPEAT structure also supports the use of the ITERATE and LEAVE statements.

FETCH_LOOP:
  REPEAT
    FETCH C1 INTO V_SALARY;
    SET V_COUNTER = V_COUNTER + 1;
    UNTIL V_SALARY = 0
  END REPEAT FETCH_LOOP;

The label at the beginning of the loop is optional but can make it easier to follow ITERATE and LEAVE instructions in nested loops.

The RESIGNAL statement is similar to the SIGNAL statement, but it is only used in the body of an error handler for a specific error condition. The role of the RESIGNAL statement is to return back to the calling program another error code, other than the one that caused the initial problem:

DECLARE overflow CONDITION FOR SQLSTATE '22003';
DECLARE CONTINUE HANDLER FOR OVERFLOW
  RESIGNAL SQLSTATE '22375';

When the overflow handler is invoked, the SQLSTATE 22375 is returned to the calling application, rather than the 22003 that cause the original error.