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;

1. Handler type ( CONTINUE , EXIT )
   
2. Handler condition ( SQLSTATE , MySQL error code, named condition) 
   
3. Hander actions

 Let’s look at each of these clauses in turn.

Condition handlers can be one of two types:

EXIT

When an EXIT handler 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 proce dure. If the block is enclosed within an outer block inside of the same stored program, control is returned to that outer block.

CONTINUE

With a CONTINUE handler, 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 the EXIT or CONTINUE takes 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

1 CREATE PROCEDURE add_departmen t
2       (in_dept_name VARCHAR(30),
3        in_location VARCHAR(30),
4        in_manager_id INT)
5     MODIFIES SQL DATA
6 BEGIN
7     DECLARE duplicate_key INT DEFAULT 0;
8     BEGIN
9          DECLARE EXIT HANDLER FOR 1062 /* Duplicate key*/ SET duplicate_key=1;
1 0
11         INSERT INTO departments (department_name,location,manager_id )
12         VALUES(in_dept_name,in_location,in_manager_id);
1 3
14         SELECT CONCAT('Department ',in_dept_name,' created') as "Result" ;
15     END;
1 6
17    IF duplicate_key=1 THE N
18         SELECT CONCAT('Failed to insert ',in_dept_name,
19           ': duplicate key') as "Result";
20    END IF;
21 END$$

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);

Example 6-7 provides an example of the same functionality implemented with a CONTINUE handler. In this example, when the handler fires, execution continues with the statement immediately following the INSERT statement. This IF statement checks to see if the handler has fired, and if it has, it displays the failure message. Other wise, 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 a CONTINUE handler is based primarily on program flow-of-control considerations.

An EXIT handler 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.

A CONTINUE handler allows subsequent statements to be executed. Generally, you will detect that the handler has fired (through some form of status variable set in the han dler) 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.

---