Condition Handlers

A condition handler defines the actions that the stored program is to take when a specified eventsuch as a warning or an erroroccurs.

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.

6.2.1. Types of Handlers

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

Let's examine the logic for Example 6-6:

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 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. Otherwise, the success message is displayed.

Example 6-7. Example of a CONTINUE handler

6.2.2. Handler Conditions

The handler condition defines the circumstances under which the handler will be invoked. The circumstance is always associated with an error condition, but you have three choices as to how you define that error:

• As a MySQL error code.
• As an ANSI-standard SQLSTATE code.
• As a named condition. You may define your own named conditions (described in the later section "Named Conditions") or use one of the built-in conditions SQLEXCEPTION, SQLWARNING, and NOT FOUND.

MySQL has its own set of error codes that are unique to the MySQL server. A handler condition that refers to a numeric code without qualification is referring to a MySQL error code. For instance, the following handler will fire when MySQL error code 1062 (duplicate key value) is encountered:

DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key=1;

SQLSTATE error codes are defined by the ANSI standard and are database-independent, meaning that they will have the same value regardless of the underlying database. So, for instance, Oracle, SQL Server, DB2, and MySQL will always report the same SQLSTATE value (23000) when a duplicate key value error is encountered. Every MySQL error code has an associated SQLSTATE code, but the relationship is not one-to-one; some SQLSTATE codes are associated with many MySQL codes; HY000 is a general-purpose SQLSTATE code that is raised for MySQL codes that have no specific associated SQLSTATE code.

The following handler will fire when SQLSTATE 23000 (duplicate key value) is encountered:

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET duplicate_key=1;

When the MySQL client encounters an error, it will report both the MySQL error code and the associated SQLSTATE code, as in the following output:

mysql> CALL nosuch_sp( ); ERROR 1305 (42000): PROCEDURE sqltune.nosuch_sp does not exist

In this case, the MySQL error code is 1305 and the SQLSTATE code is 42000.

Table 6-1 lists some of the error codes you might expect to encounter in a MySQL stored program together with their SQLSTATE equivalents. Note, again, that many MySQL error codes map to the same SQLSTATE code (many map to HY000, for instance), which is why you may wish to sacrifice portability and use MySQL error codes rather than SQLSTATE codesin your error handlers.

You can find a complete and up-to-date list of error codes in Appendix B of the MySQL reference manual, available online at http://dev.mysql.com/doc/.

6.2.3. Handler Examples

Here are some examples of handler declarations:

• If any error condition arises (other than a NOT FOUND), continue execution after setting l_error=1:

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET l_error=1;

• If any error condition arises (other than a NOT FOUND), exit the current block or stored program after issuing a ROLLBACK statement and issuing an error message:

  DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 'Error occurred - terminating'; END;

• If MySQL error 1062 (duplicate key value) is encountered, continue execution after executing the SELECT statement (which generates a message for the calling program):

  DECLARE CONTINUE HANDER FOR 1062 SELECT 'Duplicate key in index';

• If SQLSTATE 23000 (duplicate key value) is encountered, continue execution after executing the SELECT statement (which generates a message for the calling program):

  DECLARE CONTINUE HANDER FOR SQLSTATE '23000' SELECT 'Duplicate key in index';

• When a cursor fetch or SQL retrieves no values, continue execution after setting l_done=1:

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;

• Same as the previous example, except specified using a SQLSTATE variable rather than a named condition:

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET l_done=1;

• Same as the previous two examples, except specified using a MySQL error code variable rather than a named condition or SQLSTATE variable:

  DECLARE CONTINUE HANDLER FOR 1329 SET l_done=1;

6.2.4. Handler Precedence

As we've described, MySQL lets you define handler conditions in terms of a MySQL error code, a SQLSTATE error, or a named condition such as SQLEXCEPTION. It is possible, therefore, that you could define several handlers in a stored program that would all be eligible to fire when a specific error occurred. Yet only one handler can fire in response to an error, and MySQL has clearly defined rules that determine the precedence of handlers in such a situation.

To understand the problem, consider the code fragment in Example 6-8. We have declared three different handlers, each of which would be eligible to execute if a duplicate key value error occurs. Which handler will execute? The answer is that the most specific handler will execute.

Example 6-8. Overlapping condition handlers

Handlers based on MySQL error codes are the most specific type of handler, since an error condition will always correspond to a single MySQL error code. SQLSTATE codes can sometimes map to many MySQL error codes, so they are less specific. General conditions such as SQLEXCEPTION and SQLWARNING are not at all specific. Therefore, a MySQL error code takes precedence over a SQLSTATE exception, which, in turn, takes precedence over a SQLEXCEPTION condition.

This strictly defined precedence allows us to define a general-purpose handler for unexpected conditions, while creating a specific handler for those circumstances that we can easily anticipate. So, for instance, in Example 6-9, the first handler will be invoked if something catastrophic happens (perhaps a jealous colleague drops your database tables), while the second will fire in the more likely event that someone tries to create a duplicate row within your database.

Example 6-9. Example of overlapping condition handling

Note, however, that we generally don't advise creating SQLEXCEPTION handlers until MySQL implements the SIGNAL statement; see "Missing SQL:2003 Features" later in this chapter.

6.2.5. Scope of Condition Handlers

The scope of a handler determines which statements within the stored program are covered by the handler. In essence, the scope of a handler is the same as for a stored program variable: the handler applies to all statements in the block in which it is defined, including any statements in nested blocks. Furthermore, handlers in a stored program also cover statements that execute in any stored program that might be called by the first program, unless that program declares its own handler.

For instance, in Example 6-10 the handler will be invoked when the INSERT statement executes (because it violates a NOT NULL constraint). The handler fires because the INSERT statement is contained within the same block as the handlereven though the INSERT statement is in a nested block.

Example 6-10. Handler scope includes statements within BEGIN-END blocks

However, in Example 6-11 the handler will not be invokedthe scope of the handler is limited to the nested block, and the INSERT statement occurs outside that block.

Example 6-11. Handlers within a nested block do not cover statements in enclosing blocks

Handler scope extends to any stored procedures or functions that are invoked within the handler scope. This means that if one stored program calls another, a handler in the calling program can trap errors that occur in the program that has been called. So, for instance, in Example 6-12, the handler in calling_procedure( ) TRaps the null value exception that occurs in sub_procedure ( ).

Example 6-12. A handler can catch conditions raised in called procedures

Of course, a handler in a procedure will override the scope of a hander that exists in a calling procedure. Only one handler can ever be activated in response to a specific error condition.