compound-statement

id="compoundstmt">compound-statement

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

Syntax
                      .-NOT ATOMIC-.
>>-+--------+--BEGIN--+------------+---------------------------->
   '-label:-'         '-ATOMIC-----'
 
>--+--------------------------------------+--------------------->
   | .----------------------------------. |
   | V                                  | |
   '---+-SQL-variable-declaration-+-- ;-+-'
       +-condition-declaration----+
       '-return-codes-declaration-'
 
>--+----------------------------------+------------------------->
   | .------------------------------. |
   | V                              | |
   '---DECLARE CURSOR-statement-- ;-+-'
 
>--+-----------------------------+------------------------------>
   | .-------------------------. |
   | V                         | |
   '---handler-declaration-- ;-+-'
 
   .---------------------------------.
   V                                 |
>----+-----------------------------+-+--END--+-------+---------><
     '-SQL-procedure-statement-- ;-'         '-label-'
 
SQL-variable-declaration:  
            .-,-----------------.
            V                   |
|--DECLARE----SQL-variable-name-+------------------------------->
 
              .-DEFAULT NULL-------------------------.
>--data-type--+--------------------------------------+----------|
              '-DEFAULT--constant--+---------------+-'
                                   |          (1)  |
                                   '-NOT NULL------'
 
condition-declaration:  
|--DECLARE--SQL-condition-name---------------------------------->
 
                               .-VALUE-.
                   .-SQLSTATE--+-------+-.
>--CONDITION--FOR--+---------------------+--string-constant-----|
 
return-codes-declaration:  
|--DECLARE------------------------------------------------------>
 
                                 .-DEFAULT--'00000'---------.
>--+-SQLSTATE--+-CHARACTER(5)-+--+--------------------------+-+--|
   |           '-CHAR(5)------'  '-DEFAULT--string-constant-' |
   |                       .-DEFAULT--0----------------.      |
   '-SQLCODE--+-INTEGER-+--+---------------------------+------'
              '-INT-----'  '-DEFAULT--integer-constant-'
 
handler-declaration:  
|--DECLARE--+-CONTINUE-+--HANDLER FOR--------------------------->
            +-EXIT-----+
            '-UNDO-----'
 
                                (2)
>--+-specific-condition-value-+-------SQL-procedure-statement---|
   '-general-condition-value--'
 
specific-condition-value:  
   .-,-------------------------------.
   V             .-VALUE-.           |
|----+-SQLSTATE--+-------+--string-+-+--------------------------|
     '-condition-name--------------'
 
general-condition-value:  
|--+-SQLEXCEPTION-+---------------------------------------------|
   +-SQLWARNING---+
   '-NOT FOUND----'
 
data-type:  
|--+-built-in-type------+---------------------------------------|
   '-distinct-type-name-'
 
Notes:

The DEFAULT and NOT NULL clauses can be specified in either order.

