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

[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]...]|READ ONLY }] [UNTIL DISCONNECT] DO SQL procedure-statement;[SQL procedure-statement;]... END FOR [end-label]

• [starting-label]
  Specifies the statement label for the FOR statement.
  
• loop-variable-name
  Specifies the qualifier for the SQL variable that is implicitly declared.
  During the execution of the FOR statement, the SQL variable that takes a derived column name with a cursor specification is implicitly declared. The implicitly declared SQL variable can be qualified with a loop variable name.
  Loop variable names are subject to the rules on label names. Consequently, restrictions on label names are also applicable to loop variable names.
  
• cursor-name
  Specifies the name of a cursor.
  If cursor-name is omitted, HiRDB generates a specific cursor name. For details about cursor names, see 1.1.7 Specification of names.
  
• [WITH HOLD]
  Specify this option when using a holdable cursor.
  Because WITH HOLD is functionally the same as specifying UNTIL DISCONNECT, for an explanation of this statement, see UNTIL DISCONNECT. Also, there is no functional difference due to whether or not this specification is duplicated with UNTIL DISCONNECT.
  For restrictions on the holdable cursor, see DECLARE CURSOR Format 1.
  
• cursor-specification Format 1
  Specifies the cursor that represents the contents of a query.
  For cursor specification, see 2.1.1 Cursor specification: Format 1.
  

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

• lock-option
  Specifies the lock mode when a query is made, and the action to be taken when another user has exclusive use of resources.
  For details about the lock option, see 2.19 Lock option.
  
• [FOR{UPDATE [OF column-name [,column-name ]...]|READ ONLY}]
  FOR UPDATE [OF column-name [,column-name ]...] is referred to as the FOR UPDATE clause.
  FOR UPDATE [OF column-name [,column-name ]...] is referred to as the FOR UPDATE clause.
  For details about the FOR UPDATE clause and FOR READ ONLY, see DECLARE CURSOR Format 1 (Declare cursor) in Chapter 4.
  
• [UNTIL DISCONNECT]
  Specify this option when using a holdable cursor.
  This specification is functionally identical to the WITH HOLD specification. Also, there is no functional difference due to whether or not this specification is duplicated with WITH HOLD.
  For details on restrictions on the holdable cursor, see DECLARE CURSOR Format 1.
  
• SQL procedure-statement
  Specifies the SQL procedure statement that is executed repeatedly.
  

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.

• END FOR [end-label]
  Specifies the termination of the FOR statement. In end-label, a statement label should be specified.
  

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:
   

   

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.

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

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 != 'N' 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