MySQL 存储过程异常处理

---

简介：在本教程中，您将学习如何使用MySQL处理程序来处理存储过程中遇到的异常或错误。 当存储过程内发生错误时，必须正确处理它，例如继续或退出当前代码块的执行，以及发出有意义的错误消息。 MySQL提供了一种简单的方法来定义处理程序，这些处理程序可以处理一般条件，例如特定条件的警告或异常，例如特定的错误代码。

---

声明处理程序

要声明处理程序，请使用以下  DECLARE HANDLER语句：

DECLARE action HANDLER FOR condition_value statement;

如果条件的值与之匹配  condition_value，MySQL将执行statement并继续或退出当前代码块action。 action接受下列值之一：

• CONTINUE：继续执行封闭代码块（BEGIN... END）。
• EXIT ：声明处理程序的封闭代码块的执行终止。

在  condition_value指定特定条件或类的激活处理程序的条件。所述  condition_value接受下列值之一：

• MySQL错误代码。
• 标准SQLSTATE值。或者它可以是一个SQLWARNING，NOTFOUND或SQLEXCEPTION条件，这是SQLSTATE值类的简写。NOTFOUND条件用于游标或  SELECT INTO variable_list语句。
• 与MySQL错误代码或SQLSTATE值相关联的命名条件。

statement会是一个简单的语句或由一个复合语句在BEGIN和END关键字内。

---

MySQL错误处理实例

让我们看几个声明处理程序的例子。 以下处理程序意味着如果发生错误，请将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';

---

存储过程中的MySQL处理程序实例

首先，我们创建一个新表article_tags：

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

article_tags表存储文章和标签之间的关系。每篇文章可能有很多标签，反之亦然。为了简便起见，我们不创建articles和tags表格，还有外键的article_tags表。 接下来，我们创建一个存储过程，将article id和tag id插入到article_tags表中：

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  存储过程为文章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);

我们收到了一条错误消息。但是，因为我们将处理程序声明为处理CONTINUE程序，所以存储过程继续执行。结果，我们也获得了文章的标签计数。

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)

Query OK, 0 rows affected (0.00 sec)

如果我们将CONTINUE处理程序声明更改为EXIT，我们将只收到错误消息。

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

最后，我们可以尝试添加重复键以查看效果。

CALL insert_article_tags_2(1,3);

mysql> CALL insert_article_tags_2(1,3);
+-------------------------------+
| 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)

---

MySQL处理程序优先级

如果有多个处理程序有资格处理错误，MySQL将调用最具体的处理程序来首先处理错误。 错误总是映射到一个MySQL错误代码，因为在MySQL中它是最具体的。一个SQLSTATE可以被映射到很多的MySQL错误代码，因此，它是较不具体。一个SQLEXCPETION或一个SQLWARNING是一类SQLSTATES值的简写，因此它是最通用的。 基于处理程序优先级的规则，MySQL错误代码处理程序，SQLSTATE处理程序并SQLEXCEPTION采用第一，第二和第三优先级。 假设我们在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 (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

---

使用指定的错误条件

让我们从错误处理程序声明开始。

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  可以是如1015或一个MySQL错误代码这样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;

此代码显然比前一代码更具可读性。 请注意，条件声明必须出现在处理程序或游标声明之前。

---