Specifies the statement label for a compound statement.
Specifies the beginning of a compound statement.
- SQL-variable-declaration::=DECLARE SQL-variable-name[, SQL-variable-name] ...data-type [DEFAULT clause]
Declares the SQL variables that are used in the compound statement. If an SQL variable is allocated, the default value for the SQL variable is assigned as an initial value. The default for the SQL variable is specified in the DEFAULT clause. If the DEFAULT column is omitted, the default for the SQL variable is the null value.
- SQL-variable-name
- Specifies the name of the SQL variable being declared.
- data-type
- Specifies the data type of the SQL variable being declared.
- Rules on SQL variable declarations
- The name of the SQL variable declared in SQL-variable-declaration must be distinct from any parameter names used in the routine.
- The name of the SQL variable declared in SQL-variable-declaration that is directly included in the same compound statement cannot be specified in duplicate.
- The SQL variable declared in a compound statement is allocated at the beginning of the compound statement and is released at the end.
- The scope of an SQL variable is inside the compound statement in which it is declared and in the handler action for a handler declaration that is declared in the same compound statement. If a compound statement is specified in an SQL procedure statement in the compound statement, the SQL variable also remains in effect in the inner compound statement.
- If a compound statement is specified in an SQL procedure statement in a compound statement, and if the SQL variable declared in the outer compound statement is identical to the name of the SQL variable declared in the inner compound statement, the SQL variable declared inside remains in effect in the inner compound statement. When the inner compound statement terminates, the SQL variable declared outside takes effect.
- BOOLEAN cannot be specified as a data type for the SQL variable declared in SQL-variable-declaration.
- DEFAULT clause::= DEFAULT [default-value]
- For rules on the DEFAULT clause, see the rules on the DEFAULT clause in CREATE TABLE (Define table).
- The following operands cannot be specified in default-value: CURRENT_TIMESTAMP[(fractional-second-precision)] USING BES and CURRENT TIMESTAMP[(fractional-second-precision)] USING BES.
Declares the cursor to be used in the compound statement.
- Rules on cursor declarations
- The name of the cursor declared in cursor-declaration that is directly included in the same compound statement cannot be specified in duplicate.
- The cursor declared in a compound statement is allocated at the beginning of the compound statement and is released at the end. However, cursors that were declared by specifying WITH RETURN are not released.
- The scope of a cursor is inside the compound statement in which it is declared and in the handler action for a handler declaration that is declared in the same compound statement. If a compound statement is specified in an SQL procedure statement in the compound statement, the cursor also remains in effect in the inner compound statement.
- If a compound statement is specified in an SQL procedure statement in a compound statement, and if the cursor declared in the outer compound statement is identical to the name of the cursor declared in the inner compound statement, the cursor declared inside remains in effect in the inner compound statement. When the inner compound statement terminates, the cursor declared outside takes effect.
- condition-declaration::=DECLARE condition-name CONDITION [FOR-SQLCODE-value]
Declares a handler declaration, a SIGNAL statement, or the condition name to be used in the RESIGNAL statement, and the associated value of SQLCODE.
- condition-name
- Specifies the name of the condition to be declared.
- FOR-SQLCODE-value
- Specifies the value of SQLCODE to be associated with the condition being declared.
- Rules on condition declarations
- The same condition name cannot be specified in duplicate in another condition declaration that is directly included in the same compound statement.
- The scope of a condition name is inside the compound statement in which it is declared and in the handler action for a handler declaration that is declared in the same compound statement. If a compound statement is specified in an SQL procedure statement in the compound statement, the condition name also remains in effect in the inner compound statement.
- If a compound statement is specified in an SQL procedure statement in a compound statement, and if the condition name declared in the outer compound statement is identical to the condition name declared in the inner compound statement, condition name cannot be declared in the inner compound statement.
- When specifying more than one condition declaration that is directly included in the same compound statement, the value of SQLCODE cannot be specified.
- When declaring the condition name to be used in the SIGNAL or RESIGNAL statement, omit the option FOR-SQLCODE-value; if this is specified, an error may occur.
- handler-declaration::=DECLARE handler-type HANDLER FOR condition-value[, condition-value]... handler-action
Declares the handler that performs exception processing in the compound statement.
When the condition name of the SQLCODE value, SIGNAL statement, or RESIGNAL statement in the results of execution of the SQL statement in the compound statement matches the condition value specified in the handler declaration, the handler receives control and executes the handler-action.
- handler-type::={CONTINUE|EXIT}
- CONTINUE
- Upon execution of handler-action, transfers control to the SQL procedure statement following the SQL procedure statement in which the exception occurred. If the SQL procedure statement in which the exception occurred is an IF or WHILE statement of a routine control SQL statement, control is transferred to the SQL procedure statement following END IF or END WHILE[end-label].
- EXIT
- After the execution of handler-action, transfers control to the end of the compound statement in which the handler declaration was specified.
- condition-value::={SQLERROR|NOT FOUND|condition-name|SQLCODE-value}
Specifies the condition under which the handler takes effect.
- SQLERROR
- This option is specified when calling the handler if SQLERROR occurs. The condition SQLERROR corresponds to the case in which SQLCODE < 0.
- NOT FOUND
- This option is specified to call the handler when NOT FOUND occurs. The NOT FOUND option corresponds with SQLCODE = 100.
- condition-name
- Specifies the condition name for the condition under which a handler is called.
- The condition-name operand must be defined in the condition declaration and must include the handler declaration in its scope.
- If an SQLCODE value corresponding to condition-name is defined in the condition declaration, the handler is called when the SQLCODE matches the value. If an SQLCODE value corresponding to condition-name is not defined in the condition declaration, the handler is called only when the SIGNAL or RESIGNAL statement specifying the condition name is executed.
- SQLCODE-value
- Specifies the value of SQLCODE that indicates the condition under which the handler is called.
- handler-action::=SQL-procedure-statement
- Specifies the SQL statement to be executed when the handler is called.
- Rules on handler declarations
- The scope of a handler is the SQL procedure statement in the compound statement in which the handler is declared. If a compound statement is specified in the SQL procedure statement in the compound statement, the handler is effective in the entire inner compound statement. However, any SQL procedure statement in the handler declaration in the compound statement in which the handler is declared is nullified. An example is shown as follows:
![[Figure]](figure/zu7s0020.gif)
- If either SQLERROR or NOT FOUND is specified in condition-value in the handler declaration, SQLCODE-value or condition-name cannot be specified at the same time.
- In a handler declaration, the same condition value cannot be specified in duplicate. Similarly, a condition name indicating the same SQLCODE as SQLCODE-value cannot be specified.
- A condition value indicating the same condition cannot be specified in another handler declaration that is included in the same compound statement.
- A handler declaration that specified either SQLERROR or NOT FOUND in condition-value is called a general handler declaration; all other handler declarations are called special handler declarations. If a general handler declaration and a special handler declaration specifying a condition value that indicates the same SQL execution status (abnormal termination, normal termination with warning, or no data) are defined in the same compound statement, only the special handler declaration takes effect on the SQLCODE value that was specified in the special handler declaration.
- If a compound statement is specified as an SQL procedure statement in a compound statement, and if Handler A declared in the outer compound statement and Handler B declared in the inner compound statement specify the same SQLCODE or condition name, the inner Handler B prevails in the inner compound statement. Upon termination of the inner compound statement, the outer Handler A takes effect again. An example of this is shown in the following:
![[Figure]](figure/zu7s0010.gif)
- If the handler action does not terminate normally (SQLCODE does not equal 0), and if there is another handler that meets the condition, that handler is called.
- The value is SQLCODE = 0 immediately after the commencement of handler action.
Specifies the SQL procedure statement to be executed in the compound statement.
Specifies the end of the compound statement. In end-label, specify a statement label.
- SQLCODE-value::=SQLCODE[VALUE] integer-literal
Specifies the value of SQLCODE with an integer literal.
The value 0, which indicates normal termination, cannot be specified as a value of SQLCODE. The following table lists the integer literals specified in the value of SQLCODE.
Table 7-2 Integer literals specified in SQLCODE value
Execution status of SQL statement | Value of SQLCODE |
---|
Normal termination (with warning) | > 0 ( 100, 110) |
No data | 100 |
Abnormal termination | < 0 |
The following table lists the messages corresponding to SQLCODE that can appear in HiRDB.
Table 7-3 Messages corresponding to SQLCODE that can appear in HiRDB
SQLCODE | Corresponding message ID |
---|
-yyy | KFPA11yyy |
-1yyy | KFPA19yyy |
-3yyy | KFPA18yyy |
yyy | KFPA12yyy |
+3yyy | KFPA13yyy |