(一)当存储过程中发生错误时,重要的是适当处理它,例如:继续或退出当前代码块的执行,并发出有意义的错误消息。
声明处理程序
要声明一个处理程序,您可以使用DECLARE HANDLER
语句如下:
DECLARE action HANDLER FOR condition_value statement;
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
- 值相关联的命名条件。
该语句可以是一个简单的语句或由BEGIN
和END
关键字包围的复合语句。
存储过程中的MySQL处理程序示例
首先,为了更好地演示,我们创建一个名为article_tags
的新表:
USE testdb;
CREATE TABLE article_tags(
article_id INT,
tag_id INT,
PRIMARY KEY(article_id,tag_id)
);
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 ;
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
,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,1);
CALL insert_article_tags(1,2);
CALL insert_article_tags(1,3);
SQL
之后,尝试插入一个重复的键来检查处理程序是否真的被调用。
CALL insert_article_tags(1,3);
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
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 ;
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> 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错误代码,因此它不太具体。 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 ;
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);
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
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;
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;
DECLARE condition_name CONDITION FOR condition_value;
SQL
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;
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;
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_TEXT
,MYSQL_ERRORNO
,CURSOR_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 ;
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);
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>
mysql> CALL AddOrderItem(10,'S10_1678',1,95.7,1);
1644 - Order No not found in orders table
mysql>