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/