Executes an SQL statement for each row of a query expression.

1  –  Environment

    You can use the FOR control statement in a compound statement of
    a multistatement procedure:

    o  In interactive SQL

    o  Embedded in host language programs to be precompiled

    o  As part of a procedure in an SQL module

    o  In dynamic SQL as a statement to be dynamically executed

2  –  Format

  for-statement =

  -+------------------------+-> FOR <variable-name> --------------+
   +--> <beginning-label> : +                                     |
   +---------------------------------<----------------------------+
   +--> AS -+----------------+-+--------------------------------+-+
            +-> EACH ROW OF -+ +-> for-statement-table-cursor --+ |
   +----------------------------------<---------------------------+
   +--> select-expression --> DO --+-> compound-use-statement --+-+
                                   +--------------<-------------+ |
   +-----------------------------------<--------------------------+
   +--> END FOR -+------------------+->
                 +-> <ending-label> +

  for-statement-table-cursor =

  ---+-----------------+--+-----------+-> CURSOR <cursor-name> FOR -->
     +-> READ ONLY ----+  +-> TABLE --+
     +-> UPDATE ONLY --+

3  –  Arguments

3.1  –  AS EACH ROW OF cursor

    Creates a result table with a specified cursor.

    The optional naming of a cursor lets you use positioned data
    manipulation language statements in the DO clause of a FOR loop.

3.2  –  AS EACH ROW OF select expr

    Creates a simple result table.

    After SQL creates the result table from the select expression,
    the DO clause executes a set of SQL statements (compound-use-
    statement) for each result table row.

3.3  –  beginning-label:

    Assigns a name to the FOR statement.

    A named FOR loop is called a labeled FOR loop statement. If
    you include an ending label, it must be identical to its
    corresponding beginning label. A beginning label must be unique
    within the procedure in which the label is contained.

3.4  –  DO

    Executes a block of SQL statements for each row of the select
    expression result table.

3.5  –  END_FOR

    Marks the end of a FOR loop. If you choose to include the
    optional ending label, it must match exactly its corresponding
    beginning label. An ending label must be unique within the
    procedure in which the label is contained.

    The optional end-label argument makes the FOR loops of
    multistatement procedures easier to read, especially in very
    complex procedure blocks.

3.6  –  FOR variable name

    Specifies a name for a record consisting of a field for each
    named column of the FOR loop select expression. Each field in the
    record contains the data represented by each column name in each
    row of the select expression result table.

    The variable name lets you reference a field in the compound-use-
    statement argument, for example: variable-name.column-name.

4  –  Examples

    Example 1: Using the FOR statement within an SQL module procedure

    SQL> set flags 'trace';
    SQL>
    SQL> create module REPORTS
    cont> /*
    ***> This procedure counts the employees of a given state
    ***> who have had a decrease in their salary during their
    ***> employment
    ***> */
    cont> procedure COUNT_DECREASED
    cont>     (in :state CHAR(2)
    cont>     ,inout :n_decreased INTEGER);
    cont> begin
    cont> set :n_decreased = 0;
    cont>
    cont> EMP_LOOP:
    cont> for :empfor
    cont>     as each row of
    cont>         select employee_id
    cont>          from EMPLOYEES where state = :state
    cont> do
    cont>     begin
    cont>     declare :last_salary INTEGER (2) default 0;
    cont>
    cont>     HISTORY_LOOP:
    cont>     for :salfor
    cont>         as each row of
    cont>             select salary_amount
    cont>              from SALARY_HISTORY
    cont>              where employee_id = :empfor.employee_id
    cont>              order by salary_start
    cont>     do
    cont>         if :salfor.salary_amount < :last_salary
    cont>         then
    cont>             set :n_decreased = :n_decreased + 1;
    cont>             trace :empfor.employee_id, ': ', :salfor.salary_amount;
    cont>             leave HISTORY_LOOP;
    cont>         end if;
    cont>
    cont>         set :last_salary = :salfor.salary_amount;
    cont>     end for;
    cont>     end;
    cont> end for;
    cont> end;
    cont>
    cont> end module;
    SQL>
    SQL> declare :n integer;
    SQL> call COUNT_DECREASED ('NH', :n);
    ~Xt: 00200: 40789.00
    ~Xt: 00248: 46000.00
    ~Xt: 00471: 52000.00
               N
               3
    SQL>
    SQL> rollback;