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.
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.
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.
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.
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.
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:
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:
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.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.