DB2 Version 9.7 for Linux, UNIX, and Windows

ITERATE statement in SQL procedures

The ITERATE statement is used to cause the flow of control to return to the beginning of a labeled LOOP statement.

Here is an example of an SQL procedure that contains an ITERATE statement:

  CREATE  PROCEDURE ITERATOR()
  LANGUAGE SQL
  BEGIN
    DECLARE v_deptno CHAR(3); DECLARE v_deptname VARCHAR(29); 
    DECLARE at_end INTEGER DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';

    DECLARE c1 CURSOR FOR SELECT deptno, deptname  
                          FROM department ORDER BY deptno;
    DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
    OPEN c1;
  
    ins_loop: LOOP
  	  FETCH c1 INTO v_deptno, v_deptname; 
        IF at_end = 1 THEN
          LEAVE ins_loop;
        ELSEIF v_dept = 'D11' THEN
          ITERATE ins_loop;
        END  IF;

        INSERT  INTO department (deptno, deptname) 
        VALUES ('NEW', v_deptname);

    END LOOP;

    CLOSE c1;

  END

In the example, the ITERATE statement is used to return the flow of control to the LOOP statement defined with label ins_loop when a column value in a fetched row matches a certain value. The position of the ITERATE statement ensures that no values are inserted into the department table.