MySQL Stored Procedure Programming

Condition Handlers

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