Error Handling - Condition Handlers
(Page 3 of 4 )
A condition handler defines the actions that the stored program is to take when a specified event—such as a warning or an error—occurs.
Here is the syntax of the DECLARE HANDLER command:
DECLARE {CONTINUE | EXIT} HANDLER FOR
{SQLSTATE sqlstate_code| MySQL error code| condition_name}
handler_actions
Note that handlers must be defined after any variable or cursor declarations, which makes sense, since the handlers frequently access local variables or perform actions on cursors (such as closing them). They must also be declared before any executable statements. Chapter 4 includes more details on the rules governing the positioning of statements within a block.
The hander declaration has three main clauses;
- Handler type (CONTINUE,EXIT)
- Handler condition (SQLSTATE, MySQL error code, named condition)
- Hander actions
Let’s look at each of these clauses in turn.
Types of Handlers Condition handlers can be one of two types:
EXIT
When anEXIThandler fires, the currently executing block is terminated. If this block is the main block for the stored program, the procedure terminates, and control is returned to the procedure or external program that invoked the procedure. If the block is enclosed within an outer block inside of the same stored program, control is returned to that outer block.
CONTINUE
With aCONTINUEhandler, execution continues with the statement following the one that caused the error to occur.
In either case, any statements defined within the hander (the handler actions) are run before either theEXITorCONTINUEtakes place.
Let’s look at examples of both types of handlers. Example 6-6 shows a stored procedure that creates a department record and attempts to gracefully handle the situation in which the specified department already exists.
Example 6-6. Example of an EXIT handler
1CREATE PROCEDURE add_department
2 (in_dept_name VARCHAR(30),
3 in_location VARCHAR(30),
4 in_manager_id INT)
5 MODIFIES SQL DATA
6BEGIN
7 DECLARE duplicate_key INT DEFAULT 0;
8 BEGIN
9 DECLARE EXIT HANDLER FOR 1062 /* Duplicate key*/ SET duplicate_key=1;
10
11 INSERT INTO departments (department_name,location,manager_id)
12 VALUES(in_dept_name,in_location,in_manager_id);
13
14 SELECT CONCAT('Department ',in_dept_name,' created') as "Result";
15 END;
16
17 IF duplicate_key=1 THEN
18 SELECT CONCAT('Failed to insert ',in_dept_name,
19 ': duplicate key') as "Result";
20 END IF;
21END$$
Let’s examine the logic for Example 6-6:
Lines(s) | Explanation |
7 | Declare a status variable that will record the status of our insert attempt. |
8-15 | This BEGIN-ENDblock encloses the INSERTstatement that will attempt to create the department row. The block includes the EXIThandler that will terminate the block if a 1062 error occurs. |
11 | Attempt to insert our row—if we get a duplicate key error, the handler will set the variable and terminate the block. |
14 | This line executes only if the EXIThandler did not fire, and reports success to the user. If the handler fired, then the block was terminated and this line would never be executed. |
17 | Execution will then continue on this line, where we check the value of the variable and—if the hander has fired—advise the user that the insert was unsuccessful. |
Following is the output from this stored procedure for both unsuccessful and successful execution:
MySQL> CALL add_department('OPTIMIZER RESEARCH','SEATTLE',4) //
+----------------------------------------+
| Result |
+----------------------------------------+
| Failed to insert OPTIMIZER RESEARCH: duplicate key |
+----------------------------------------+
1 row in set (0.02 sec)
MySQL> CALL add_department('CUSTOMER SATISFACTION','DAVIS',4);
+----------------------------------------+
| Result |
+----------------------------------------+
| Department CUSTOMER SATISFACTION
created |
+----------------------------------------+
1 row in set (0.00 sec)
Example 6-7 provides an example of the same functionality implemented with aCONTINUEhandler. In this example, when the handler fires, execution continues with the statement immediately following theINSERTstatement. ThisIFstatement checks to see if the handler has fired, and if it has, it displays the failure message. Otherwise, the success message is displayed.
Example 6-7. Example of a CONTINUE handler
CREATE PROCEDURE add_department
(in_dept_name VARCHAR(30),
in_location VARCHAR(30),
in_manager_id INT)
MODIFIES SQL DATA
BEGIN
DECLARE duplicate_key INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR 1062 /* Duplicate key*/
SET duplicate_key=1;
INSERT INTO departments (department_name,location,manager_id)
VALUES(in_dept_name,in_location,in_manager_id);
IF duplicate_key=1 THEN
SELECT CONCAT('Failed to insert ',in_dept_name,
': duplicate key') as "Result";
ELSE
SELECT CONCAT('Department ',in_dept_name,' created') as "Result";
END IF;
END$$
EXIT or CONTINUE?
The choice between creating an EXIT handler and creating aCONTINUEhandler is based primarily on program flow-of-control considerations.
AnEXIThandler will exit from the block in which it is declared, which precludes the possibility that any other statements in the block (or the entire procedure) might be executed. This type of handler is most suitable for catastrophic errors that do not allow for any form of continued processing.
ACONTINUEhandler allows subsequent statements to be executed. Generally, you will detect that the handler has fired (through some form of status variable set in the handler) and determine the most appropriate course of action. This type of handler is most suitable when you have some alternative processing that you will execute if the exception occurs.
Next: Handler Conditions >>
More MySQL Articles
More By O'Reilly Media
|
 This article is excerpted from chapter six of the book MySQL Stored Procedure Programming, written by Guy Harrison and Steven Feuerstein (O'Reilly; ISBN: 0596100892). Check it out today at your favorite bookstore. Buy this book now.
|
|
