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
       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