mysql存储过程之错误处理实例详解

本文实例讲述了mysql存储过程之错误处理。分享给大家供大家参考，具体如下：

当存储过程中发生错误时，重要的是适当处理它，例如：继续或退出当前代码块的执行，并发出有意义的错误消息。其中mysql提供了一种简单的方法来定义处理从一般条件(如警告或异常)到特定条件(例如特定错误代码)的处理程序。完事我们来使用declare handler语句来尝试声明一个处理程序，先来看语法：

declare action handler for condition_value statement;

上述sql中，如果条件的值与condition_value匹配，则mysql将执行statement，并根据该操作继续或退出当前的代码块。其中，操作(action)接受以下值之一：

• continue：继续执行封闭代码块(begin ... end)。
• exit：处理程序声明封闭代码块的执行终止。

condition_value指定一个特定条件或一类激活处理程序的条件。condition_value接受以下值之一：

• 一个mysql错误代码。
• 标准sqlstate值或者它可以是sqlwarning，notfound或sqlexception条件，这是sqlstate值类的简写。notfound条件用于游标或select into variable_list语句。
• 与mysql错误代码或sqlstate值相关联的命名条件。

最重要的是，上述sql可以是一个简单的语句或由begin和end关键字包围的复合语句。介绍完事之后，咱们来看几个声明处理程序的例子，首先是当程序发生错误时，将has_error变量的值设置为1并继续执行的例子：

declare continue handler for sqlexception set has_error = 1;

再来看当发生错误时，回滚上一个操作，发出错误消息，并退出当前代码块。 如果在存储过程的begin end块中声明它，则会立即终止存储过程：

declare exit handler for sqlexception
begin
rollback;
select 'an error has occurred, operation rollbacked and the stored procedure was terminated';
end;

以下处理程序的意思是，如果没有更多的行要提取，在光标或select into语句的情况下，将no_row_found变量的值设置为1并继续执行：

declare continue handler for not found set no_row_found = 1;

以下处理程序如果发生重复的键错误，则会发出mysql错误1062。 它发出错误消息并继续执行：

declare continue handler for 1062
select 'error, duplicate key occurred';

上面这些实例可能有点抽象，咱们废话不多说，先来创建一个名为article_tags的新表，来具体操作下：

use testdb;
create table article_tags(
  article_id int,
  tag_id   int,
  primary key(article_id,tag_id)
);

其中呢，article_tags表存储文章和标签之间的关系。每篇文章可能有很多标签，反之亦然。 为了简单起见，我们不会在article_tags表中创建文章(article)表和标签(tags)表以及外键。

完事呢，我们来创建一个存储过程，将文章的id和标签的id插入到article_tags表中：

use testdb;
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$$
delimiter ;

然后呢，我们通过调用insert_article_tags存储过程，为文章id为1添加标签id：1,2和3，如下所示：

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

我们再尝试插入一个重复的键来检查处理程序是否真的被调用：

call insert_article_tags(1,3);

执行上面查询语句，得到以下结果：

mysql> call insert_article_tags(1,3);
+----------------------------+
| msg            |
+----------------------------+
| duplicate keys (1,3) found |
+----------------------------+
1 row in set
+----------+
| count(*) |
+----------+
|    3 |
+----------+
1 row in set
query ok, 0 rows affected

执行后会收到一条错误消息。 但是，由于我们将处理程序声明为continue处理程序，所以存储过程继续执行。因此，最后获得了文章的标签计数值为：3。来看个图：

但是如果将处理程序声明中的continue更改为exit，那么将只会收到一条错误消息。如下查询语句：

delimiter $$
create procedure insert_article_tags_exit(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 $$
delimiter ;

执行上面查询语句，得到以下结果：

mysql> call insert_article_tags_exit(1,3);
+-------------------------------+
| mysql error code 1062 invoked |
+-------------------------------+
| mysql error code 1062 invoked |
+-------------------------------+
1 row in set
query ok, 0 rows affected

来看个图感受下：

如果我们使用多个处理程序来处理错误，mysql将调用最特定的处理程序来处理错误。这就涉及到优先级的问题了，我们来具体看下。

我们知道错误总是映射到一个mysql错误代码，因为在mysql中它是最具体的。 sqlstate可以映射到许多mysql错误代码，因此它不太具体。 sqlexcpetion或sqlwarning是sqlstates类型值的缩写，因此它是最通用的。假设在insert_article_tags_3存储过程中声明三个处理程序，如下所示：

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 $$
delimiter ;

然后我们尝试通过调用存储过程将重复的键插入到article_tags表中：

call insert_article_tags_3(1,3);

如下，可以看到mysql错误代码处理程序被调用：

mysql> call insert_article_tags_3(1,3);
+----------------------------------+
| duplicate keys error encountered |
+----------------------------------+
| duplicate keys error encountered |
+----------------------------------+
1 row in set
query ok, 0 rows affected

完事之后，咱们再来看下使用命名错误条件。从错误处理程序声明开始，如下：

declare exit handler for 1051 select 'please create table abc first';
select * from abc;

1051号是什么意思？ 想象一下，你有一个大的存储过程代码使用了好多类似这样的数字; 这将成为维护代码的噩梦。幸运的是，mysql为我们提供了声明一个命名错误条件的declare condition语句，它与条件相关联。declare condition语句的语法如下：

declare condition_name condition for condition_value;

condition_value可以是mysql错误代码，例如：1015或sqlstate值。 condition_value由condition_name表示。声明后，可以参考condition_name，而不是参考condition_value。所以可以重写上面的代码如下：

declare table_not_found condition for 1051;
declare exit handler for table_not_found select 'please create table abc first';
select * from abc;

这段代码比以前的代码显然更可读，不过我们要注意，条件声明必须出现在处理程序或游标声明之前。

好啦，这次就到这里了。