DECLARE HANDLER

---

Declares an exception handler.

Usage

Procedural.

Description

An exception handler may be declared to respond to a specific exception condition or one or more of the exception class groups SQLEXCEPTION, SQLWARNING or NOT FOUND. An exception handler that responds to one or more exception class groups is called a general exception handler.

A specific exception condition may be specified by using an SQLSTATE value or a condition name, see DECLARE CONDITION. An exception handler that responds to one or more specific exception conditions is called a specific exception handler.

The keywords CONTINUE, EXIT and UNDO affect the flow of control behavior subsequent to the execution of the exception handler.

If CONTINUE is specified, the flow of control continues by executing the SQL statement immediately following the statement that raised the error, after the handler has executed.

If EXIT is specified, the flow of control exits the compound statement within which the exception handler is declared after the handler has executed.

If UNDO is specified, all the changes made by the SQL statements in the ATOMIC compound statement, within which the handler is declared, (or by any SQL statements triggered by those changes) are cancelled. Then the handler is executed and the flow of control exits the compound statement.

Restrictions

An UNDO exception handler can only be declared within an ATOMIC compound statement.

An exception handler must be either a general or a specific exception handler, it cannot respond to both an exception class group and a specific exception condition.

The same exception condition must not be specified more than once, whether by SQLSTATE value or by condition name, in an exception handler declaration.

Within a given scope, only one specific exception handler may be declared for a particular exception condition.

If string is specified, it must have length five and contain only alphanumeric characters.

Notes

Within a given compound statement, if both a general and a specific exception handler have been declared to respond to a given exception condition, the specific exception handler will handle the exception.

If no exception handlers have been declared to respond to an exception condition in the compound statement within which the error was raised, the exception condition is propagated out to the enclosing compound statement or to the calling environment.

The above does not apply to exception conditions with the NOT FOUND and SQLWARNING class, these are not propagated by the exception handling mechanism in absence of an exception handler defined to handle them.

SQLWARNING covers SQLSTATE values beginning with 01.

NOT FOUND covers SQLSTATE values beginning with 02.

SQLEXCEPTION covers all other SQLSTATE values (including implementation-defined values), except those beginning with 00.

Example

 S1:
 BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
       ...
       ...
    END;
    ...
    ...
  END S1;
 

For more information, see Return Status and Conditions or the Mimer SQL Programmer's Manual, Declaring Exception Handlers.

Standard Compliance

Standard	Compliance	Comments
SQL-2011	Features outside core	Feature P002, "Computational completeness".

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se