compound-statement

A compound statement groups other statements together in an SQL routine. A compound statement allows the declaration of SQL variables, cursors, and condition handlers.

Syntax

                      .-NOT ATOMIC-.   
>>-+--------+--BEGIN--+------------+---------------------------->
   '-label:-'                          

>--+--------------------------------------+--------------------->
   | .----------------------------------. |   
   | V                                  | |   
   '---+-SQL-variable-declaration--+--;-+-'   
       +-SQL-condition-declaration-+          
       '-return-codes-declaration--'          

   .------------------------------.   
   V                              |   
>----+--------------------------+-+----------------------------->
     '-statement-declaration--;-'     

>--+---------------------------------+-------------------------->
   | .-----------------------------. |   
   | V                             | |   
   '---DECLARE-CURSOR-statement--;-+-'   

>--+----------------------------+------------------------------->
   | .------------------------. |   
   | V                        | |   
   '---handler-declaration--;-+-'   

   .--------------------------------.                   
   V                                |                   
>----+----------------------------+-+--END--+-------+----------><
     '-SQL-procedure-statement--;-'         '-label-'

SQL-variable-declaration:

>>-DECLARE------------------------------------------------------>

     .-,-----------------.                                           
     V                   |               .-DEFAULT NULL------.       
>------SQL-variable-name-+--+-data-type--+-------------------+-+---><
                            |            '-DEFAULT--constant-' |     
                            '-RESULT_SET_LOCATOR VARYING-------'

SQL-condition-declaration:

>>-DECLARE--SQL-condition-name--CONDITION--FOR------------------>

               .-VALUE-.                      
   .-SQLSTATE--+-------+-.                    
>--+---------------------+--string-constant--------------------><

return-codes-declaration:

                                          .-DEFAULT '00000'----------.     
>>-DECLARE--+-SQLSTATE--+-CHAR(5)------+--+--------------------------+-+-><
            |           '-CHARACTER(5)-'  '-DEFAULT--string-constant-' |   
            |                       .-DEFAULT 0-----------------.      |   
            '-SQLCODE--+-INTEGER-+--+---------------------------+------'   
                       '-INT-----'  '-DEFAULT--integer-constant-'

statement-declaration:

            .-,--------------.                  
            V                |            (1)   
>>-DECLARE----statement-name-+--STATEMENT----------------------><

Notes:

The statement is not allowed in an SQL-routine-body for an SQL function.

handler-declaration:

>>-DECLARE--+-CONTINUE-+--HANDLER--FOR-------------------------->
            '-EXIT-----'                 

>--+-specific-condition-value-+--SQL-procedure-statement-------><
   '-general-condition-value--'

specific-condition-value:

   .-,----------------------------------------.   
   V             .-VALUE-.                    |   
>>---+-SQLSTATE--+-------+--string-constant-+-+----------------><
     '-condition-name-----------------------'

general-condition-value:

   .-,----------------.   
   V                  |   
>>---+-SQLEXCEPTION-+-+----------------------------------------><
     +-SQLWARNING---+     
     '-NOT FOUND----'

Description

label

Specifies the label for the compound-statement. If the beginning label is specified, it can be used to qualify SQL variables declared in the compound statement and can also be specified as the target on a LEAVE statement. If the ending label is specified, it must be the same as beginning label. The label name cannot be the same as the routine name or another label within the same scope.

NOT ATOMIC

NOT ATOMIC indicates that an unhandled exception condition within the compound-statement does not cause the compound-statement to be rolled back.

SQL-variable-declaration

Declares a variable that is local to the compound statement.

SQL-variable-name: Defines the name of a variable. DB2® converts all SBCS SQL variable names that are not delimited to uppercase. SQL-variable-name must be unique within the compound statement in which is it declared, excluding any declarations in compound statements that are nested within that compound statement. SQL-variable-name must not be the same as a parameter name. See References to SQL parameters and SQL variables for information about how SQL variable names are resolved when there are columns with the same name as an SQL variable involved in a statement, or when multiple SQL variables exist with the same name in the routine body.
SQL-variable-name can only be referenced within the compound statement in which it is declared, including any compound statement that is nested within that compound statement. If the compound statement where the variable is declared has a label, references to the variable name can be qualified with that label. For example, an SQL variable V that is declared in a compound statement that is labeled C can be referenced as C.V.
data-type: Specifies the data type and length of the variable. SQL variables follow the same rules for default lengths and maximum lengths as SQL routine parameters. See CREATE FUNCTION (compiled SQL scalar) for descriptions of SQL data types and lengths.
DEFAULT constant or NULL: Defines the default for the SQL variable. The specified constant must represent a value that could be assigned to the variable in accordance with the rules of assignment as described in Assignment and comparison. The variable is initialized when the compound statement begins processing. If a default value is not specified, the SQL variable is initialized to NULL.
RESULT_SET_LOCATOR VARYING: Specifies the data type for a result set locator variable.

