MySQL的存储过程/触发器/事务的总结(二):异常处理

在其他编程语言中,例如java,C#,C++ 等都会有异常处理,通常是try...catch. Mysql中也可以异常处理,只不过并不是try..catch.. 而是 定义条件 和 处理程序.

DECLARE 定义条件和处理程序 (异常处理)

• 定义条件 => 定义可能会出现的错误
• 处理程序 => 定义处理异常的程序
• 综合可以理解为存储过程的异常处理

定义条件(捕获异常)

DECLARE condition_name CONDITION FOR condition_value


condition value:
SQLSTATE [VALUE] sqlstate_value | mysql_error_code

--方法一：使用sqlstate_value
DECLARE can_not_find CONDITION FOR SQLSTATE '42S02';

--方法二：使用 mysql_error_code
DECLARE can_not_find CONDITION FOR 1146;

其中：

• condition_name 参数表示条件的名称；
• condition_value 参数表示条件的类型；
• sqlstate_value 参数和 mysql_error_code 参数都可以表示 MySQL 的错误。sqlstate_value 表示长 度为 5 的字符串类型错误代码，mysql_error_code 表示数值类型错误代码。例如 ERROR 1146(42S02) 中，sqlstate_value 值是 42S02，mysql_error_code 值是 1146。

定义处理程序(异常处理的方法)

• 处理程序也是有作用域的说法,处理程序的在用域是在同一个 being...end下.

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

sp_statement: 参数为程序语句段，表示在遇到定义的错误时，需要执行的一些存储过程或函数

其中，handler_type 参数指明错误的处理方式，该参数有 3 个取值

• CONTINUE 表示遇到错误不进行处理，继续向下执行；
• EXIT 表示遇到错误后马上退出；
• UNDO 表示遇到错误后撤回之前的操作，MySQL 中暂时还不支持这种处理方式。

其中 condition_value 该参数有 6 个取值:

• sqlstate_value：包含 5 个字符的字符串错误值；
• condition_name：表示 DECLARE 定义的错误条件名称 (也就是上面说的定义条件)；
• SQLWARNING：匹配所有以 01 开头的 sqlstate_value 值；
• NOT FOUND：匹配所有以 02 开头的 sqlstate_value 值；
• SQLEXCEPTION：匹配所有没有被 SQLWARNING 或 NOT FOUND 捕获的 sqlstate_value 值；
• mysql_error_code：匹配数值类型错误代码。

例子:

--方法一：捕获 sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='CAN NOT FIND';

--方法二：捕获 mysql_error_code
DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';

--方法三：先定义条件，然后调用
DECLARE can_not_find CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR can_not_find SET @info='CAN NOT FIND';

--方法四：使用 SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';

--方法五：使用 NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';

--方法六：使用 SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

综合例子

mysql> CREATE TABLE t8(s1 INT,PRIMARY KEY(s1));
Query OK, 0 rows affected (0.07 sec)

mysql> DELIMITER //
mysql> CREATE PROCEDURE handlerdemo()
    -> BEGIN
    -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @X2=1;
    -> SET @X=1;
    -> INSERT INTO t8 VALUES(1);
    -> SET @X=2;
    -> INSERT INTO t8 VALUES(1);
    -> SET @X=3;
    -> END //
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;
mysql> CALL handlerdemo();
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @X;
+------+
| @X   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

• 上述代码中，@X 是一个用户变量，执行结果 @X 等于 3，这表明 MySQL 执行到程序的末尾。

• 如果DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @X2=1;这一行不存在，第二个 INSERT 因 PRIMARY KEY 约束而失败之后，MySQL 可能已经采取 EXIT 策略，且 SELECT @X 可能已经返回 2。

• 注意：@X 表示用户变量，使用 SET 语句为其赋值，用户变量与连接有关，一个客户端定义的变量不能被其他客户端所使用，当客户端退出时，该客户端连接的所有变量将自动释放。