compound (dynamic)

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

Invocation

This statement can be issued interactively. It is an executable statement that can be dynamically prepared. It cannot be embedded in an application program.

Authorization

The privileges held by the authorization ID of the statement must also include all of the privileges necessary to invoke the SQL statements that are specified in the compound (dynamic) statement.

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

ATOMIC

ATOMIC indicates that an unhandled exception condition within the compound (dynamic) statement causes the compound statement to be rolled back. If ATOMIC is specified, COMMIT, ROLLBACK (ROLLBACK TO SAVEPOINT may be specified), SET CURRENT DEBUG MODE, SET CURRENT DEGREE, SET CURRENT DECFLOAT ROUNDING MODE, SET CURRENT IMPLICIT XMLPARSE OPTION, Start of change

SET CURRENT TEMPORAL SYSTEM_TIME, End of change

SET PATH, and SET SCHEMA statements cannot be specified in the compound statement.

NOT ATOMIC

NOT ATOMIC indicates that an unhandled exception condition within the compound (dynamic) statement does not causes the compound statement to be rolled back.

SQL-variable-declaration

Declares a variable that is local to the compound (dynamic) 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 (dynamic) statement, excluding any declarations in compound-statements nested within the compound (dynamic) statement. Do not name SQL variables the same as a column name. 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 (dynamic) statement is invoked. 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.
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.

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 (dynamic) statement, excluding any declarations in compound-statements nested within the compound (dynamic) 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 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 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 compound (dynamic) statement. The cursor name must be unique within the compound (dynamic) statement, excluding any declarations in compound-statements nested within the compound (dynamic) statement.

A cursor-name can only be referenced within the compound (dynamic) statement in which it is declared, including any compound-statements nested within the compound (dynamic) statement.

Use an OPEN statement to open the cursor, and a FETCH statement to read rows using the cursor. Any open cursor is closed at the end of the compound (dynamic) 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 (dynamic) 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 (dynamic) 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 (dynamic) statement, including any nested compound-statements.

A handler for a condition may exist at several levels of nested compound statements. For example, assume that compound (dynamic) 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 (dynamic) statement.
UNDO: Specifies that when the condition handler is activated changes made by the compound (dynamic) statement are rolled back. When the handler completes successfully, control is returned to the end of the compound (dynamic) statement. If UNDO is specified, then ATOMIC must be specified.

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'.

SQL-procedure-statement

An SQL statement or SQL control statement as defined in SQL-procedure-statement. The SET RESULT SETS and SET SESSION AUTHORIZATION SQL statements are not allowed.

Notes

Compound (dynamic) content: See SQL control statements for more information on the constructs that can be used in a compound (dynamic) statement.

Nesting compound statements: Compound-statements can be nested within a compound (dynamic) statement. 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 (dynamic) 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.

Compound (dynamic) statement execution: When a compound (dynamic) statement is dynamically prepared and executed, the statements within the compound statement are processed as static statements. A temporary program that embeds the statements for the compound statement is created and then executed. The program name is QTEMP.QCMPDnnnnn, where nnnnn is a unique number for the job.

CURRENT PATH and CURRENT SCHEMA: The current schema and current path apply to the prepared and executed compound (dynamic) statement. Any change to these special registers within the compound statement does not affect subsequent resolution of statements within the same compound statement. For example:

SET SCHEMA prodlib;
SET stmt =  'BEGIN  SET SCHEMA datalib;  INSERT INTO test_table VALUES(1);  END';
PREPARE compound_stmt FROM stmt;
EXECUTE compound_stmt;

When the compound statement compound_stmt is prepared and executed, the INSERT will resolve to PRODLIB.TEST_TABLE, not DATALIB.TEST_TABLE.

Most end user SQL script interfaces use dynamic SQL to execute SQL statements. For example, executing the following two statements in IBM® i Navigator's Run SQL Scripts is logically equivalent to the above example. The INSERT will resolve to PRODLIB.TEST_TABLE, not DATALIB.TEST_TABLE.

SET SCHEMA prodlib;
BEGIN
  SET SCHEMA datalib;
  INSERT INTO test_table VALUES(1);
END;

Condition handlers: Condition handlers in a compound (dynamic) 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 routine body.

A condition handler is declared within a compound (dynamic) statement, and it is active for the set of SQL-procedure-statements that follow all of the condition handler declarations within the compound (dynamic) statement. 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 (dynamic) 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 (dynamic) 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 compound (dynamic) statement 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 compound statement 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 (dynamic) statement terminates with the specified exception. This happens even if this condition handler or another condition handler in the same compound (dynamic) statement specifies CONTINUE, since these condition handlers are not in the scope of this exception. If a compound-statement is nested in a compound (dynamic) statement, condition handlers in the compound (dynamic) statement may handle the exception from within the compound-statement because those condition handlers are within the scope of the exception.

Null values in SQL variables: If the value of an 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.

Uncommitted changes: Any uncommitted transactional work is scoped to the activation group of the connection in which the dynamic compound statement is run.

Effect on open cursors: Upon exit from the compound (dynamic) statement for any reason, all open cursors that are declared in that compound statement are closed.

Considerations for SQLSTATE and SQLCODE SQL variables: The compound (dynamic) 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 (dynamic) statement, they contain the same values as when the compound (dynamic) statement was invoked.