Handling Last Row Conditions - MySQL

One of the most common operations in a MySQL stored program involves fetching one or more rows of data. You can do this in a stored program through the use of a cursor (explained in Chapter 5). However, MySQL (and the ANSI standard) considers an attempt to fetch past the last row of the cursor an error. Therefore, you almost always need to catch that particular error when looping through the results from a cursor.

Consider the simple cursor loop shown in Example 6-4. At first glance, you might worry that we might inadvertently have created an infinite loop, since we have not coded any way to leave thedept_looploop.

Example 6-4. Cursor loop without a NOT FOUND handler

CREATE PROCEDURE sp_fetch_forever()
  READS SQL DATA
BEGIN
    DECLARE l_dept_id INT;
    DECLARE c_dept CURSOR FOR
            SELECT department_id
              FROM departments;

    OPEN c_dept;
    dept_cursor: LOOP
        FETCH c_dept INTO l_dept_id;
    END LOOP dept_cursor;
    CLOSE c_dept;
END

Bravely, we run this program and find that the seemingly infinite loop fails as soon as we attempt to fetch beyond the final row in the result set:

  mysql> CALL sp_fetch_forever();
  ERROR 1329 (02000): No data to FETCH

Since we likely want to do something with the data after we’ve fetched it, we cannot let this exception propagate out of the procedure unhandled. So we will add a declaration for aCONTINUE HANDLERin the procedure, setting a flag to indicate that the last row has been fetched. This technique is shown in Example 6-5.

Example 6-5. Cursor loop with a NOT FOUND handler

1CREATE PROCEDURE sp_not_found()
2    READS SQL DATA
3BEGIN
4    DECLARE l_last_row INT DEFAULT 0;
5    DECLARE l_dept_id INT;
6    DECLARE c_dept CURSOR FOR
7          SELECT department_id
8            FROM departments;
9   /* handler to set l_last_row=1 if a cursor returns no more rows */
10   DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row=1;
11
12  OPEN c_dept;
13  dept_cursor: LOOP
14       FETCH c_dept INTO l_dept_id;
15        IF (l_last_row=1) THEN
16            LEAVE dept_cursor;
17        END IF;
18        /* Do something with the data*/
19
20  END LOOP dept_cursor;
21  CLOSE c_dept;
22
23END;

In plain English, the handler on line 10 says “When a fetch from a cursor returns no more rows, continue execution, but set the variablel_last_rowto 1.” After retrieving each row, we check thel_last_rowvariable and exit from the cursor loop if the last row is returned. Without this handler, our cursor loop will fetch too many times and raise an exception.

Now that you have seen two simple examples of declaring handlers for error situations that you can anticipate, let’s explore this functionality in more detail.