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;