specific-condition-value and general-condition-value cannot be specified in the same handler declaration.
built-in-type:  
|--+-+---SMALLINT---+-----------------------------------------------------------------+--|
   | +-+-INTEGER-+--+                                                                 |
   | | '-INT-----'  |                                                                 |
   | '---BIGINT-----'                                                                 |
   |                  .-(--5,0--)--------------------.                                |
   +-+-+-DECIMAL-+-+--+------------------------------+--------------------------------+
   | | '-DEC-----' |  |             .-,0--------.    |                                |
   | '-NUMERIC-----'  '-(--integer--+-----------+--)-'                                |
   |                                '-, integer-'                                     |
   |          .-(--53--)------.                                                       |
   +-+-FLOAT--+---------------+-+-----------------------------------------------------+
   | |        '-(--integer--)-' |                                                     |
   | +-REAL---------------------+                                                     |
   | |         .-PRECISION-.    |                                                     |
   | '-DOUBLE--+-----------+----'                                                     |
   |                    .-(--1--)-------.                                             |
   +-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+------------+-+
   | | | '-CHAR------'  '-(--integer--)-'          |  +-FOR BIT DATA---+            | |
   | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)-'  +-FOR SBCS DATA--+            | |
   | |   | '-CHAR------'          |                   +-FOR MIXED DATA-+            | |
   | |   '-VARCHAR----------------'                   '-CCSID--integer-'            | |
   | |                                 .-(--1M--)-------------.                     | |
   | '---+-CLOB-------------------+----+----------------------+--+----------------+-' |
   |     +-CHAR LARGE OBJECT------+    '-(--integer--+---+--)-'  +-FOR SBCS DATA--+   |
   |     '-CHARACTER LARGE OBJECT-'                  +-K-+       +-FOR MIXED DATA-+   |
   |                                                 +-M-+       '-CCSID--integer-'   |
   |                                                 '-G-'                            |
   |                .-(--1--)-------.                                                 |
   +-+---GRAPHIC----+---------------+-------+--+----------------+---------------------+
   | |              '-(--integer--)-'       |  '-CCSID--integer-'                     |
   | +-+-GRAPHIC VARYING-+--(--integer--)---+                                         |
   | | '-VARGRAPHIC------'                  |                                         |
   | |             .-(--1M--)-------------. |                                         |
   | '---DBCLOB----+----------------------+-'                                         |
   |               '-(--integer--+---+--)-'                                           |
   |                             +-K-+                                                |
   |                             +-M-+                                                |
   |                             '-G-'                                                |
   |             .-(--1--)-------.                                                    |
   +-+-+-BINARY--+---------------+---------+-----------------+------------------------+
   | | |         '-(--integer--)-'         |                 |                        |
   | | '-+-BINARY VARYING-+--(--integer--)-'                 |                        |
   | |   '-VARBINARY------'                                  |                        |
   | |                              .-(--1M--)-------------. |                        |
   | '---+-BLOB----------------+----+----------------------+-'                        |
   |     '-BINARY LARGE OBJECT-'    '-(--integer--+---+--)-'                          |
   |                                              +-K-+                               |
   |                                              +-M-+                               |
   |                                              '-G-'                               |
   +-+-DATE------+--------------------------------------------------------------------+
   | +-TIME------+                                                                    |
   | '-TIMESTAMP-'                                                                    |
   |             .-(--200--)-----.                                                    |
   +---DATALINK--+---------------+--+----------------+--------------------------------+
   |             '-(--integer--)-'  '-CCSID--integer-'                                |
   '---ROWID--------------------------------------------------------------------------'
 
Description

label

Specifies the label for the compound-statement 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 the beginning label. The label name cannot be the same as the routine name or another label within the same scope. For more information, see Labels.

ATOMIC

ATOMIC indicates that an unhandled exception condition within the compound-statement causes the compound-statement to be rolled back. If ATOMIC is specified, COMMIT or ROLLBACK statements cannot be specified in the compound statement (ROLLBACK TO SAVEPOINT may be specified).

NOT ATOMIC

NOT ATOMIC indicates that an unhandled exception within the compound-statement does not causes the compound-statement to be rolled back. If NOT ATOMIC is specified in the outermost compound statement of an SQL trigger, it is treated as ATOMIC.

SQL-variable-declaration

Declares a variable that is local to the compound statement.

SQL-variable-name

Defines the name of a local variable. The database manager converts all undelimited SQL variable names to uppercase. The SQL-variable-name must be unique within the compound-statement, excluding any declarations in compound-statements nested within the compound-statement. SQL variable names should not be the same as column names and cannot be the same as SQL parameter names. See References to SQL parameters and SQL variables for how SQL variable names are resolved when there are columns with the same name involved in a statement. Variable names should not begin with 'SQL'.
An SQL-variable-name can only be referenced within the compound-statement in which it is declared, including any compound-statements nested within the compound-statement. If the compound statement where the variable is declared is labeled, then references to the variable name can be qualified with that label. For example, SQL variable V declared in a compound statement labeled C can be referred to as C.V.

data-type