SQL-condition-declaration

Declares a condition name and corresponding SQLSTATE value.

SQL-condition-name: Specifies the name of the condition. The condition name must be unique within the compound statement in which it is declared, excluding any declarations that are in compound statements that are nested within that compound statement. A condition name can only be referenced within the compound statement in which it is declared, including any compound statements that are nested within that compound statement.
FOR SQLSTATE string-constant: Specifies the SQLSTATE that is associated with the condition. The string must be specified as five characters enclosed in single quotes, and the SQLSTATE class (the first two characters) must not be '00'.

return-codes-declaration

Declares special variables named SQLSTATE and SQLCODE. These variables are automatically set to the SQLSTATE and SQLCODE values for the first condition in the diagnostics area after executing an SQL statement other than GET DIAGNOSTICS or an empty compound statement.

The SQLSTATE and SQLCODE SQL variables are only intended to be used as a means of obtaining the SQL return codes that resulted from processing the previous SQL statement other than GET DIAGNOSTICS. If there is any intention to use the SQLSTATE and SQLCODE values, save the values immediately to other SQL variables to avoid having the values replaced by the SQL return codes returned after executing the next SQL statement. If a handler is defined that handles an SQLSTATE, you can use an assignment statement to save that SQLSTATE (or the associated SQLCODE) value in another SQL variable, if the assignment is the first statement in the handler.

Assignment to these variables is not prohibited; however, it is not recommended. Assignment to these variables is ignored by condition handlers, and processing an assignment to these special variables causes the specified values for the assignment to be overlayed with the SQL return codes returned from executing the statement that does the assignment. The SQLSTATE and SQLCODE SQL variables cannot be set to NULL.

statement-declaration

Declares a list of one or more names that are local to the compound statement. A statement name cannot be the same as another statement name within the same compound statement.

DECLARE-CURSOR-statement

Declares a cursor in the procedure body. Each cursor must have a unique name within the routine. The cursor can only be referenced from within the compound statement in which it is declared, including any compound statements that are nested within that compound statement. Use an OPEN statement to open the cursor, a FETCH statement to read a row using the cursor, and a CLOSE statement to close the cursor. If the cursor is intended for use as a result set cursor:

Specify WITH RETURN when the cursor is declared
Create the procedure using the DYNAMIC RESULT SETS clause with a non-zero value
Do not specify a CLOSE statement for the cursor in the compound statement

For additional information about declaring a cursor, see DECLARE CURSOR.

handler-declaration

Specifies a condition handler, an SQL-procedure-statement to execute when an exception or completion condition occurs in the compound-statement. The SQL-procedure-statement executes when a condition handler receives control.

A condition handler declaration cannot reference the same condition value or SQLSTATE value more than one time. It cannot reference an SQLSTATE value and a condition name that represent the same SQLSTATE value.

When two or more condition handlers are declared in a compound statement, no two condition handler declarations can specify the same:

general condition category
specific condition, either as an SQLSTATE value or as a condition name that represents the same value

A condition handler is active for the set of SQL-procedure-statements that follow the condition handler declarations within the compound statement in which the condition handler is declared, including any nested compound statements.

CONTINUE: Specifies that after the condition handler is activated and completes successfully, control is returned to the SQL statement that follows the statement that raised the condition. However, if the condition is an error condition and it was encountered while evaluating a search condition, as in a CASE, FOR, IF, REPEAT or WHILE statement, control returns to the statement that follows the corresponding END CASE, END FOR, END IF, END REPEAT, or END WHILE.
EXIT: Specifies that after the condition handler is activated and completes successfully, control is returned to the end of the compound statement that declared the condition handler.

The conditions that can cause the handler to gain control are:

SQLSTATE string-constant: Specifies that the handler is invoked when the specific SQLSTATE occurs. The first two characters of the SQLSTATE value must not be '00'.
SQL-condition-name: Specifies that the handler is invoked when the specific SQLSTATE that is associated with the condition name occurs. The SQL-condition-name must be declared within the compound statement that contains the handler declarations, or within a compound statement in which that compound statement is nested.
SQLEXCEPTION: Specifies that the handler is invoked when an SQLEXCEPTION occurs. An SQLEXCEPTION is an SQLSTATE in which the class code is a value other than '00', '01', or '02'. For more information on SQLSTATE values, see DB2 Codes.
SQLWARNING: Specifies that the handler is invoked when an SQLWARNING occurs. An SQLWARNING is an SQLSTATE value with a class code of '01'.
NOT FOUND: Specifies that the handler is invoked when a NOT FOUND condition occurs. NOT FOUND corresponds to an SQLSTATE value with a class code of '02'.

