Syntax
------

DECLARE handler_type HANDLER
  FOR condition_value [, condition_value] ...
  statement

handler_type:
  CONTINUE
 | EXIT
 | UNDO

condition_value:
  SQLSTATE [VALUE] sqlstate_value
 | condition_name
 | SQLWARNING
 | NOT FOUND
 | SQLEXCEPTION
 | mariadb_error_code

Description
-----------

The DECLARE ... HANDLER statement specifies handlers that each may deal with
one or more conditions. If one of these conditions occurs, the specified
statement is executed. statement can be a simple statement (for example, SET
var_name = value), or it can be a compound statement written using BEGIN and
END.

Handlers must be declared after local variables, a CONDITION and a CURSOR.

For a CONTINUE handler, execution of the current program continues after
execution of the handler statement. For an EXIT handler, execution terminates
for the BEGIN ... END compound statement in which the handler is declared.
(This is true even if the condition occurs in an inner block.) The UNDO
handler type statement is not supported.

If a condition occurs for which no handler has been declared, the default
action is EXIT.

A condition_value for DECLARE ... HANDLER can be any of the following values:

* An SQLSTATE value (a 5-character string literal) or a MariaDB error
code (a number). You should not use SQLSTATE value '00000' or MariaDB
error code 0, because those indicate sucess rather than an error
condition. For a list of SQLSTATE values and MariaDB error codes, see
MariaDB Error Codes.
* A condition name previously specified with DECLARE ... CONDITION. It must be
in the same stored program. See DECLARE CONDITION.
* SQLWARNING is shorthand for the class of SQLSTATE values that begin
with '01'.
* NOT FOUND is shorthand for the class of SQLSTATE values that begin
with '02'. This is relevant only the context of cursors and is used to
control what happens when a cursor reaches the end of a data set. If
no more rows are available, a No Data condition occurs with SQLSTATE
value 02000. To detect this condition, you can set up a handler for it
(or for a NOT FOUND condition). An example is shown in Cursor Overview. This
condition also occurs for SELECT ... INTO var_list statements that retrieve no
rows.
* SQLEXCEPTION is shorthand for the class of SQLSTATE values that do
not begin with '00', '01', or '02'.

When an error raises, in some cases it could be handled by multiple HANDLERs.
For example, there may be an handler for 1050 error, a separate handler for
the 42S01 SQLSTATE, and another separate handler for the SQLEXCEPTION class:
in theory all occurrences of HANDLER may catch the 1050 error, but MariaDB
chooses the HANDLER with the highest precedence. Here are the precedence rules:

* Handlers which refer to an error code have the highest precedence.
* Handlers which refer to a SQLSTATE come next.
* Handlers which refer to an error class have the lowest precedence.

In some cases, a statement could produce multiple errors. If this happens, in
some cases multiple handlers could have the highest precedence. In such cases,
the choice of the handler is indeterminate.

Note that if an error occurs within a CONTINUE HANDLER block, it can be
handled by another HANDLER. However, a HANDLER which is already in the stack
(that is, it has been called to handle an error and its execution didn't
finish yet) cannot handle new errors—this prevents endless loops. For example,
suppose that a stored procedure contains a CONTINUE HANDLER for SQLWARNING and
another CONTINUE HANDLER for NOT FOUND. At some point, a NOT FOUND error
occurs, and the execution enters the NOT FOUND HANDLER. But within that
handler, a warning occurs, and the execution enters the SQLWARNING HANDLER. If
another NOT FOUND error occurs, it cannot be handled again by the NOT FOUND
HANDLER, because its execution is not finished.

When a DECLARE HANDLER block can handle more than one error condition, it may
be useful to know which errors occurred. To do so, you can use the GET
DIAGNOSTICS statement.

An error that is handled by a DECLARE HANDLER construct can be issued again
using the RESIGNAL statement.

Below is an example using DECLARE HANDLER:

CREATE TABLE test.t (s1 INT, PRIMARY KEY (s1));

DELIMITER //

CREATE PROCEDURE handlerdemo ( )
  BEGIN
   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
   SET @x = 1;
   INSERT INTO test.t VALUES (1);
   SET @x = 2;
   INSERT INTO test.t VALUES (1);
   SET @x = 3;
  END;
  //

DELIMITER ;

CALL handlerdemo( );

SELECT @x;
+------+
| @x   |
+------+
|    3 |
+------+

URL: https://mariadb.com/kb/en/declare-handler/