Specifies the data type of the variable. See CREATE TABLE for a description of data type.
If the data-type is a graphic string data type, consider specifying CCSID 1200 or 13488 to indicate UTF-16 or UCS-2 data. If a CCSID is not specified, the CCSID of the graphic string variable will be the associated DBCS CCSID for the job.

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 Assignments and comparisons. The variable is initialized when the compound-statement in which it is declared is entered. If a default value is not specified, the SQL variable is initialized to NULL.

NOT NULL

Prevents the SQL variable from containing the NULL value. Omission of NOT NULL implies that the column can be null.

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 compound-statement, excluding any declarations in compound-statements nested within the compound-statement.
A condition-name can only be referenced within the compound-statement in which it is declared, including any compound-statements nested within the compound-statement.

FOR SQLSTATE string-constant

Specifies the SQLSTATE associated with this condition. The string constant must be specified as 5 characters. The SQLSTATE class (the first two characters) '00'.

return-codes-declaration

Declares special variables called SQLSTATE and SQLCODE that are set automatically to the SQL return codes returned after executing an SQL statement. Both the SQLSTATE and SQLCODE variables can only be declared in the outermost compound-statement of an SQL procedure, SQL function, or SQL trigger.
Assignment to these variables is not prohibited. However, the assignment will not be useful since the next SQL statement will replace the assigned value. The SQLCODE and SQLSTATE variables cannot be set to NULL.
SQLCODE and SQLSTATE variables should be saved immediately to another SQL variable if there is any intention to use the values. If a handler exists for the SQLSTATE, this assignment must be the first statement in the handler to avoid having the value replaced by the next SQL procedure statement.

DECLARE CURSOR-statement

Declares a cursor in the routine body. The cursor name must be unique within the compound-statement, excluding any declarations in compound-statements nested within the compound-statement.
A cursor-name can only be referenced within the compound-statement in which it is declared, including any compound-statements nested within the compound-statement.
Use an OPEN statement to open the cursor, and a FETCH statement to read rows using the cursor. If the cursor in an SQL procedure and is intended for use as a result set:

specify WITH RETURN when declaring the cursor

create the procedure using the DYNAMIC RESULT SETS clause with a non-zero value

do not specify a CLOSE statement in the compound-statement.
Any open cursor that does not meet these criteria is closed at the end of the compound-statement.
For more information on declaring a cursor, refer to DECLARE CURSOR.

handler-declaration

Specifies a handler, an SQL-procedure-statement to execute when an exception or completion condition occurs in the compound-statement.
A condition handler declaration cannot reference the same condition value or SQLSTATE value more than once, and cannot reference an SQLSTATE value and a condition name that represent the same SQLSTATE value. For a list of SQLSTATE values as well as more information, see the SQL Programming book.
Furthermore, when two or more condition handlers are declared in a compound statement, no two condition handler declarations may specify the same:

general condition category or

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 handler-declarations within the compound-statement in which it is declared, including any nested compound statements.
A handler for a condition may exist at several levels of nested compound statements. For example, assume that compound statement n1 contains another compound statement n2 which contains another compound statement n3. When an exception condition occurs within n3, any active handlers within n3 are first allowed to handle the condition. If no appropriate handler exists in n3, then the condition is resignalled to n2 and the active handlers within n2 may handle the condition. If no appropriate handler exists in n2, then the condition is resignalled to n1 and the active handlers within n1 may handle the condition. If no appropriate handler exists in n1, the condition is considered unhandled.
There are three types of condition handlers:

CONTINUE

Specifies that after the condition handler is activated and completes successfully, control is returned to the SQL statement following the one that raised the exception. If the error occurs while executing a comparison as in an IF, CASE, FOR, WHILE, or REPEAT, control returns to the statement following the corresponding END IF, END CASE, END FOR, END WHILE, or END REPEAT.

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.

UNDO

Specifies that when the condition handler is activated changes made by the compound-statement are rolled back. When the handler completes successfully, control is returned to the end of the compound-statement. If UNDO is specified, then ATOMIC must be specified.
UNDO cannot be specified in the outermost compound-statement of an SQL function or SQL trigger.

