Control Statements › ITERATE

ITERATE

The ITERATE statement terminates execution of the current iteration of an iterated statement, such as LOOP, REPEAT or WHILE. If the iteration condition is true, a new iteration starts; otherwise, the statement following the iterated statement is executed.

Syntax

►►── ITERATE ── stmnt-label ──────────────────────────────────────────────────►◄

Parameters

stmnt-label

Specifies the begin label of the iterated statement.

Usage

Statements that may be iterated

The labeled statement referred in the ITERATE must be a LOOP, REPEAT, or WHILE statement that contains the ITERATE statement.

Example

The procedure USER01.TITERATE1 retrieves all rows of the DEMOEMPL.EMPLOYEE table three times. The first loop uses a WHILE, the second uses a REPEAT, and the third uses a LOOP statement.

set options command delimiter '++';
create procedure USER01.TITERATE1
  ( TITLE     varchar(10) with default
  , P_FNAME   char(20)
  , P_COUNT     integer
  , RESULT    varchar(10)
  )
    EXTERNAL NAME TITERATE LANGUAGE SQL

Label_600:
begin not atomic
  declare FNAME   char(20);
  declare LNAME   varchar(20);
  declare EMP1 CURSOR FOR
        Select EMP_FNAME, EMP_LNAME
          From DEMOEMPL.EMPLOYEE;
 /*
 ITERATE in WHILE
 */
  set RESULT = '?????';
  open EMP1;

  while_loop:
  while (9 = 9)
     do
      fetch EMP1 into FNAME, LNAME;
      if (SQLSTATE = '00000')
        then
           set P_COUNT = P_COUNT + 1;
           iterate while_loop;
      end if;

      if (SQLSTATE = 'abcde')
        then
           iterate while_loop;
      end if;

      set RESULT = SQLSTATE;
      leave while_loop;
  end while while_loop;

  close EMP1;

 /*
 ITERATE in REPEAT
 */
  set RESULT = '?????';
  open EMP1;

  repeat_loop:
  repeat
      fetch EMP1 into FNAME, LNAME;
      if (SQLSTATE = '00000')
        then
           set P_COUNT = P_COUNT + 1;
           iterate repeat_loop;
      end if;

      set RESULT = SQLSTATE;
      leave repeat_loop;
  until (9 = 0)
  end repeat repeat_loop;

  close EMP1;
 /*
 ITERATE in LOOP
 */
  set RESULT = '?????';
  open EMP1;

  loop_loop:
  loop
      fetch EMP1 into FNAME, LNAME;
      if (SQLSTATE = '00000')
        then
           set P_COUNT = P_COUNT + 1;
           iterate loop_loop;
      end if;

      set RESULT = SQLSTATE;
      leave loop_loop;
  end loop loop_loop;

  close EMP1;
end
++
commit++

call USER01.TITERATE1('TITERATE1','James  ',0,'U')++
*+
*+ TITLE       P_FNAME                   P_COUNT  RESULT
*+ -----       -------                   -------  ------
*+ TITERATE1   James                         165  02000
set options command delimiter default++