Mysql Programming CS 155P笔记(四) Handling Errors

Handling Errors(条件处理(Condition Handling) / 错误、异常处理)

Declaring a handler

To declare a handler, you use the  DECLARE HANDLER statement as follows:

 

DECLARE action HANDLER FOR condition_value statement;

If a condition whose value matches the  condition_value , MySQL will execute the statement and continue or exit the current code block based on the action .

The action accepts one of the following values:

• CONTINUE :  the execution of the enclosing code block ( BEGIN … END ) continues. //当使用continue时会继续执行Begin...end里面的内容
• EXIT : the execution of the enclosing code block, where the handler is declared, terminates. //按解析当declear语句在procedure 或 function的begin ... end内当使用exit时会直接跳出内容，但如果是declare下面的begin ... end还是会执行

The  condition_value specifies a particular condition or a class of conditions that activates the handler. The  condition_value accepts one of the following values:

• A MySQL error code.
• A standard SQLSTATE value. Or it can be an SQLWARNING , NOTFOUND or SQLEXCEPTION condition, which is shorthand for the class of SQLSTATE values. The NOTFOUND condition is used for a cursor or  SELECT INTO variable_list statement.
• A named condition associated with either a MySQL error code or SQLSTATE value.

·         SQLWARNING是对所有以01开头的SQLSTATE代码的速记。

·         NOT FOUND是对所有以02开头的SQLSTATE代码的速记。

·         SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。

下面是定义处理程序的几种方式

方法一：捕获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 NOT FOUND SET @info='CAN NOT FIND';

方法六：使用SQLEXCEPTION

DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

The statement could be a simple statement or a compound statement enclosing by the BEGIN and END keywords. 

The following handler means that if an error occurs, set the value of the  has_error variable to 1 and continue the execution.

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;

The following is another handler which means that in case any error occurs, rollback the previous operation, issue an error message, and exit the current code block. If you declare it inside the BEGIN END block of a stored procedure, it will terminate stored procedure immediately.

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';
END;

 

MySQL handler example in stored procedures

First, we create a new table named  article_tags for the demonstration:

CREATE TABLE article_tags(
    article_id INT,
    tag_id     INT,
    PRIMARY KEY(article_id,tag_id)
);

　　

The  article_tags table stores the relationships between articles and tags. Each article may have many tags and vice versa. For the sake of simplicity, we don’t create articles and tags tables, as well as the foreign keys in the  article_tags table.

Next, we create a stored procedure that inserts article id and tag id into the article_tags table: (这里action使用continue)

DELIMITER $$
 
CREATE PROCEDURE insert_article_tags(IN article_id INT, IN tag_id INT)
BEGIN
 
 DECLARE CONTINUE HANDLER FOR 1062
 SELECT CONCAT('duplicate keys (',article_id,',',tag_id,') found') AS msg;
 
 -- insert a new record into article_tags
 INSERT INTO article_tags(article_id,tag_id)
 VALUES(article_id,tag_id);
 
 -- return tag count for the article
 SELECT COUNT(*) FROM article_tags;
END

Then, we add tag id 1, 2 and 3 for the article 1 by calling the insert_article_tags  stored procedure as follows:

CALL insert_article_tags(1,1);
CALL insert_article_tags(1,2);
CALL insert_article_tags(1,3);

mysql> CALL insert_article_tags(1,1);
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

当执行Call insert_article_tags(1, 1)时，当未捕获到错误时，所有所有sql语句都会执行包括select count(*)

mysql> CALL insert_article_tags(1,3);
+----------------------------+
| msg                        |
+----------------------------+
| duplicate keys (1,3) found |
+----------------------------+
1 row in set (0.00 sec)

+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

当捕获到错误时，因为使用conintue，包括select count(*)这条语句就算之前有错误都不会终止程序一样执行

当我们把上面的procedure的action从continue改成exit的话，下面的变化将会是:

mysql> CALL insert_article_tag(1,3);
+----------------------------+
| msg                        |
+----------------------------+
| duplicate keys (1,3) found |
+----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

从这里证明action为exit都会执行后面的statement，但在procedure里面执行后面的statement后马上就会退出程序

