MySql error handling (2)-Condition & Handle

Document directory

• Criteria 2.10. Conditions and handling procedures

Criteria 2.10. Conditions and handling procedures

Limit 2.10.1. Declare Condition

Listen 2.10.2. Declare Handler

Specific conditions must be specific. These conditions can be associated with errors and general process control in subprograms.

Limit 2.10.1. Declare Condition

DECLARE condition_name CONDITION FOR condition_value

condition_value:

    SQLSTATE [VALUE] sqlstate_value

 | mysql_error_code

This statement specifies the conditions that require special processing. It associates a name with a specified error condition. This name can then be used in the declare handler statement. See section 2.10.2 "declare handler ".

Besides the sqlstate value, MySQL error codes are also supported.

Listen 2.10.2. Declare Handler

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

handler_type:

    CONTINUE

 | EXIT

 | UNDO

condition_value:

    SQLSTATE [VALUE] sqlstate_value

 | condition_name

 | SQLWARNING

 | NOT FOUND

 | SQLEXCEPTION

 | mysql_error_code

This statement specifies each handler that can process one or more conditions. If one or more conditions are generated, the specified statement is executed.

For a continue processing program, the execution of the current subroutine continues after the execution of the Processing Program Statement. For the exit handler, the execution of the current begin... end compound statement is terminated. Undo handler type statements are not supported yet.

· Sqlwarning is a shorthand for all sqlstate codes starting with 01.

· Not found is a shorthand for all sqlstate codes starting with 02.

· Sqlexception is a shorthand for all sqlstate Codes not captured by sqlwarning or not found.

Besides the sqlstate value, the MySQL error code is not supported.

For example:

mysql> CREATE TABLE test.t (s1 int,primary key (s1));

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

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

mysql> CALL handlerdemo()//

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//

    +------+

    | @x   |

    +------+

    | 3    |

    +------+

    1 row in set (0.00 sec)

Note that @ X is 3, which indicates that MySQL is executed to the end of the program. If declare continue handler for sqlstate '200' set @ X2 = 1; this row is not present, MySQL may have adopted the default exit path after the second insert fails due to the primary key force, and select @ X may have returned 2.