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.
