A compound statement groups other statements together in an SQL procedure. A compound statement allows the declaration of SQL variables, cursors, and condition handlers.
.-NOT ATOMIC-. >>-+--------+--BEGIN--+------------+----------------------------> '-label:-' '-ATOMIC-----' >--+---------------------------------------+--------------------> | .-----------------------------------. | | V | | '---+-SQL-variable-declaration--+-- ; +-' +-SQL-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-----' | | .-DEFAULT NULL-. | +-array-type-name--+--------------+------------------+ '-RESULT_SET_LOCATOR VARYING-------------------------' SQL-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-+-+--------------------------| '-SQL-condition-name----------' general-condition-value |--+-SQLEXCEPTION-+---------------------------------------------| +-SQLWARNING---+ '-NOT FOUND----' data-type |--+-built-in-type------+---------------------------------------| '-distinct-type-name-'
built-in-type |--+-+---SMALLINT---+-----------------------------------------------------------------+--| | +-+-INTEGER-+--+ | | | '-INT-----' | | | '---BIGINT-----' | | .-(--5,0--)--------------------. | +-+-+-DECIMAL-+-+--+------------------------------+--------------------------------+ | | '-DEC-----' | | .-,0--------. | | | '-+-NUMERIC-+-' '-(--integer--+-----------+--)-' | | '-NUM-----' '-, integer-' | | .-(--53--)------. | +-+-FLOAT--+---------------+-+-----------------------------------------------------+ | | '-(--integer--)-' | | | +-REAL---------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+----' | | .-(--34--)-. | +---DECFLOAT--+----------+---------------------------------------------------------+ | '-(--16--)-' | | .-(--1--)-------. | +-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+------------+-+ | | | '-CHAR------' '-(--integer--)-' | +-FOR BIT DATA---+ | | | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)-' +-FOR SBCS DATA--+ | | | | | '-CHAR------' | +-FOR MIXED DATA-+ | | | | '-VARCHAR----------------' '-ccsid-clause---' | | | | .-(--1M--)-------------. | | | '---+-CLOB-------------------+----+----------------------+--+----------------+-' | | +-CHAR LARGE OBJECT------+ '-(--integer--+---+--)-' +-FOR SBCS DATA--+ | | '-CHARACTER LARGE OBJECT-' +-K-+ +-FOR MIXED DATA-+ | | +-M-+ '-ccsid-clause---' | | '-G-' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+-------+--+--------------+-----------------------+ | | '-(--integer--)-' | '-ccsid-clause-' | | +-+-GRAPHIC VARYING-+--(--integer--)---+ | | | '-VARGRAPHIC------' | | | | .-(--1M--)-------------. | | | '---DBCLOB----+----------------------+-' | | '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-+-NATIONAL CHARACTER-+--+---------------+----------+---------------------+---+ | | | +-NATIONAL CHAR------+ '-(--integer--)-' | | | | | | '-NCHAR--------------' | | | | | '-+-+-NATIONAL CHARACTER-+--VARYING-+--(--integer--)-' | | | | | +-NATIONAL CHAR------+ | | | | | | '-NCHAR--------------' | | | | | '-NVARCHAR------------------------' | | | | .-(--1M--)-------------. | | | '-----+-+-NATIONAL CHARACTER-+--LARGE OBJECT-+------+----------------------+-' | | | '-NCHAR--------------' | '-(--integer--+---+--)-' | | '-NCLOB--------------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-BINARY--+---------------+---------+-----------------+------------------------+ | | | '-(--integer--)-' | | | | | '-+-BINARY VARYING-+--(--integer--)-' | | | | '-VARBINARY------' | | | | .-(--1M--)-------------. | | | '---+-BLOB----------------+----+----------------------+-' | | '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------+-------------------------------------------------------+ | | .-(--0--)-. | | | +-TIME--+---------+------+ | | | .-(--6--)-. | | | '-TIMESTAMP--+---------+-' | | .-(--200--)-----. | +---DATALINK--+---------------+--+--------------+----------------------------------+ | '-(--integer--)-' '-ccsid-clause-' | +---ROWID--------------------------------------------------------------------------+ '---XML----------------------------------------------------------------------------' ccsid-clause |--CCSID--integer-----------------------------------------------|
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.
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.
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
RESULT_SET_LOCATOR VARYING
Specifies the data type for a result set locator variable.
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.
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.
For more information about declaring a cursor, refer to DECLARE CURSOR.
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:
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:
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:
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.
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.
Create a procedure body with a compound statement that performs the following actions.
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