Notes

Unlike host variables, SQL variables are not preceded by colons when they are used in SQL statements.

Nesting compound statements: Compound statements can be nested. Nested compound statements can be used to scope variable definitions, condition names, condition handlers, and cursors to a subset of the statements in a routine. This can simplify the processing that is done for each SQL routine statement. Nested compound statements enable the use of a compound statement within the declaration of a condition handler.

The scope of a cursor: The scope of a cursor name is the compound statement in which it is declared, including any compound statements that are nested within that compound statement. A cursor name can only be referenced within the compound statement in which it is declared, including any compound statements that are nested within that compound statement.

Considerations for statement-name: The scope of a statement-name that is declared in a compound statement is the compound statement and any nested compound statements (unless the same statement-name is declared in a nested compound statement). If a statement-name is used in a DECLARE CURSOR statement or a PREPARE statement and has not been declared in the compound statement where it is used or any outer compound statements in which it is nested, the statement-name is assumed to be declared globally for the routine.

Condition handlers: Condition handlers in SQL routines are similar to WHENEVER statements that are used in external SQL application programs. A condition handler can be defined to automatically get control when an exception, warning, or not found condition occurs. The body of a condition handler contains code that is executed when the condition handler is activated. A condition handler can be activated as the result of an exception, a warning, or a not found condition that is returned by DB2 for the processing of an SQL statement. Or the condition that activates the handler can be the result of a SIGNAL or RESIGNAL statement that is issued within the SQL routine body.

A condition handler is declared within a compound statement, and it is active for the set of SQL-procedure-statements that follow all of the condition handler declarations within the compound statement in which the condition handler is declared. For example, the scope of a condition handler declaration H is the list of SQL-procedure-statements that follow the condition handler declarations that are contained within the compound statement in which H appears. This means that the scope of H does not include the statements that are contained in the body of the condition handler H, implying that a condition handler cannot handle conditions that arise inside its own body. Similarly, for any two condition handlers H1 and H2 that are declared in the same compound statement, H1 will not handle conditions that arise in the body of H2, and H2 will not handle conditions that arise in the body of H1.

The declaration of a condition handler specifies the condition that activates it, the type of condition handler (CONTINUE or EXIT), and the handler action. The type of condition handler determines to where control is returned after the handler action successfully completes.

Condition handler activation: When a condition other than a successful completion occurs in the processing of SQL-procedure-statement, if a condition handler that could handle the condition is within scope, one such condition handler will be activated to process the condition.

In a routine with nested compound statements, condition handlers that could handle a specific condition might exist at several levels of the nested compound statements. The condition handler that is activated is a condition handler that is declared innermost to the scope in which the condition was encountered. If more than one condition handler at the nesting level could handle the condition, the condition handler that is activated is the most appropriate handler that is declared in that compound statement.

The most appropriate handler is the condition handler that most closely matches the SQLSTATE or the exception or completion condition. For a given compound statement, when both a specific handler for a condition and a general handler are declared that address the same condition, the specific handler takes precedence over the general handler.

For example, if the innermost compound statement declares a specific handler for SQLSTATE '22001', as well as a general handler for SQLEXCEPTION, the specific handler for SQLSTATE '22001' is the most appropriate handler when SQLSTATE '22001' is encountered. In this case, the specific handler is activated.

When a condition handler is activated, the condition handler action is executed. If the handler action completes successfully or with an unhandled warning, the diagnostics area is cleared, and the type of the condition handler (CONTINUE or EXIT handler) determines to where control is returned. Additionally, the SQLSTATE and SQLCODE SQL variables are cleared when a handler completes successfully or with an unhandled warning.

If the handler action does not complete successfully and an appropriate handler exists for the condition that is encountered in the handler action, that condition handler is activated. Otherwise, the condition that is encountered within the condition handler is unhandled.

Unhandled conditions: If a condition is encountered and an appropriate handler does not exist for that condition, the condition is unhandled.

If the unhandled condition is an exception, the SQL routine that contains the failing statement is terminated with an unhandled exception condition.
If the unhandled condition is a warning or is a not found condition, processing continues with the next statement. Note that the processing of the next SQL statement will cause information about the unhandled condition in the diagnostics area to be overwritten, and evidence of the unhandled condition will no longer exist.

Considerations for using SIGNAL and RESIGNAL statements with nested compound statements: If an SQL-procedure-statement that is specified in the condition handler is either a SIGNAL or RESIGNAL statement with an exception SQLSTATE, the compound statement terminates with the specified exception. This happens even when this condition handler or another condition handler in the same compound statement specifies CONTINUE, since these condition handlers are not in the scope of this exception. If a compound statement is nested in another compound statement, condition handlers in the higher level compound statement can handle the exception because those condition handlers are within the scope of the exception.

