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