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值或者它可以是SQLWARNINGNOTFOUNDSQLEXCEPTION条件,这是SQLSTATE值类的简写。NOTFOUND条件用于游标或SELECT INTO variable_list语句。
  • 与MySQL错误代码或SQLSTATE值相关联的命名条件。

该语句可以是一个简单的语句或由BEGINEND关键字包围的复合语句。

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

首先,为了更好地演示,我们创建一个名为article_tags的新表:


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

SQL

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 ;

SQL

然后,通过调用insert_article_tags存储过程,为文章ID为1添加标签ID:1,23,如下所示:


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

SQL

之后,尝试插入一个重复的键来检查处理程序是否真的被调用。


CALL insert_article_tags(1,3);

SQL

执行上面查询语句,得到以下结果 -


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更改为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 ;

SQL

执行上面查询语句,得到以下结果 -


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错误代码,因为在MySQL中它是最具体的。 SQLSTATE可以映射到许多MySQL错误代码,因此它不太具体。 SQLEXCPETIONSQLWARNINGSQLSTATES类型值的缩写,因此它是最通用的。

假设在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 ;

SQL

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


CALL insert_article_tags_3(1,3);

SQL

如下,可以看到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;

SQL

1051号是什么意思? 想象一下,你有一个大的存储过程代码使用了好多类似这样的数字; 这将成为维护代码的噩梦。

幸运的是,MySQL为我们提供了声明一个命名错误条件的DECLARE CONDITION语句,它与条件相关联。

DECLARE CONDITION语句的语法如下:


DECLARE condition_name CONDITION FOR condition_value;

SQL

condition_value可以是MySQL错误代码,例如:1015SQLSTATE值。 condition_valuecondition_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;

SQL

这段代码比以前的代码显然更可读。

请注意,条件声明必须出现在处理程序或游标声明之前。

 

MySQL SIGNAL语句  来引发存储过程中的错误条件。

 

使用SIGNAL语句在存储的程序(例如存储过程,存储函数触发器事件)中向调用者返回错误或警告条件。 SIGNAL语句提供了对返回值(如值和消息SQLSTATE)的信息的控制。

以下说明SIGNAL语句的语法:


SIGNAL SQLSTATE | condition_name;
SET condition_information_item_name_1 = value_1,
    condition_information_item_name_1 = value_2, etc;

SQL

SIGNAL关键字是由DECLARE CONDITION语句声明的SQLSTATE值或条件名称。 请注意,SIGNAL语句必须始终指定使用SQLSTATE值定义的SQLSTATE值或命名条件。

要向调用者提供信息,请使用SET子句。如果要使用值返回多个条件信息项名称,则需要用逗号分隔每个名称/值对。

condition_information_item_name可以是MESSAGE_TEXTMYSQL_ERRORNOCURSOR_NAME等。

以下存储过程将订单行项目添加到现有销售订单中。 如果订单号码不存在,它会发出错误消息。


DELIMITER $$

CREATE PROCEDURE AddOrderItem(in orderNo int,
 in productCode varchar(45),
 in qty int,in price double, in lineNo int )

BEGIN
 DECLARE C INT;

 SELECT COUNT(orderNumber) INTO C
 FROM orders 
 WHERE orderNumber = orderNo;

 -- check if orderNumber exists
 IF(C != 1) THEN 
 SIGNAL SQLSTATE '45000'
 SET MESSAGE_TEXT = 'Order No not found in orders table';
 END IF;
 -- more code below
 -- ...
END $$
DELIMITER ;

SQL

首先,它使用传递给存储过程的输入订单号对订单进行计数。
第二步,如果订单数不是1,它会引发SQLSTATE 45000的错误以及orders表中不存在订单号的错误消息。

请注意,45000是一个通用SQLSTATE值,用于说明未处理的用户定义异常。

如果调用存储过程AddOrderItem(),但是传递不存在的订单号,那么将收到一条错误消息。


CALL AddOrderItem(10,'S10_1678',1,95.7,1);

SQL

执行上面代码,得到以下结果 -


mysql> CALL AddOrderItem(10,'S10_1678',1,95.7,1);
1644 - Order No not found in orders table
mysql>

 

 

 

 

 

 

 

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页