The LEAVE statement is used to transfer the flow of control out of a loop or compound statement.
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
In the example, the LEAVE statement is used to exit the LOOP statement defined with label ins_loop. It is nested within an IF statement and therefore is conditionally executed when the IF-condition is true which becomes true when there are no more rows found in the cursor. The position of the LEAVE statement ensures that no further iterations of the loop are executed once a NOT FOUND error is raised.