MySQL Stored Procedure Programming

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
          SET  v_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;
          IF  v_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;