Home arrow MySQL arrow Page 2 - Error Handling

Handling Last Row Conditions - MySQL

In this first article in a three-part series, you will learn how to create various types of exception handlers. It is excerpted from chapter six of the book MySQL Stored Procedure Programming, written by Guy Harrison and Steven Feuerstein (O'Reilly; ISBN: 0596100892). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

TABLE OF CONTENTS:
  1. Error Handling
  2. Handling Last Row Conditions
  3. Condition Handlers
  4. Handler Conditions
By: O'Reilly Media
Rating: starstarstarstarstar / 14
August 30, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: