LOOP statement in SQL procedures
The LOOP statement is a special type of looping statement, because it has no terminating condition clause.
It defines a series of statements that are executed repeatedly until another piece of logic, generally a transfer of control statement, forces the flow of control to jump to some point outside of the loop.
The LOOP statement is generally used in conjunction with one of the following statements: LEAVE, GOTO, ITERATE, or RETURN. These statements can force control to just after the loop, to a specified location in the SQL procedure, to the start of the loop to begin another iteration of the loop, or to exit the SQL procedure. To indicate where to pass flow to when using these statements, labels are used.
The LOOP statement is useful when you have complicated logic in a loop which you might need to exit in more than one way, however it should be used with care to avoid instances of infinite loops.
If the LOOP statement is used alone without a transfer of control statement, the series of statements included in the loop will be executed indefinitely or until a database condition occurs that raises a condition handler that forces a change in the control flow or a condition occurs that is not handled that forces the return of the SQL procedure.
CREATE PROCEDURE ITERATOR()
LANGUAGE SQL
BEGIN
DECLARE v_deptno CHAR(3); DECLARE v_deptname VARCHAR(29);
DECLARE at_end INTEGER DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE c1 CURSOR FOR SELECT deptno, deptname
FROM department ORDER BY deptno;
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
OPEN c1;
ins_loop: LOOP
FETCH c1 INTO v_deptno, v_deptname;
IF at_end = 1 THEN
LEAVE ins_loop;
ELSEIF v_dept = 'D11' THEN
ITERATE ins_loop;
END IF;
INSERT INTO department (deptno, deptname)
VALUES ('NEW', v_deptname);
END LOOP;
CLOSE c1;
END