Compound statement (Execute multiple statements)

Function

This compound statement executes a group of SQL statements as a single, compound SQL statement.

Format

[starting-label:]
BEGIN
[{SQL-variable-declaration;|cursor-declaration;|condition-declaration;|handler-declaration;}]...
[SQL-procedure-statement;]...

END[end-label]
SQL-variable-declaration::=DECLARE SQL-variable-name[, SQL-variable-name]...data-type [DEFAULT clause]
DEFAULT clause::= DEFAULT [default-value]
condition-declaration::=DECLARE condition-name CONDITION [FOR-SQLCODE-value]
handler-declaration::=DECLARE handler-type
    HANDLER FOR condition-value[, condition-value]... handler-action
handler-type::={CONTINUE|EXIT}
condition-value::={SQLERROR|NOT FOUND|condition-name|SQLCODE-value}
handler-action::=SQL-procedure-statement
SQLCODE-value::=SQLCODE [VALUE] integer-literal

Operands

Specifies the statement label for a compound statement.

Specifies the beginning of a compound statement.

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
  1. The name of the SQL variable declared in SQL-variable-declaration must be distinct from any parameter names used in the routine.
  2. 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.
  3. The SQL variable declared in a compound statement is allocated at the beginning of the compound statement and is released at the end.
  4. 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.
  5. 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.
  6. 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
  1. The name of the cursor declared in cursor-declaration that is directly included in the same compound statement cannot be specified in duplicate.
  2. 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.
  3. 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.
  4. 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.

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
  1. The same condition name cannot be specified in duplicate in another condition declaration that is directly included in the same compound statement.
  2. 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.
  3. 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.
  4. When specifying more than one condition declaration that is directly included in the same compound statement, the value of SQLCODE cannot be specified.
  5. 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.

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
  1. 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]

  2. 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.
  3. 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.
  4. A condition value indicating the same condition cannot be specified in another handler declaration that is included in the same compound statement.
  5. 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.
  6. 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]

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

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 statementValue of SQLCODE
Normal termination (with warning)> 0 ([Figure] 100, 110)
No data100
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

SQLCODECorresponding message ID
-yyyKFPA11yyy
-1yyyKFPA19yyy
-3yyyKFPA18yyy
yyyKFPA12yyy
+3yyyKFPA13yyy

