FOR statement (Repeat a statement on rows)

Function

Repeats the execution of a given SQL statement with respect to rows in a table.

Format

[starting-label:]
FOR loop-variable-name AS
 [cursor-name CURSOR [WITH HOLD]FOR]
   cursor-specification-Format-1
 <lock-option>
  [[{WITH {SHARE|EXCLUSIVE}LOCK
    | WITHOUT LOCK [{WAIT|NOWAIT}]}]
  [{WITH ROLLBACK|NO WAIT}]]
    [FOR {UPDATE [OF column-name [, column-name]...] [NOWAIT]|READ ONLY }]
    [UNTIL DISCONNECT]
 DO
 SQL procedure-statement;[SQL procedure-statement;]...
END FOR [end-label]

Operands

Rules on cursor-specification that is specified in a FOR statement
  • An unnamed column cannot be specified in a derived column. When specifying an unnamed column as a derived column, specify the AS clause, and assign an alias to the derived column.
  • A derived column with a duplicate derived column name cannot be specified.
  • [table-specification.] ROW cannot be specified in a derived column.
  • In a derived column, an unsubscripted repetition column without a flattening specification cannot be specified in the FROM clause.
If a derived column in the cursor specification format has been derived from any of the items listed below and AS column-name is omitted, that column becomes a nameless column. If the derived column is a scalar subquery, the derived column name depends on the derived column name of the selection expression of the scalar subquery.
  • Scalar operation (including the window function)
  • Function call
  • Set function
  • Literal
  • USER
  • CURRENT_DATE value function
  • CURRENT_TIME value function
  • CURRENT_TIMESTAMP value function
  • Component specification
  • GET_JAVA_STORED_ROUTINE_SOURCE specification
  • WRITE specification
  • SQL variable
  • SQL parameter
Rules on the SQL procedure statement specified in the FOR statement
  • An OPEN, FETCH, or CLOSE statement specifying the cursor in the FOR statement cannot be specified.
  • A LEAVE statement with a loop variable name specification cannot be specified.
  • If the cursor in the FOR statement is not a holdable cursor, the COMMIT, ROLLBACK, or PURGE TABLE statement cannot be specified. If a procedure specifying any of these statements is called by a CALL statement, a run-time error may occur.
  • The specified SQL procedure statements are executed in the order in which they are specified. If an error occurs during the execution of an SQL procedure statement, any subsequent SQL procedure statement is not executed. The execution of the FOR statement also terminates.

Common rules

  1. When specifying end-label, specify a statement label with the same name as the starting label.
  2. The scope of a statement label and that of a loop variable name is between the beginning and the end of the FOR statement. A statement label or a loop variable name that is identical to the statement label or loop variable name of other statements contained in the FOR statement cannot be specified. However, if the SQL procedure statement contains a handler declaration, such identical statement labels or loop variable names can still be specified in the handler declaration.
  3. The SQL variable that is implicitly declared in the FOR statement or the cursor that is explicitly declared is allocated first in the FOR statement and is released last.
  4. The scope of the SQL variable implicitly declared in the FOR statement, or the cursor that is explicitly declared, is within the FOR statement in which the SQL statement or the cursor is declared.
  5. If a compound statement is declared in an SQL procedure statement in a FOR statement, or if a FOR statement is declared in an SQL procedure statement in a compound statement, and if the SQL variable implicitly declared in the FOR statement and the SQL variable declared in the compound statement have the same name, in the inner routine control SQL, the SQL variable declared in the inner routine control SQL statement takes effect. When the inner routine control SQL statement terminates, the SQL variable declared in the outer routine control SQL statement takes effect. An example is shown below:

    [Figure]

  6. If a FOR statement is specified in an SQL procedure statement in a FOR statement, and if the SQL variable implicitly declared in the outer FOR statement and the SQL variable implicitly declared in the inner FOR statement have the same name, in the inner FOR statement, the SQL variable declared in the inner FOR statement takes effect. When the inner FOR statement terminates, the SQL variable declared in the outer FOR statement takes effect.
  7. If a compound statement is specified in an SQL procedure statement in a FOR statement, if a FOR statement is specified in an SQL statement in the compound statement, and if the cursor declared in the FOR statement and the cursor declared in the compound statement have the same name, the cursor declared in the inner routine control SQL statement takes effect. When the inner routine control SQL statement terminates, the cursor declared outside takes effect.
  8. If a FOR statement is specified in an SQL procedure statement in a FOR statement, and if the cursor declared in the outer FOR statement and the cursor declared in the inner FOR statement have the same name, the cursor declared in the inner FOR statement takes effect. When the inner FOR statement terminates, the cursor declared in the outer FOR statement takes effect.
  9. If an error occurs during the execution of an SQL procedure, the transaction is nullified only if the error is subject to an implicit rollback. If an error occurs during the execution of a trigger operation SQL procedure statement, the transaction is always implicitly rolled back.
  10. The maximum allowable number of nesting levels for FOR statements and compound statements is 255.

Notes

  1. A FOR statement can be specified in an SQL procedure or a trigger.

Examples

Of the data listed in table T1, assign the data with a column C1(INT type) less than or equal to 100 to Table T2, and for other data, define a procedure (PROC1) that assigns the data to Table T3:

CREATE PROCEDURE PROC1 ()
FLBL :
FOR LVN AS SELECT C1, C2, C3 FROM T1 DO
IF C1 <= 100 THEN
 INSERT INTO T2 VALUES(LVN.C1, LVN.C2, LVN.C3) ;
ELSE
 INSERT INTO T3 VALUES(LVN.C1, LVN.C2, LVN.C3) ;
END IF ;
END FOR FLBL

SQL statements equivalent to the FOR statements shown in the above example can be implemented in the following SQL statements:

CREATE PROCEDURE PROC1 ()
LVN :
BEGIN
 DECLARE C1, C2, C3 INT ;
 DECLARE FCN CURSOR FOR SELECT C1, C2, C3 FROM T1 ;
 DECLARE AT_END CHAR(1) DEFAULT 'N' ;
 OPEN FCN ;
 FLBL :
 WHILE AT_END != 'Y' DO
   FETCH FCN INTO C1, C2, C3 ;
   IF SQLCODE = 100 THEN
     SET AT_END = 'Y' ;
   ELSE
     IF C1 <= 100 THEN
       INSERT INTO T2 VALUES(LVN.C1, LVN.C2, LVN.C3) ;
     ELSE
       INSERT INTO T3 VALUES(LVN.C1, LVN.C2, LVN.C3) ;
     END IF ;
   END IF ;
 END WHILE FLBL ;
CLOSE FCN ;
END LVN