SQL Reference

ITERATE Statement

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

Syntax

>>-ITERATE--label----------------------------------------------><
 

Description

label

Specifies the label of the FOR, LOOP, REPEAT, or WHILE statement to which DB2 passes the flow of control.

Examples

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

     CREATE PROCEDURE ITERATOR()
        LANGUAGE SQL 
        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