Common rules

  1. If a compound statement is specified in the outermost SQL procedure statement and a begin label is omitted, the routine identifier for that routine is assumed to be the statement label. If a compound statement is specified in an SQL procedure statement in a compound statement, and a begin label for the inner compound statement is omitted, it is assumed that there is no statement label.
  2. When specifying an end label, specify a statement label with the same name as the begin label.
  3. The scope of a statement label is from the beginning of the compound statement in which the statement label is specified at the end of the compound statement. A statement label identical to a statement label for other statements or group variable names included in the compound statement cannot be specified. If a handler declaration is included in the compound statement, that handler declaration is exempted from this rule. The following code provides an example of where statement labels of the same name can or cannot be specified:

    AAA: BEGIN  ...........................................1
     DECLARE CN1 CONDITION FOR SQLCODE VALUE -800;
     DECLARE EXIT HANDLER FOR CN1
     AAA: BEGIN  .........................................2
         :
     END AAA;
     AAA: BEGIN  .........................................3
       DECLARE CN2 CONDITION FOR SQLCODE VALUE -800;
       DECLARE EXIT HANDLER FOR CN2
         :
     END AAA;
       :
    END AAA

    Explanation
    Although the statement label in line 2 is the same name as line 1, it can be specified because it is in the handler declaration.
    The statement label in line 3 is the same name as in line 1; it cannot be specified because it is not in the handler declaration.
  4. Specified SQL procedure statements are executed in the order in which they are specified.
  5. If an error occurs during the execution of an SQL procedure statement, the transaction is nullified only if the error is implicitly subject to a rollback. Any error occurring during execution of an SQL procedure statement for a trigger action is always implicitly subject to a rollback.
  6. If an error occurs during execution of an SQL procedure statement, the error is handled according to the following rules:
    • If an error without implicit rollback occurs:
      If there is a handler meeting the conditions, the exception processing of the handler is executed. If a condition-meeting handler does not exist, the execution of the SQL routine is terminated at that time, and an error is returned. Any subsequent SQL procedure statements are not executed.
    • If an error with an implicit rollback occurs:
      Even if there is a condition-meeting handler, exception processing is not executed. Execution of the SQL routine or the trigger at that time is terminated, and an error is returned. Any subsequent SQL procedure statements are not executed.
  7. The maximum number of nesting levels for compound statements and the FOR statement is 255.
  8. Any of the following names specified in SQL-variable-name, cursor-name, or condition-name must be enclosed in double quotation marks ("):
    • CONDITION
    • EXIT
    • HANDLER

Note

  1. Compound statements can be specified in an SQL routine or a trigger.

Examples

  1. Defines a procedure (PROC1) that applies a 30% discount on the unit price of a product for which the quantity indicated in the inventory table (STOCK) is 1, 000 or greater, deletes the row if the result of the discount is 0, and in other cases applies a 10% discount to the unit price:

    CREATE PROCEDURE PROC1(OUT OUTDATA INT)
    BEGIN
     DECLARE CR1 CURSOR FOR SELECT QUANTITY FROM STOCK ;
     OPEN CR1 ;
     WHILE SQLCODE=0 DO
       FETCH CR1 INTO OUTDATA ;
       IF SQLCODE=0 THEN
         IF OUTDATA|>=1000 THEN
           UPDATE STOCK SET PRICE = (1-0.3)*PRICE WHERE CURRENT OF CR1 ;
         ELSE IF OUTDATA=0 THEN
           DELETE FROM STOCK WHERE CURRENT OF CR1 ;
         ELSE
           UPDATE STOCK SET PRICE = (1-0.1)*PRICE WHERE CURRENT OF CR1 ;
         END IF ;
       END IF ;
     END;
    END

  2. Defines a procedure (PROC2) that updates the quantity of a specified product code in the inventory table (STOCK):
    • The specified quantity is less than or equal to 0 (the condition illegal_value defined in the condition is TRUE):
      The SIGNAL statement generates an error, and the exception processing sets a message in the output parameter. Execution of the SQL procedure statement terminates.
    • No data with a specified product code is found (NOT FOUND):
      The exception processing sets a message in the output parameter. Execution of the SQL procedure statement terminates.
    • Attempt to update a NOT NULL-constrained column with the NULL value (SQLCODE = -210):
      The exception processing sets a message in the output parameter. Execution of the SQL procedure statement continues.

    CREATE PROCEDURE PROC2(IN UPCODE CHAR(4), IN UQUANTITY INT,
                          OUT MSG MVARCHAR(255))
    BEGIN
     DECLARE PQUANTITY INT;
     DECLARE illegal_value CONDITION ;
     DECLARE EXIT HANDLER FOR illegal_value
       SET MSG=M'Invalid value as a quantity';
     DECLARE EXIT HANDLER FOR NOT FOUND
       SET MSG=M'Specified product code not found';
     DECLARE CONTINUE HANDLER FOR SQLCODE VALUE -210
       SET MSG=M'Attempt to update a NOT NULL-constrained column with NULL
                  Attempt ignored';
     SET MSG ='';
     IF UQUANTITY<0 THEN
       SIGNAL illegal_value;
     ELSE
       UPDATE STOCK SET SQUANTITY=UQUANTITY WHERE SPCODE=UPCODE;
       SET MSG=MSG||M'Processing complete'
       SELECT SQUANTITY INTO PQUANTITY FROM STOCK WHERE SPCODE=UPCODE;
       SET MSG=MSG||M'Current quantity:'||NUMEDIT(PQUANTITY, '<999999');
     END IF;
    END

  3. Defines an SQL procedure (PROC3) that registers new product data in the inventory table (STOCK).
    It is assumed that the product code column (PCODE) is the primary key. If the product code for the data to be inserted is a duplicate of the product code for previously registered data (SQLCODE = -803), the transaction is rolled back using the exception processing, and a message is set in the output parameter. Execution of the SQL procedure statement terminates.

    CREATE PROCEDURE PROC3(IN  UPCODE CHAR(4), IN  UPNAME NCHAR(8),
                          IN  UCOL   NCHAR(1), IN  UPRICE INT,
                          OUT MSG    MVARCHAR(255))
    BEGIN
     DECLARE EXIT HANDLER FOR SQLCODE VALUE -803
       BEGIN
         ROLLBACK;
         SET MSG=M'Rollback performed due to duplicate key violation';
       END;
     INSERT INTO STOCK VALUES(UPCODE, UPNAME, UCOL, UPRICE, 0);
     SET MSG=M'Registration complete.';
    END