系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > Mysql > 详细页面

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

时间:2019-12-22来源:系统城作者:电脑系统城

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

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


 
  1. DECLARE action HANDLER FOR condition_value statement;
  2.  

上述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并继续执行的例子:


 
  1. DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;
  2.  

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


 
  1. DECLARE EXIT HANDLER FOR SQLEXCEPTION
  2. BEGIN
  3. ROLLBACK;
  4. SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';
  5. END;
  6.  

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


 
  1. DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_row_found = 1;
  2.  

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


 
  1. DECLARE CONTINUE HANDLER FOR 1062
  2. SELECT 'Error, duplicate key occurred';
  3.  

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


 
  1. USE testdb;
  2. CREATE TABLE article_tags(
  3. article_id INT,
  4. tag_id INT,
  5. PRIMARY KEY(article_id,tag_id)
  6. );
  7.  

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

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


 
  1. USE testdb;
  2. DELIMITER $$
  3. CREATE PROCEDURE insert_article_tags(IN article_id INT, IN tag_id INT)
  4. BEGIN
  5. DECLARE CONTINUE HANDLER FOR 1062
  6. SELECT CONCAT('duplicate keys (',article_id,',',tag_id,') found') AS msg;
  7. -- insert a new record into article_tags
  8. INSERT INTO article_tags(article_id,tag_id)
  9. VALUES(article_id,tag_id);
  10. -- return tag count for the article
  11. SELECT COUNT(*) FROM article_tags;
  12. END$$
  13. DELIMITER ;
  14.  

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


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

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


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

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


 
  1. mysql> CALL insert_article_tags(1,3);
  2. +----------------------------+
  3. | msg |
  4. +----------------------------+
  5. | duplicate keys (1,3) found |
  6. +----------------------------+
  7. 1 row in set
  8. +----------+
  9. | COUNT(*) |
  10. +----------+
  11. | 3 |
  12. +----------+
  13. 1 row in set
  14. Query OK, 0 rows affected
  15.  

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

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


 
  1. DELIMITER $$
  2. CREATE PROCEDURE insert_article_tags_exit(IN article_id INT, IN tag_id INT)
  3. BEGIN
  4. DECLARE EXIT HANDLER FOR SQLEXCEPTION
  5. SELECT 'SQLException invoked';
  6. DECLARE EXIT HANDLER FOR 1062
  7. SELECT 'MySQL error code 1062 invoked';
  8. DECLARE EXIT HANDLER FOR SQLSTATE '23000'
  9. SELECT 'SQLSTATE 23000 invoked';
  10. -- insert a new record into article_tags
  11. INSERT INTO article_tags(article_id,tag_id)
  12. VALUES(article_id,tag_id);
  13. -- return tag count for the article
  14. SELECT COUNT(*) FROM article_tags;
  15. END $$
  16. DELIMITER ;
  17.  

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


 
  1. mysql> CALL insert_article_tags_exit(1,3);
  2. +-------------------------------+
  3. | MySQL error code 1062 invoked |
  4. +-------------------------------+
  5. | MySQL error code 1062 invoked |
  6. +-------------------------------+
  7. 1 row in set
  8. Query OK, 0 rows affected
  9.  

来看个图感受下:

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

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


 
  1. DELIMITER $$
  2. CREATE PROCEDURE insert_article_tags_3(IN article_id INT, IN tag_id INT)
  3. BEGIN
  4. DECLARE EXIT HANDLER FOR 1062 SELECT 'Duplicate keys error encountered';
  5. DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLException encountered';
  6. DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000';
  7. -- insert a new record into article_tags
  8. INSERT INTO article_tags(article_id,tag_id)
  9. VALUES(article_id,tag_id);
  10. -- return tag count for the article
  11. SELECT COUNT(*) FROM article_tags;
  12. END $$
  13. DELIMITER ;
  14.  

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


 
  1. CALL insert_article_tags_3(1,3);
  2.  

如下,可以看到MySQL错误代码处理程序被调用:


 
  1. mysql> CALL insert_article_tags_3(1,3);
  2. +----------------------------------+
  3. | Duplicate keys error encountered |
  4. +----------------------------------+
  5. | Duplicate keys error encountered |
  6. +----------------------------------+
  7. 1 row in set
  8. Query OK, 0 rows affected
  9.  

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


 
  1. DECLARE EXIT HANDLER FOR 1051 SELECT 'Please create table abc first';
  2. SELECT * FROM abc;
  3.  

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


 
  1. DECLARE condition_name CONDITION FOR condition_value;
  2.  

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


 
  1. DECLARE table_not_found CONDITION for 1051;
  2. DECLARE EXIT HANDLER FOR table_not_found SELECT 'Please create table abc first';
  3. SELECT * FROM abc;
  4.  

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

好啦,这次就到这里了。

更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL存储过程技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》及《MySQL数据库锁相关技巧汇总》

分享到:

相关信息

  • MySQL的核心查询语句详解

    一、单表查询 1、排序 2、聚合函数 3、分组 4、limit 二、SQL约束 1、主键约束 2、非空约束 3、唯一约束 4、外键约束 5、默认值 三、多表查询 1、内连接 1)隐式内连接: 2)显式内连接: 2、外连接 1)左外连接 2)右外连接 四...

    2023-10-30

  • Mysql中如何删除表重复数据

    Mysql删除表重复数据 表里存在唯一主键 没有主键时删除重复数据 Mysql删除表中重复数据并保留一条 准备一张表 用的是mysql8 大家自行更改 创建表并添加四条相同的数据...

    2023-10-30

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载