Name
SQL-06: Always reset the NOT FOUND variable after completing a cursor loop
Synopsis
You should usually terminate a cursor loop when a CONTINUE
handler for the NOT FOUND
condition fires and modifies
the value of a status variable. For instance, in the following
fragment, the CONTINUE
handler
sets the v_last_row_fetched
variable to 1, and we test this value after each FETCH
call:
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_last_row_fetched=1;
OPEN cursor1;
cursor_loop:LOOP
FETCH cursor1 INTO v_customer_name, v_contact_surname, v_contact_firstname;
IF v_last_row_fetched=1 THEN
LEAVE cursor_loop;
END IF;
-- Do something with the row fetched.
END LOOP cursor_loop;
CLOSE cursor1;
SET v_last_row_fetched=0;
It is important to reset this status value to 0 after the cursor loop terminates; otherwise, subsequent or nested cursor loops may terminate prematurely.
The following code incorrectly fetches employees for only a single department, because after the first cursor loop, the status variable continues to indicate that the last row has been fetched:
DECLARE CONTINUE HANDLER FOR NOT FOUND SETv_not_found
=1; SET v_dept_id=1; WHILE( v_dept_id<=10) DO OPEN dept_emp_csr; emp_loop:LOOP FETCH dept_emp_csr INTO v_employee_id; IFv_not_found
THEN LEAVE emp_loop; END IF; CALL process_employee( v_employee_id); END LOOP; CLOSE dept_emp_csr; SET v_dept_id= v_dept_id+1; END WHILE;
Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.