DB2 Version 9.7 for Linux, UNIX, and Windows

Structure of SQL procedures

SQL procedures consist of several logic parts and SQL procedure development requires you to implement these parts according to a structured format. The format is quite straight-forward and easy to follow and is intended to simplify the design and semantics of routines.

The core of an SQL procedure is a compound statement. Compound statements are bounded by the keywords BEGIN and END. These statements can be ATOMIC or NOT ATOMIC. By default they are NOT ATOMIC.

Within a compound statement, multiple optional SQL PL objects can be declared and referenced with SQL statements. The following diagram illustrates the structured format of a compound statement within SQL procedures:

  label:  BEGIN 
    Variable declarations
    Condition declarations
    Cursor declarations
    Condition handler declarations
    Assignment, flow of control, SQL statements and other compound statements
  END label

The diagram shows that SQL procedures can consist of one or more optionally atomic compound statements (or blocks) and that these blocks can be nested or serially introduced within a single SQL procedure. Within each of these atomic blocks there is a prescribed order for the optional variable, condition, and handler declarations. These must precede the introduction of procedural logic implemented with SQL-control statements and other SQL statements and cursor declarations. Cursors can be declared anywhere with the set of SQL statements contained in the SQL procedure body.

To clarify control-flow, SQL procedure atomic blocks can be labeled as can many of the SQL control-statements contained within them. This makes it easier to be precise when referencing variables and transfer of control statement references.

Here is an example of an SQL procedure that demonstrates each of the elements listed above:

   CREATE PROCEDURE DEL_INV_FOR_PROD (IN prod INT, OUT err_buffer VARCHAR(128))
   LANGUAGE SQL
   DYNAMIC RESULT SETS 1
   BEGIN
   
     DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
     DECLARE SQLCODE integer DEFAULT 0;
     DECLARE NO_TABLE CONDITION FOR SQLSTATE '42704';
     DECLARE cur1 CURSOR WITH RETURN TO CALLER  
                     FOR SELECT * FROM Inv;

     A: BEGIN ATOMIC
          DECLARE EXIT HANDLER FOR NO_TABLE
            BEGIN
               SET ERR_BUFFER='Table Inv does not exist';
            END;

          SET err_buffer = '';

          IF (prod < 200)
            DELETE FROM Inv WHERE product = prod;
          ELSE IF (prod < 400)
            UPDATE Inv SET quantity = 0 WHERE product = prod;
          ELSE
            UPDATE Inv SET quantity = NULL WHERE product = prod; 
          END IF;
        END A;

      B:  OPEN cur1;

   END

NOT ATOMIC compound statements in SQL procedures

The previous example illustrated a NOT ATOMIC compound statement and is the default type used in SQL procedures. If an unhandled error condition occurs within the compound statement, any work that is completed before the error will not be rolled back, but will not be committed either. The group of statements can only be rolled back if the unit of work is explicitly rolled back using ROLLBACK or ROLLBACK TO SAVEPOINT statements. You can also use the COMMIT statement to commit successful statements if it makes sense to do so.

Here is an example of an SQL procedure with a NOT ATOMIC compound statement:

  CREATE PROCEDURE not_atomic_proc ()   
  LANGUAGE SQL   
  SPECIFIC not_atomic_proc
   nap:  BEGIN NOT ATOMIC            

   INSERT INTO c1_sched (class_code, day)
     VALUES ('R11:TAA', 1);           
  
   SIGNAL SQLSTATE '70000';   
   
   INSERT INTO c1_sched (class_code, day)               
     VALUES ('R22:TBB', 1);    

  END nap

When the SIGNAL statement is executed it explicitly raises an error that is not handled. The procedure returns immediately afterwards. After the procedure returns, although an error occurred, the first INSERT statement did successfully execute and inserted a row into the c1_sched table. The procedure neither committed, nor rolled back the row insert and this remains to be done for the complete unit of work in which the SQL procedure was called.

ATOMIC compound statements in SQL procedures

As the name suggests, ATOMIC compound statements, can be thought of as a singular whole. If any unhandled error conditions arise within it, all statements that have executed up to that point are considered to have failed as well and are therefore rolled back.

Atomic compound statements cannot be nested inside other ATOMIC compound statements.

You cannot use the SAVEPOINT statement, the COMMIT statement, or the ROLLBACK statement from within an ATOMIC compound statement. These are only supported in NOT ATOMIC compound statements within SQL procedures.

Here is an example of an SQL procedure with an ATOMIC compound statement:

  CREATE PROCEDURE atomic_proc ()
  LANGUAGE SQL  
  SPECIFIC atomic_proc  
 
  ap:  BEGIN ATOMIC            

     INSERT INTO c1_sched (class_code, day)     
       VALUES ('R33:TCC', 1);           
 
     SIGNAL SQLSTATE '70000';       
   
     INSERT INTO c1_sched (class_code, day)               
       VALUES ('R44:TDD', 1);    

  END ap

When the SIGNAL statement is executed it explicitly raises an error that is not handled. The procedure returns immediately afterwards. The first INSERT statement is rolled back despite successfully executing resulting in a table with no inserted rows for this procedure.

Labels and SQL procedure compound statements

Labels can optionally be used to name any executable statement in an SQL procedure, including compound statements and loops. By referencing labels in other statements you can force the flow of execution to jump out of a compound statement or loop or additionally to jump to the beginning of a compound statement or loop. Labels can be referenced by the GOTO, ITERATE, and LEAVE statements.

Optionally you can supply a corresponding label for the END of a compound statement. If an ending label is supplied, it must be same as the label used at its beginning.

Each label must be unique within the body of an SQL procedure.

Labels can also be used to avoid ambiguity if a variable with the same name has been declared in more than one compound statement if the stored procedure. A label can be used to qualify the name of an SQL variable.