The WHILE loop construct is a mechanism for continuously looping through a section of logic. The condition will be evaluated before the WHILE loop is entered. The loop is entered only if the condition evaluates to TRUE. The end of the looped section must be specified with the END WHILE statement. Before the next iteration starts, the condition is tested again. Loop processing continues as long as the condition evaluates to TRUE at the beginning of each iteration through the loop.

Example:

CREATE PROCEDURE DEL_DEPT (IN deldept VARCHAR(3))
 LANGUAGE SQL
 BEGIN ATOMIC
   DECLARE currentDEPT CHAR(3);
   DECLARE SQLCODE INTEGER DEFAULT 0;
   DECLARE mycur CURSOR FOR
       select DEPTNO as v_dept
       from DEPARTMENT
       where deptno = deldept;

   OPEN mycur;
   FETCH FROM mycur INTO currentDEPT;
   WHILE (SQLCODE = 0)
   DO
       delete from EMPLOYEE where workdept = currentDEPT;
       fetch from mycur into currentDEPT;
   END WHILE;
   CLOSE mycur;
   delete from DEPARTMENT where deptno = deldept;
 END!

Link to the DB2 Information Center: DB2 9.7 DB2 10.1 DB2 10.5