If we change the CONTINUE in the handler declaration to EXIT , we will get an error message only.

DELIMITER $$
 
CREATE PROCEDURE insert_article_tags_2(IN article_id INT, IN tag_id INT)
BEGIN
 
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 SELECT 'SQLException invoked';
 
 DECLARE EXIT HANDLER FOR 1062 
        SELECT 'MySQL error code 1062 invoked';
 
 DECLARE EXIT HANDLER FOR SQLSTATE '23000'
 SELECT 'SQLSTATE 23000 invoked';
 
 -- insert a new record into article_tags
 INSERT INTO article_tags(article_id,tag_id)
   VALUES(article_id,tag_id);
 
 -- return tag count for the article
 SELECT COUNT(*) FROM article_tags;
END

mysql> call insert_article_tags_2(1, 4);
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call insert_article_tags_2(1, 4);
+-------------------------------+
| MySQL error code 1062 invoked |
+-------------------------------+
| MySQL error code 1062 invoked |
+-------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

当程序正常的时候会全部执行所有的sql语句，但使用exit做捕获异常的时候，程序就会直接退出，不会执行select (*)语句

MySQL handler precedence

In case there are multiple handlers that are eligible for handling an error, MySQL will call the most specific handler to handle the error first.

An error always maps to one MySQL error code because in MySQL it is the most specific. An SQLSTATEmay map to many MySQL error codes therefore it is less specific. An SQLEXCPETION or an SQLWARNINGis the shorthand for a class of SQLSTATES values so it is the most generic.

Based on the handler precedence’s rules,  MySQL error code handler, SQLSTATE handler and SQLEXCEPTION takes the first, second and third precedence.

优先级别分别为Mysql error code, SQLSTATE, SQLEXCEPTION
MySQL特殊的错误编码（如1146），是一个整数类型的代码，这些错误编码并不适用于其他的数据库产品；
MySQL的SQL状态值，由5个字符组成（如42S02），该值支持ANSI SQL、ODBC或其他标准协议，并不是所有的MySQL返回的错误信息都有SQL状态值，对于没有SQL状态值使用‘HY000’代替。

Suppose we declare three handlers in the  insert_article_tags_3 stored procedure as follows:

DELIMITER $$
 
CREATE PROCEDURE insert_article_tags_3(IN article_id INT, IN tag_id INT)
BEGIN
 
 DECLARE EXIT HANDLER FOR 1062 SELECT 'Duplicate keys error encountered';
 DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLException encountered';
 DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000';
 
 -- insert a new record into article_tags
 INSERT INTO article_tags(article_id,tag_id)
 VALUES(article_id,tag_id);
 
 -- return tag count for the article
 SELECT COUNT(*) FROM article_tags;
END

mysql> CALL insert_article_tags_3(1,3);
+----------------------------------+
| Duplicate keys error encountered |
+----------------------------------+
| Duplicate keys error encountered |
+----------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

从结果上看可以看出MySQL error code是优先执行

DECLARE....HANDLER语句
这个语句用于但数据库出现某种情况的时候(condition，大部分指发生某种错误时)，来定义具体的处理办法(handler)；所以这里涉及到包括：a、就是个什么情况 b、如何处理它；下面是其格式

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

这里面需要注意几点：

      a、condition_value [,condition_value]，这个的话说明可以包括多种情况(方括弧表示可选的)，也就是一个handler可以定义成针对多种情况进行相应的操作；另外condition_value可以包括的值有上面列出来的6种：

                1、mysql_error_code，这个表示mysql的错误代码，错误代码是一个数字，完成是由mysql自己定义的，这个值可以参考mysql数据库错误代码及信息。

                2、SQLSTATE [VALUE] sqlstate_value，这个同错误代码类似形成一一对应的关系，它是一个5个字符组成的字符串，关键的地方是它从ANSI SQL和ODBC这些标准中引用过来的，因此更加标准化，而不像上面的error_code完全是mysql自己定义给自己用的，这个和第一个类似也可以参考mysql数据库错误代码及信息。

                3、condtion_name，这个是条件名称，它使用DECLARE...CONDITION语句来定义，这个后面我们会介绍如何定义自己的condition_name。

                4、SQLWARNING，表示SQLTATE中的字符串以‘01’起始的那些错误，比如Error: 1311 SQLSTATE: 01000 (ER_SP_UNINIT_VAR)

                5、NOT FOUND，表示SQLTATE中的字符串以‘02’起始的那些错误，比如Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)，其实这个错误就是用在我们介绍游标的那个问题所出现的情况，也就是没有fetch到记录，也就是我们游标到记录的尾巴了的情况。

                6、SQLEXCEPTION，表示SQLSTATE中的字符串不是以'00'、'01'、'02' 起始的那些错误，这里'00'起始的SQLSTATE其实表示的是成功执行而不是错误，另外两个就是上面的4和5的两种情况。

