ITERATE statement

The ITERATE statement causes the flow of control to return to the beginning of a labeled loop.

Syntax

Description

label

Specifies the label for the ITERATE statement. The label name cannot be the same as the routine name, advanced trigger name, or another label within the same scope. For additional information, see References to SQL labels in SQL PL. End of change

target-label

Specifies the label of the FOR, LOOP, REPEAT, or WHILE statement to which the flow of control is passed. target-label must be defined as a label for a FOR, LOOP, REPEAT, or WHILE statement. The ITERATE statement must be in that FOR, LOOP, REPEAT, or WHILE statement, or in the block of code that is directly or indirectly nested within that statement, subject to the following restrictions:

If the ITERATE statement is in a condition handler, target-label must be defined in that condition handler.
If the ITERATE statement is not in a condition handler, target-label must not be defined in a condition handler.

Examples

Example 1: This example uses a cursor to return information for a new department. If the not_found condition handler is invoked, the flow of control passes out of the loop. If the value of v_dept is 'D11', an ITERATE statement causes the flow of control to be passed back to the top of the LOOP statement. Otherwise, a new row is inserted into the table.

CREATE PROCEDURE ITERATOR ()
  LANGUAGE SQL
  MODIFIES SQL DATA
  BEGIN
    DECLARE v_dept CHAR(3);
    DECLARE v_deptname VARCHAR(29);
    DECLARE v_admdept CHAR(3);
    DECLARE at_end INTEGER DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE c1 CURSOR FOR
      SELECT deptno,deptname,admrdept
        FROM department
        ORDER BY deptno;
    DECLARE CONTINUE HANDLER FOR not_found
      SET at_end = 1;
    OPEN c1;
    ins_loop:
    LOOP
      FETCH c1 INTO v_dept, v_deptname, v_admdept;
      IF at_end = 1 THEN
        LEAVE ins_loop;
      ELSEIF v_dept = 'D11' THEN
        ITERATE ins_loop;
      END IF;
      INSERT INTO department (deptno,deptname,admrdept)
                       VALUES('NEW', v_deptname, v_admdept);
    END LOOP;
    CLOSE c1;
  END

Example 2: An ITERATE statement can be issued from a nested block to cause that flow of control to return to the beginning of a loop at a higher level. In the following example, the ITERATE statement within the LAB2 compound statement causes the flow of control to return to the beginning of the LAB1 LOOP statement:

LAB1: LOOP
   SET A = 0;
   LAB2: BEGIN
     ...
     LAB3: BEGIN
        ...
        ITERATE LAB1;  --	Multilevel ITERATE
        ...
     END LAB3;
     ...
     ITERATE LAB1;   --  Multilevel ITERATE
     ...
   END LAB2;
END LOOP;S