The LOOP statement is a special type of looping statement. Instead of the FOR, WHILE, and REPEAT constructs, there’s no condition clause for terminating the loop. To leave the loop, the LEAVE, ITERATE, GOTO, or RETURN statement must be used.
To use the LOOP construct as loop is not very popular, and it’s not a good programming style. You must be careful, because it’s very easy to create infinite loops.
CREATE PROCEDURE DEL_DEPT (IN deldept VARCHAR(3)) LANGUAGE SQL BEGIN ATOMIC DECLARE currentDEPT CHAR(3); DECLARE exitcode INTEGER DEFAULT 0; DECLARE mycur CURSOR FOR select DEPTNO as v_dept from DEPARTMENT where deptno = deldept; DECLARE CONTINUE HANDLER FOR NOT FOUND SET exitcode = 1; OPEN mycur; loop_over_employee: LOOP FETCH FROM mycur INTO currentDEPT; IF exitcode = 1 THEN LEAVE loop_over_employee; END IF; delete from EMPLOYEE where workdept = currentDEPT; END LOOP loop_over_employee; CLOSE mycur; delete from DEPARTMENT where deptno = deldept; END!