MySQL 存储过程中的错误处理

MySQL 存储过程中的错误处理实例

第一步：建一个表

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

第二步：建立存储过程insert_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 $$

第三步：插入正常数据

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

第四步：异常数据处理（有主键约束，报错了）

CALL insert_article_tags(1,3);

第五步：测试其他异常及运行级别顺序（）：

DECLARE EXIT HANDLER FOR 1062 SELECT 'MySQL error code 1062 invoked as errmsg'; DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000 invoked' as errmsg; DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'MySQL error code 1062 invoked as errmsg';

5.1创建存储过程

DELIMITER $$ CREATE PROCEDURE insert_article_tags_2(IN article_id INT, IN tag_id INT) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLException invoked ' as errmsg; DECLARE EXIT HANDLER FOR 1062 SELECT 'MySQL error code 1062 invoked' as errmsg; DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000 invoked' as errmsg; -- 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 $$

5.2测试结果

CALL insert_article_tags_2(1,3);

结果为：

MySQL error code 1062 invoked

分别屏蔽存储过程如下insert_article_tags_2中 /* DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLException invoked ’ as errmsg;*/ /DECLARE EXIT HANDLER FOR 1062 SELECT ‘MySQL error code 1062 invoked’ as errmsg;/ /DECLARE EXIT HANDLER FOR SQLSTATE ‘23000’ SELECT ‘SQLSTATE 23000 invoked’ as errmsg;/ 测试最中结果 优先级：【FOR 1062 】>【FOR SQLSTATE ‘23000’】>【FOR SQLEXCEPTION 】

6.异常回滚或继续执行 CREATE DEFINER=root@% PROCEDURE insert_article_tags_3(IN article_id INT, IN tag_id INT)

BEGIN DECLARE has_error int(11); DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated' as errmsg; END; /*DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000 invoked' as errmsg;*/ -- 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

7.异常条件判断

CREATE DEFINER=`root`@`%` PROCEDURE `insert_article_tags_5`(IN article_id INT, IN tag_id INT) BEGIN DECLARE has_error int(11); DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated' as errmsg; END; DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 5; SELECT 'MySQL error code 1062 invoked' as errmsg; /*DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000 invoked' as errmsg;*/ -- 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; IF has_error <> 5 THEN SELECT CONCAT('ERR0R ', has_error) as msg; ELSE SELECT CONCAT('正常 ', has_error) as msg; END IF; END

运行结果：

CALL insert_article_tags_5(1,3); Result1: MySQL error code 1062 invoked Result2: 4 Result3: ERR0R 5

注意：别把异常判断放在了出现异常的前面，如存储过程insert_article_tags_4就是错误的，这样就找不到has_error值了 8.游标示例

CREATE TABLE `t_users` ( `ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `user_name` VARCHAR(60), `user_pass` VARCHAR(64), PRIMARY KEY (`ID`) ); INSERT INTO t_users VALUES(null,'sheng','sheng123'), (null,'yu','yu123'), (null,'ling','ling123'); DELIMITER // DROP PROCEDURE test_cursor ; CREATE PROCEDURE test_cursor (out result varchar(100)) BEGIN DECLARE name VARCHAR(20); DECLARE pass VARCHAR(20); DECLARE done INT; DECLARE cur_test CURSOR FOR SELECT user_name,user_pass FROM t_users; DECLARE continue handler FOR SQLSTATE '02000' SET done = 1; OPEN cur_test; repeat FETCH cur_test into name,pass; SELECT concat(',',name,pass); until done END repeat; CLOSE cur_test; END // call test_cursor(@test) select @test