MySQL error handling (ii)-Condition & Handle

20.2.10. Conditions and handlers

20.2.10.1. DECLARE conditions

20.2.10.2. DECLARE handlers

Specific conditions require specific processing. These conditions can be linked to errors, as well as general process control in the subroutine.

20.2.10.1. DECLARE conditions

Condition_name Condition_value

Condition_value:

Sqlstate_value

Mysql_error_code

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

MySQL error code is supported in addition to the SQLSTATE value.

20.2.10.2. DECLARE handlers

Handler_type Condition_value sp_statement

Handler_type:

CONTINUE

| EXIT

| UNDO

Condition_value:

Sqlstate_value

Condition_name

| SQLWarning

| Not FOUND

| SQLEXCEPTION

Mysql_error_code

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

For a continue handler, the execution of the current subroutine continues after the execution of the handler statement. For the exit handler, the current begin ... The execution of the end compound statement is terminated. The UNDO handler type statement is not yet supported.

· SQLWarning is shorthand for all SQLSTATE codes that begin with 01.

· Not found is shorthand for all SQLSTATE codes that begin with 02.

· SqlException is shorthand for all SQLSTATE codes that are not captured by sqlwarning or not found.

In addition to the SQLSTATE value, the MySQL error code is not supported.

For example:

CREATE TABLE test.t (S1 int,primary key (S1));

Query OK, 0 rows Affected (0.00 sec)

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;

//

Query OK, 0 rows Affected (0.00 sec)

Call Handlerdemo ()//

Query OK, 0 rows Affected (0.00 sec)

SELECT @x//

+------+

| @x |

+------+

| 3 |

+------+

1 row in Set (0.00 sec)

Note that @x is 3, which indicates that MySQL was executed at the end of the program. If declare CONTINUE HANDLER for SQLSTATE ' 23000 ' SET @x2 = 1; This line is not available, and after the second insert fails due to primary key coercion, MySQL may have taken the default (EXIT) path, and select @x may have returned 2.

Http://www.cnblogs.com/end/archive/2011/04/01/2001946.html

MySQL error handling (ii)-Condition & Handle