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

SQL-variable-declaration

SQL-condition-declaration

return-codes-declaration

handler-declaration

specific-condition-value

general-condition-value

data-type

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.
³ The same clause must not be specified more than once.

built-in-type

ccsid-clause

Description

label

Specifies the label for the compound-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 References to SQL 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 condition within the compound-statement does not cause 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.

If ATOMIC is specified in an SQL procedure or function, the routine must have a data classification of MODIFIES SQL DATA.

SQL-variable-declaration

Declares a variable that is local to the compound-statement.

SQL-variable-name: Defines the name of a local SQL variable. The database manager converts all undelimited SQL variable names to uppercase. The name must not be the same as another SQL variable within the same compound-statement, excluding any declarations in compound-statements nested within the compound-statement. Do not name SQL variables the same as column names or 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'.
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.
array-type-name: Specifies that the SQL variable is an array as defined with the CREATE TYPE (Array) statement.
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. SQL variables of type XML cannot have a default value specified. SQL variables of array-type are always initialized to NULL.
NOT NULL: Prevents the SQL variable from containing the NULL value. Omission of NOT NULL implies that the variable can be null. SQL variables of type XML cannot have NOT NULL specified.
CONSTANT constant or NULL: Specifies that the SQL variable has a fixed value that cannot be changed. An SQL variable that is defined using CONSTANT cannot be used as the target of any assignment operation. 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.
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 (excluding any declarations in compound-statements nested within the compound-statement ) in which it is declared.
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 2 characters) must not be '00'.

return-codes-declaration

Declares special SQL variables called SQLSTATE and SQLCODE that are set 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 variables can only be declared in the outermost compound-statement of an SQL procedure, SQL function, or SQL trigger.

The SQLSTATE and SQLCODE special 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 special variables is ignored by condition handlers. The SQLSTATE and SQLCODE special variables cannot be set to NULL.

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 about 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 messages and codes topic collection.

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 occurs. The SQLSTATE class (the first 2 characters) must not be '00'.
SQL-condition-name: Specifies that the handler is invoked when the specific SQLSTATE associated with the condition name occurs. The SQL-condition-name must be previously defined in a SQL-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'.

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 or with an unhandled warning, the diagnostics area is cleared, and the type of the condition handler (CONTINUE, EXIT, or UNDO handler) determines 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 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 and 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 or unless *ENDACTGRP is specified.

Considerations for SQLSTATE and SQLCODE SQL variables: 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 that compound statement that caused a change to the SQLSTATE and SQLCODE SQL variables. If the SQLSTATE and SQLCODE variables were not changed within the compound statement, they contain the same values as when the compound statement was entered.

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