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 Handling errors and warnings.
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 will be initialized when the SQL procedure, SQL function, or SQL trigger is invoked. 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:

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:

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.