FOR statement in SQL procedures

FOR statements are a special type of looping statement, because they are used to iterate over rows in a defined read-only result set.

When a FOR statement is executed a cursor is implicitly declared such that for each iteration of the FOR-loop the next row is the result set if fetched. Looping continues until there are no rows left in the result set.

The FOR statement simplifies the implementation of a cursor and makes it easy to retrieve a set of column values for a set of rows upon which logical operations can be performed.

  CREATE PROCEDURE P()
  LANGUAGE SQL
  BEGIN ATOMIC
    DECLARE fullname CHAR(40);
   
    FOR v AS cur1 CURSOR FOR 
                SELECT firstnme, midinit, lastname FROM employee
    DO 
      SET fullname = v.lastname || ',' || v.firstnme 
                     ||' ' || v.midinit;
      INSERT INTO tnames VALUES (fullname);
    END FOR;
  END

Note: Logic such as is shown in this example would be better implemented using the CONCAT function. The simple example serves to demonstrate the syntax.

The for-loop-name specifies a label for the implicit compound statement generated to implemented the FOR statement. It follows the rules for the label of a compound statement. The for-loop-name can be used to qualify the column names in the result set as returned by the select-statement.

The cursor-name simply names the cursor that is used to select the rows from the result set. If it is not specified, the Db2® database manager will automatically generate a unique cursor name internally.

The column names of the select statement must be unique and a FROM clause specifying a table (or multiple tables if doing some kind of JOIN or UNION) is required. The tables and columns referenced must exist prior to the loop being executed. Global temporary tables and declared temporary tables can be referenced.

Positioned updates and deletes, and searched updates and deletes are supported in the FOR loop. To ensure correct results, the FOR loop cursor specification must include a FOR UPDATE clause.

The cursor that is created in support of the FOR statement cannot be referenced outside of the FOR loop.