The conditions under which the handler is activated are:

SQLSTATE string

Specifies that the handler is invoked when the specific SQLSTATE condition occurs. The SQLSTATE class (the first two characters) '00'.

condition-name

Specifies that the handler is invoked when the condition occurs. The condition name must be previously defined in a condition-declaration.

SQLEXCEPTION

Specifies that the handler is invoked when an exception condition occurs. An exception condition is represented by an SQLSTATE value where the first two characters are not '00', '01', or '02'.

SQLWARNING

Specifies that the handler is invoked when a warning condition occurs. A warning condition is represented by an SQLSTATE value where the first two characters are '01'.

NOT FOUND

Specifies that the handler is invoked when a NOT FOUND condition occurs. A NOT FOUND condition is represented by an SQLSTATE value where the first two characters are '02'.

The same condition cannot be specified more than once in the handler-declaration.
If the SQL-procedure-statement specified in the handler is either a SIGNAL or RESIGNAL statement with an exception SQLSTATE, the compound-statement will exit with the specified exception even if this handler or another handler in the same compound-statement specifies CONTINUE, since these handlers are not in the scope of this exception. If the compound-statement is nested in another compound-statement, handlers in the higher level compound-statement may handle the exception because those handlers are within the scope of the exception.

Notes

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 the compound-statement. This can simplify the processing done for each SQL procedure statement. Support for nested compound statements enables the use of a compound statement within the declaration of a condition handler.
Condition handlers: Condition handlers in a compound-statement are similar to WHENEVER statements 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 a result of an exception, warning, or not found condition that is returned by the database manager for the processing of an SQL statement, or the activating condition can be the result of a SIGNAL or RESIGNAL statement issued within the procedure 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. To be more specific, the scope of a condition handler declaration H is the list of SQL-procedure-statements that follows the condition handler declarations contained within the compound statement in which H appears. This means that the scope of H does not include the statements 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 declared in the same compound statement, H1 will not handle conditions arising in the body of H2, and H2 will not handle conditions arising in the body of H1.
The declaration of a condition handler specifies the condition that activates it, the type of the condition handler (CONTINUE, EXIT, or UNDO), and the handler action. The type of the condition handler determines where control is returned to after successful completion of the handler action.
Condition handler activation: When a condition other than successful completion occurs in the processing of an 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 may 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 that nesting level could handle the condition, the condition handler that is activated is the most appropriate handler declared in that compound statement.
The most appropriate handler is a handler that is defined in the compound-statement which most closely matches the SQLSTATE of the exception or completion condition.
For example, if the innermost compound statement declares a specific handler for SQLSTATE 22001 as well as a handler for SQLEXCEPTION, the specific handler for SQLSTATE 22001 is the most appropriate handler when an 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, the type of the condition handler (CONTINUE, EXIT, or UNDO handler) determines where control is returned to.
If the handler action does not complete successfully, and an appropriate handler exists for the condition encountered in the handler action, that condition handler is activated. Otherwise, the condition encountered within the condition handler is unhandled.
Unhandled conditions If a condition is encountered such that an appropriate handler does not exist for that condition, the condition is unhandled.

If the unhandled condition is an exception, the SQL procedure, SQL function, or SQL trigger containing the failing statement is terminated with an unhandled exception condition.

If the unhandled condition is a warning or 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 or RESIGNAL statements with nested compound statements: If an SQL-procedure-statement 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 if 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 the compound statement is nested in another compound statement, condition handlers in the higher level compound statement may handle the exception because those condition handlers are within the scope of the exception.
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 (such as CONNECT or DESCRIBE) that does not allow an indicator variable, an error is returned.
Effect on open cursors: Upon exit from the compound-statement for any reason, all open cursors that are declared in that compound statement are closed, unless they are declared to return result sets.

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.

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
[ Top of Page | Previous Page | Next Page | Contents |
Index ]