SQLSTATE and SQLCODE variables in SQL routines: To help debug your SQL routines, you might find it useful to check the SQLSTATE and SQLCODE value after executing a statement. An SQLCODE or SQLSTATE variable can be declared and subsequently referenced in an SQL routine. You could insert the value of the SQLCODE and SQLSTATE into a table at various points in the SQL routine, or return the SQLCODE and SQLSTATE values in a diagnostics string as an OUT parameter. To use the SQLCODE and SQLSTATE values, you must declare the following SQL variables in the SQL routine body:

DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

When you reference the SQLCODE or SQLSTATE variables in an SQL routine, DB2 sets the value of SQLCODE to 0 and SQLSTATE to '00000' for the subsequent statement. You can also use CONTINUE condition handlers to assign the value of the SQLSTATE and SQLCODE variables to variables in your SQL routine body. You can then use these SQL variables to control your routine logic, or pass the value back as an output parameter. In the following example, the SQL routine returns control to the statement following each SQL statement with the SQLCODE set in a SQL variable called RETCODE:

DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE retcode INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET retcode = SQLCODE;
DECLARE CONTINUE HANDLER FOR SQLWARNING SET retcode = SQLCODE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET retcode = SQLCODE;

The compound statement itself does not affect the SQLSTATE and SQLCODE SQL variables. However, SQL statements contained within the compound statement can affect the SQLSTATE and SQLCODE SQL variables. At the end of the compound statement, the SQLSTATE and SQLCODE SQL variables reflect the result of the last SQL statement executed within the compound statement that caused a change to the SQLSTATE and SQLCODE SQL variables. If the SQLSTATE and SQLCODE SQL variables were not changed within the compound statement, they contain the same values as when the compound statement was entered.

Null values in SQL parameters and SQL variables: If the value of an SQL parameter or SQL variable is null and it is used in an SQL statement that does not allow an indicator variable, an error is returned.

Effect on open cursors: At the end of the compound statement, all open cursors that are declared in that compound statement, except cursors that are used to return result sets, are closed.

Atomic processing of a compound statement: Atomic processing is not supported for a compound statement. If atomic behavior is needed for a block of code in a compound statement, set a savepoint before the nested compound statement is entered. This will allow changes to be undone with a ROLLBACK TO SAVEPOINT statement.

Examples

Example 1: Create a procedure body with a compound statement that performs the following actions:

Declares SQL variables.
Declares a cursor to return the salary of employees in a department determined by an IN parameter.
Declares an EXIT handler for the condition NOT FOUND (end of file) which assigns the value 6666 to the OUT parameter medianSalary.
Select the number of employees in the given department into the SQL variable v_numRecords.
Fetch rows from the cursor in a WHILE loop until 50% + 1 of the employees have been retrieved.
Return the median salary.

CREATE PROCEDURE DEPT_MEDIAN 
   (IN deptNumber SMALLINT, 
    OUT medianSalary DOUBLE) 
   LANGUAGE SQL 
   BEGIN 
     DECLARE v_numRecords INTEGER DEFAULT 1; 
     DECLARE v_counter INTEGER DEFAULT 0; 
     DECLARE c1 CURSOR FOR 
        SELECT salary FROM staff 
          WHERE DEPT = deptNumber 
          ORDER BY salary; 
     DECLARE EXIT HANDLER FOR NOT FOUND 
        SET medianSalary = 6666; 
        /* initialize OUT parameter */ 
        SET medianSalary = 0; 
        SELECT COUNT(*) INTO v_numRecords FROM staff 
           WHERE DEPT = deptNumber; 
        OPEN c1; 
        WHILE v_counter < (v_numRecords / 2 + 1) DO 
           FETCH c1 INTO medianSalary;
           SET v_counter = v_counter + 1; 
        END WHILE; 
        CLOSE c1; 
   END

Example 2: Define an exit handler for any error, warning, or case of end of data. When this procedure is invoked and it returns to the caller, the value 45000 is returned for the output parameter:

CREATE PROCEDURE JMBLIB.PROCL(OUT MEDIANSALARY INT)
  LANGUAGE SQL
  BEGIN
   DECLARE CHAR1 CHAR;
   DECLARE C1 CURSOR FOR SELECT * 
     FROM SYSIBM.SYSDUMMY1;
   DECLARE EXIT HANDLER FOR NOT FOUND, 
        SQLEXCEPTION, 
        SQLWARNING;
   OPEN C1;
   FETCH C1 INTO CHAR1;
   SET MEDIANSALARY = 45000;
   FETCH C1 INTO CHAR1;
   SET MEDIANSALARY = 50000;
  END