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

2  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

 (B)0for-statement =                                                   
                                                                   
 qwqqqqqqqqqqqqqqqqqqqqqqqqwq> FOR <variable-name> qqqqqqqqqqqqqqk 
  mqq> <beginning-label> : j                                     x 
  lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqj 
  mqq> AS qwqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk 
           mq> EACH ROW OF qj mq> for-statement-table-cursor qqj x 
  lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqj 
  mqq> select-expression qq> DO qqwq> compound-use-statement qqwqk 
                                  mqqqqqqqqqqqqqq<qqqqqqqqqqqqqj x 
  lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqj 
  mqq> END FOR qwqqqqqqqqqqqqqqqqqqwq>                             
                mq> <ending-label> j                               



 (B)0for-statement-table-cursor =                                         
                                                                      
 qqqwqqqqqqqqqqqqqqqqqwqqwqqqqqqqqqqqwq> CURSOR <cursor-name> FOR qq> 
    tq> READ ONLY qqqqu  mq> TABLE qqj                                
    mq> UPDATE ONLY qqj                                               
                                                                      

 

2  Arguments
 

3  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  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  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  DO

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

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

2  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;