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
(B)0[m[1mfor-statement = [m [1m [m [1mqwqqqqqqqqqqqqqqqqqqqqqqqqwq> [1;4mFOR[m[1m <variable-name> qqqqqqqqqqqqqqk [m [1m mqq> <beginning-label> : j x [m [1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m [1m mqq> [1;4mAS[m[1m qwqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk [m [1m mq> [1;4mEACH[m[1m [1;4mROW[m[1m [1;4mOF[m[1m qj mq> for-statement-table-cursor qqj x [m [1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqj [m [1m mqq> select-expression qq> [1;4mDO[m[1m qqwq> compound-use-statement qqwqk [m [1m mqqqqqqqqqqqqqq<qqqqqqqqqqqqqj x [m [1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqj [m [1m mqq> [1;4mEND[m[1m [1;4mFOR[m[1m qwqqqqqqqqqqqqqqqqqqwq> [m [1m mq> <ending-label> j [m (B)0[m[1mfor-statement-table-cursor = [m [1m [m [1mqqqwqqqqqqqqqqqqqqqqqwqqwqqqqqqqqqqqwq> [1;4mCURSOR[m[1m <cursor-name> [1;4mFOR[m[1m qq> [m [1m tq> [1;4mREAD[m[1m [1;4mONLY[m[1m qqqqu mq> [1;4mTABLE[m[1m qqj [m [1m mq> [1;4mUPDATE[m[1m [1;4mONLY[m[1m qqj [m [1m [m
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;