上面的6种情况其实可以分为两类，一类就是比较明确的处理，就是对指定的错误情况进行处理，包括1、2、3这三种方式；另一类是对对应类型的错误的处理，就是对某一群错误的处理，包括4、5、6这三种方式。这个是介绍了condition_value。另外还要注意的一个内容是MySQL在默认情况下(也就是我们没有定义处理错误的方法-handler)自己的错误处理机制：1、对于SQLWARNING和NOT FOUND的处理方法就是无视错误继续执行，所以在游标的例子里面如果我们没有对repeat的条件判断的那个值做个no_more_products=1的handler来处理，那么循环就会一直下去。2、对于SQLEXCEPTION的话，其默认的处理方法是在出现错误的地方就终止掉了。

      b、statement，这个比较简单就是当出现某种条件/错误时，我们要执行的语句，可以是简单的如 SET  var = value这样的简单的语句，也可以是复杂的多行的语句，多行的话可以使用BEGIN  .....  END这里把语句包括在里面(这个好比delphi里面的情况，注意到我们的存储过程也是多行的，所以也要BEGIN .... END)。

      c、handler_action，这个表示当执行完上面的statement后，希望执行怎样的动作，这里包括CONTINUE、EXIT、UNDO，表示继续、退出、撤销(暂时不支持)。这边就是两种动作，其实这两种动作在上面也说过了，CONTINUE就是一个是SQLWARNING和NOT FOUND的默认处理方法，而EXIT就是SQLEXCEPTION的默认处理方法。

来看个简单的例子，这里创建一个对SQLSTATE的代码为'23000'的错误(重复的主键)进行处理的HANDLER，每次发生时我们对变量@x进行增加1：

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`ConditionProc` $$
CREATE PROCEDURE `test`.`ConditionProc` ()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x = @x+1;
INSERT INTO products values(1,default,default,default,default,default);
INSERT INTO products values(1,default,default,default,default,default);
SELECT @x;
END $$

DELIMITER ;

代码比较简单，当然这里我们需要对数据库进行一点修改，把除了主键外其他的字段设置了默认值，方便测试

测试比较简单，这里我们数据库里面本身就有记录了，然后在Qurey browser中先输入

SET @x = 0;

然后调用存储过程下面是结果：

通过结果我们知道出现了两次插入的记录同原有的记录出现主键重复的情况。当然这个是由下面这个代码触发的。

INSERT INTO products values(1,default,default,default,default,default);

DECLARE...CONDITION语句
这个语句其实是为了让我们的错误条件更加的清晰明了化的，对于上面的情况，像SQLSTATE '23000'这种表示是一种很不直观的方法，要通过相应的文档去对应，阅读起来比较不方便。而DECLARE....CONDITION可以对条件定义相对应的名称，看个例子就清楚了：

DECLARE duplicate_key CONDITION FOR SQLSTATE '23000';
DECLARE CONTINUE HANDLER FOR duplicate_key
  BEGIN
    -- body of handler
  END;

 当然在上面的例子我们没有用到BEGIN....END，我们只有一行SET @x=@x+1；这里我们用duplicate_key这个条件名称来对应到SQLSTATE '23000'上面，这样查看起来更加的直观。这你duplicate_key就是我们上面介绍DECLARE....HANDLER时候的那个condition_name。