Mysql 异常处理--condition和handler

定义条件和处理

条件的定义和处理可以用来定义在处理过程中遇到问题时相应的处理步骤。

1.条件的定义

DECLARE condition_name CONDITION FOR condition_value

condition_value:
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value

declare..condition 为一个error声明一个别名，将这个别名与错误连接起来，相当于给错误代码起一个友好的别名供异常处理使用
随后的declare..handler 可以使用这个别名

condition_value可以是一个mysql error code或一个SQLSTATE(由5个字符组成)。记住不要使用mysql error code 0 或 以 ‘00’开头的code或一个SQLSTATE，因为这些指示成功而不是一个错误条件
error codes 和 SQLSTATE列表file:///Volumes/SamSung%20SD/Material/Mysql/refman-5.6-en.html-chapter/error-handling.html#error-messages-server

使用别名可以让代码更加便于理解。举个例子，这个处理程序适用于尝试删除一个不存在的表，但这是因为你知道1051错误代码的定义才会这样声明handler

DECLARE CONTINUE HANDLER FOR 1051
  BEGIN
    -- body of handler
  END;
 
 
DECLARE CONTINUE HANDLER FOR 1051
  BEGIN
    -- body of handler
  END;

通过为1051声明一个别名，这段处理程序的目的将更容易被读者了解

DECLARE no_such_table CONDITION FOR 1051;
DECLARE CONTINUE HANDLER FOR no_such_table
  BEGIN
    -- body of handler
  END;
 
 
DECLARE no_such_table CONDITION FOR 1051;
DECLARE CONTINUE HANDLER FOR no_such_table
  BEGIN
    -- body of handler
  END;

下面是一个错误相同，但是基于相应的SQLSTATE值而不是MySQL error code的例子:

DECLARE no_such_table CONDITION FOR SQLSTATE '42S02';
DECLARE CONTINUE HANDLER FOR no_such_table
  BEGIN
    -- body of handler
  END;
 
 
DECLARE no_such_table CONDITION FOR SQLSTATE '42S02';
DECLARE CONTINUE HANDLER FOR no_such_table
  BEGIN
    -- body of handler
  END;

SQLSTATE 42S02 为所有没有该表错误的总称:

mysql> SELECT * FROM FAN;
ERROR 1146 (42S02): Table 'fandb.FAN' doesn't exist
mysql> DROP TABLE FAN;
ERROR 1051 (42S02): Unknown table 'fandb.FAN'

Error: 1051 SQLSTATE: 42S02 (ER_BAD_TABLE_ERROR)

Message: Unknown table '%s'

Error: 1109 SQLSTATE: 42S02 (ER_UNKNOWN_TABLE)  -multi delete时没有该表

Message: Unknown table '%s' in %s 

Error: 1146 SQLSTATE: 42S02 (ER_NO_SUCH_TABLE)

Message: Table '%s.%s' doesn't exist
 
 

  
  

需要注意的是使用signal和resignal是只能使用SQLSTATE不能使用mysql error code

2.条件的处理

DECLARE handler_action HANDLER
FOR condition_value [, condition_value] …
statement

handler_action:
CONTINUE
| EXIT
| UNDO

condition_value:
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION

declare..handler事件指定一个handler处理一个或多个条件，当其中一个条件触发，指定的事件就会被执行(所说的事件可以是一个简单的set var_name=value或 compound statement written using BEGIN and END )

必须在变量或condition后声明handler

handler_action指明当handler被触发后需要执行什么动作:

1.continue:继续执行程序

2.exit:当条件触发(This is true even if the condition occurs in an inner block.)，handler事件执行后终止程序

3.undo:目前还不支持此功能

condition_value指明handler被何种条件触发:

1.mysql error code或SQLSTATE value。记住不要使用mysql error code 0 或 以 ‘00’开头的code或一个SQLSTATE，因为这些指示成功而不是一个错误条件

2.在声明handler前被declare..condition语句声明的condition name

3.SQLWARNING -代表所有已01开头的SQLSTATE，意思就是declare continue|exit handler for SQLWARNING.泛指一类异常

