Skip to Main Content
MySQL Stored Procedure Programming
book

MySQL Stored Procedure Programming

by Guy Harrison, Steven Feuerstein
March 2006
Intermediate to advanced content levelIntermediate to advanced
640 pages
17h 8m
English
O'Reilly Media, Inc.
Content preview from 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;
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

Jesper Wisborg Krogh
MySQL 8 Administrator???s Guide

MySQL 8 Administrator???s Guide

Chintan Mehta, Hetal Oza, Subhash Shah, Ravi Shah
MySQL Cookbook, 4th Edition

MySQL Cookbook, 4th Edition

Sveta Smirnova, Alkin Tezuysal
Learning MySQL, 2nd Edition

Learning MySQL, 2nd Edition

Vinicius M. Grippa, Sergey Kuzmichev

Publisher Resources

ISBN: 0596100892Supplemental ContentErrata Page