Stai visualizzando una vecchia versione di questo article. Visualizza la versione più recente.


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

  | EXIT 
  | UNDO

    SQLSTATE [VALUE] valore_sqlstate
  | nome_condizione
  | codice_errore_mariadb


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


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


Sto caricando i commenti......
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.