SQL Reference

Compound Statement

A compound statement groups other statements together in an SQL procedure. You can declare SQL variables, cursors, and condition handlers within a compound statement.

Syntax

                         .-NOT ATOMIC--.
>>-+---------+--BEGIN----+-------------+------------------------>
   '-label:--'           '-ATOMIC------'
 
>-----+-----------------------------------------------+--------->
      |  .-----------------------------------------.  |
      |  V                                         |  |
      '-----+-| SQL-variable-declaration |-+---;---+--'
            +-| condition-declaration |----+
            '-| return-codes-declaration |-'
 
>-----+-------------------------------------+------------------->
      |  .-------------------------------.  |
      |  V                               |  |
      '----DECLARE-CURSOR-statement--;---+--'
 
>-----+------------------------------------+-------------------->
      |  .------------------------------.  |
      |  V                              |  |
      '----| handler-declaration |--;---+--'
 
      .-------------------------------.
      V                               |
>--------SQL-procedure-statement--;---+---END--+--------+------><
                                               '-label--'
 
SQL-variable-declaration
 
               .-,--------------------.
               V                      |
|---DECLARE-------SQL-variable-name---+------------------------->
 
                     .-DEFAULT NULL-------.
>-----+-data-type----+--------------------+-+-------------------|
      |              '-DEFAULT--constant--' |
      '-RESULT_SET_LOCATOR--VARYING---------'
 
condition-declaration
 
|---DECLARE--condition-name--CONDITION--FOR--------------------->
 
                 .-VALUE-.
     .-SQLSTATE--+-------+---.
>----+-----------------------+---string-constant----------------|
 
return-codes-declaration
 
|---DECLARE----+-SQLSTATE--CHAR (5)--+---+--------------------+-|
               '-SQLCODE--INTEGER----'   '-DEFAULT--constant--'
 
handler-declaration
 
|---DECLARE----+-CONTINUE-+---HANDLER--FOR---------------------->
               +-EXIT-----+
               '-UNDO-----'
 
      .-,-----------------------------------.
      V               .-VALUE-.             |
>---------+-SQLSTATE--+-------+--string--+--+------------------->
          +-condition-name---------------+
          +-SQLEXCEPTION-----------------+
          +-SQLWARNING-------------------+
          '-NOT FOUND--------------------'
 
>----SQL-procedure-statement------------------------------------|

Description

label

Defines the label for the code block. If the beginning label is specified, it can be used to qualify SQL variables declared in the compound statement and can also be specified on a LEAVE statement. If the ending label is specified, it must be the same as the beginning label.

ATOMIC or NOT ATOMIC

ATOMIC indicates that if an error occurs in the compound statement, all SQL statements in the compound statement will be rolled back. NOT ATOMIC indicates that an error 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 local variable. DB2 converts all SQL variable names to uppercase. The name cannot be the same as another SQL variable within the same compound statement and cannot be the same as a parameter name. SQL variable names should not be the same as column names. If an SQL statement contains an identifier with the same name as an SQL variable and a column reference, DB2 interprets the identifier as a column.
data-type: Specifies the data type of the variable. Refer to Data Types for a description of SQL data types. User-defined data types, graphic types, and FOR BIT DATA are not supported.
DEFAULT constant or NULL: Defines the default for the SQL variable. The variable is initialized when the SQL procedure is called. If a default value is not specified, the variable is initialized to NULL.
RESULT_SET_LOCATOR VARYING: Specifies the data type for a result set locator variable.

condition-declaration

Declares a condition name and corresponding SQLSTATE value.

condition-name: Specifies the name of the condition. The condition name must be unique within the procedure body and can be referenced only within the compound statement in which it is declared.
FOR SQLSTATE string-constant: Specifies the SQLSTATE that is associated with the condition. The string-constant must be specified as five characters enclosed in single quotes, and cannot be '00000'.

return-codes-declaration

Declares special variables called SQLSTATE and SQLCODE that are set automatically to the value returned after processing an SQL statement. Both the SQLSTATE and SQLCODE variables can only be declared in the outermost compound statement of the SQL procedure body. These variables may be declared only once per SQL procedure.

declare-cursor-statement

Declares a cursor in the procedure body. Each cursor must have a unique name. The cursor can be referenced only from within the compound statement. Use an OPEN statement to open the cursor, and a FETCH statement to read rows using the cursor. To return result sets from the SQL procedure to the client application, the cursor must be declared using the WITH RETURN clause. The following example returns one result set to the client application:

     CREATE PROCEDURE RESULT_SET()
        LANGUAGE SQL
        RESULT SETS 1
        BEGIN
           DECLARE C1 CURSOR WITH RETURN FOR 
              SELECT id, name, dept, job 
                 FROM staff;
           OPEN C1;
        END

Note: To process result sets, you must write your client application using one of the DB2 Call Level Interface (DB2 CLI), Open Database Connectivity (ODBC), Java Database Connectivity (JDBC), or embedded SQL for Java (SQLJ) application programming interfaces.

For more information on declaring a cursor, refer to DECLARE CURSOR.

handler-declaration

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

A handler is active only within the compound statement in which it is declared.

There are three types of condition handlers:

CONTINUE: After the handler is invoked successfully, control is returned to the SQL statement that follows the statement that raised the exception. If the error that raised the exception is a FOR, IF, CASE, WHILE, or REPEAT statement (but not an SQL-procedure-statement within one of these), then control returns to the statement that follows END FOR, END IF, END CASE, END WHILE, or END REPEAT.
EXIT: After the handler is invoked successfully, control is returned to the end of the compound statement that declared the handler.
UNDO: Before the handler is invoked, any SQL changes that were made in the compound statement are rolled back. After the handler is invoked successfully, control is returned to the end of the compound statement that declared the handler. If UNDO is specified, then ATOMIC must be specified.

The conditions under which the handler is activated:

SQLSTATE string: Specifies an SQLSTATE for which the handler is invoked. The SQLSTATE cannot be '00000'.
condition-name: Specifies a condition name for which the handler is invoked. The condition name must be previously defined in a condition declaration.
SQLEXCEPTION: Specifies that the handler is invoked when an SQLEXCEPTION occurs. An SQLEXCEPTION is an SQLSTATE where the first two characters are not "00", "01", or "02".
SQLWARNING: Specifies that the handler is invoked when an SQLWARNING occurs. An SQLWARNING is an SQLSTATE where the first two characters are "01".
NOT FOUND: Specifies that the handler is invoked when a NOT FOUND condition occurs. NOT FOUND corresponds to an SQLSTATE where the first two characters are "02".

Rules

ATOMIC compound statements cannot be nested.
The following rules apply to handler declarations:
- A handler declaration that contains SQLEXCEPTION, SQLWARNING, or NOT FOUND cannot contain additional SQLSTATE or condition names.
- Handler declarations within the same compound statement cannot contain duplicate conditions.
- A handler declaration cannot contain the same condition code or SQLSTATE value more than once, and cannot contain an SQLSTATE value and a condition name that represent the same SQLSTATE value. For a list of SQLSTATE values and more information, refer to the Message Reference.
- A handler is activated when it is the most appropriate handler for an exception or completion condition. The most appropriate handler is a handler (for the exception or completion condition) that is defined in the compound statement, nearest in scope to the statement with the exception or completion condition. If an exception occurs for which there is no handler, execution of the compound statement is terminated.

Examples

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. In the SELECT statement, casts the data type of the salary column from a DECIMAL into a DOUBLE.
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 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 CAST(salary AS DOUBLE) 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

[ Top of Page | Previous Page | Next Page ]