DB2 10.5 for Linux, UNIX, and Windows

ITERATE statement

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

Invocation

This statement can be embedded in an:

SQL procedure definition
Compound SQL (compiled) statement
Compound SQL (inlined) statement

The compound statements can be embedded in an SQL procedure definition, SQL function definition, or SQL trigger definition. It is not an executable statement and cannot be dynamically prepared.

Authorization

None required.

Syntax


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

Description

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

Example

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