4.NOT FOUND -代表所有以02开头的SQLSTATE

This is relevant within 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). For an example, see Section 13.6.6, “Cursors”. This condition also occurs for SELECT … INTO var_list statements that retrieve no rows.

5.SQLEXCEPTION -代表所有未被SQLWARNING或NOT FOUND捕获的SQLSTATE(class of SQLSTATE values that do not begin with ‘00’, ‘01’, or ‘02’)

如果条件被触发，却没有handler被声明用于处理该条件，程序的进行将取决于条件类型(the action taken depends on the condition class)。

1.For SQLEXCEPTION conditions:程序终止，抛出异常，就好似有个exit handler一样

the stored program terminates at the statement that raised the condition, as if there were an EXIT handler。如果该程序是被其他程序调用，那么由调用者处理异常

2.For SQLWARNING conditions:程序继续运行，就好像有个continue handler

3.For NOT FOUND conditions:if the condition was raised normally, the action is CONTINUE. If it was raised by SIGNAL or RESIGNAL, the action is EXIT.

关于compound-statement复合语句

[begin_label:] BEGIN
[statement_list]
END [end_label]
存储子程序可以使用BEGIN … END复合语句来包含多个语句。statement_list 代表一个或多个语句的列表。statement_list之内每个语句都必须用分号(;)来结尾。
复合语句可以被标记(或者说起个标签label)。除非begin_label存在,否则end_label不能被给出,并且如果二者都存在,他们必须是同样的。

例子:

1.
这里使用主键冲突模拟
mysql> insert into incr values(2);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

delimiter $$

create procedure actor_insert()
begin
    declare duplicatekey condition for sqlstate '23000';
    declare continue handler for duplicatekey set @b=1;
    set @a=1;
    insert into incr values(1);
    set @a=2;
    insert into incr values(2);
    set @a=3;
    insert into incr values(3);
end $$

delimiter ;

2.
CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
 
1.
这里使用主键冲突模拟
mysql> insert into incr values(2);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

delimiter $$

create procedure actor_insert()
begin
    declare duplicatekey condition for sqlstate '23000';
    declare continue handler for duplicatekey set @b=1;
    set @a=1;
    insert into incr values(1);
    set @a=2;
    insert into incr values(2);
    set @a=3;
    insert into incr values(3);
end $$

delimiter ;

2.
CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

使用condition别名和begin..end复合语句的handler

1.continue
delimiter $$

create procedure actor_insert()
begin
    declare duplicatekey condition for sqlstate '23000';
    declare continue handler for duplicatekey begin select 'hello world';set @b=8;end;
    set @a=1;
    insert into incr values(1);
    set @a=2;
    insert into incr values(2);
    set @a=3;
    insert into incr values(3);
end $$

delimiter ;

mysql> delete from incr where id in (1,3);
Query OK, 2 rows affected (0.02 sec)

mysql> call actor_insert();
+-------------+
| hello world |
+-------------+
| hello world |
+-------------+
1 row in set (0.01 sec)

Query OK, 1 row affected (0.04 sec)

mysql> select @a,@b;
+------+------+
| @a   | @b   |
+------+------+
|    3 |    8 |
+------+------+
1 row in set (0.00 sec)

mysql> select * from incr;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

2.exit
delimiter $$

create procedure actor_insert()
begin
    declare duplicatekey condition for sqlstate '23000';
    declare exit handler for duplicatekey begin select 'hello world';set @b=8;end;
    set @a=1;
    insert into incr values(1);
    set @a=2;
    insert into incr values(2);
    set @a=3;
    insert into incr values(3);
end $$

delimiter ;

mysql> call actor_insert();
+-------------+
| hello world |
+-------------+
| hello world |
+-------------+
1 row in set (0.33 sec)

Query OK, 0 rows affected (0.33 sec)

mysql> select * from incr;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> select @a,@b;
+------+------+
| @a   | @b   |
+------+------+
|    2 |    8 |
+------+------+
1 row in set (0.00 sec)