DECLARE HANDLER

Sintassi

DECLARE tipo_handler HANDLER
    FOR condizione [, condizione] ...
    istruzione

tipo_handler:
    CONTINUE
  | EXIT 
  | UNDO

condizione:
    SQLSTATE [VALUE] valore_sqlstate
  | nome_condizione
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | codice_errore_mariadb

Spiegazione

L'istruzione DECLARE ... HANDLER specifica un handler che può gestire una o più condizioni. Se una di queste condizioni si verifica, l'istruzione specificata viene eseguita. Essa può essere un comando semplice (per esempio, SET nome_var = valore), o un'istruzione composta scritta con BEGIN e END.

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 MySQL error code (a number). You should not use SQLSTATE value '00000' or MySQL error code 0, because those indicate sucess rather than an error condition. For a list of SQLSTATE values and MySQL error codes, see http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html.
• A condition name previously specified with DECLARE ... CONDITION. 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 http://dev.mysql.com/doc/refman/5.1/en/cursors.html. 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'.

Examples:

MariaDB [test]> CREATE TABLE test.t (s1 INT, PRIMARY KEY (s1));
Query OK, 0 rows affected (0.11 sec)

MariaDB [test]> delimiter //
MariaDB [test]> 
MariaDB [test]> 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;
    -> //
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> CALL handlerdemo()//
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT @x//
+------+
| @x   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)