MySQL - 存储过程（2） - 定义条件和处理程序

参考：<<MySQL5.7从入门到精通>>

特定条件需要特定处理。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了遇到这些问题所采取的处理方式，保证存储过程或函数遇到警告或错误时能够继续执行。以下介绍DECLARE关键字定义和处理程序。

一、定义条件

1. 语法格式

该语句为指定的错误创建条件，被用在定义处理程序的DECLARE HANDLER语句中。

DECLARE cond_name CONDITION FOR [cond_type]

[cond_type]：
SQLSTATE [VALUE] sqlstate_value | mysql_erroe_code

• cond_name：条件名称
• cond_type：条件类型，用于定义MySQL的错误，SQLSTATE是长度为5的字符串类型的错误代码；mysql_error_code是数值类型的错误代码。例如ERROR 1142（42000），sql_state_value的值是'42000'，mysql_error_code的值是1142。

2. 示例：

若有错误ERROR 1148(42000)，定义条件如下：

-- 使用SQLSTATE
DECLARE cond_error CONDITION FOR SQLSTATE '42000';
-- 使用mysql_error_code
DECLARE cond_error CONDITION FOR 1148;

二、定义处理程序

1. 语法

DECLARE handler_type HANDLER FOR  condition_value[,...] sp_statement

（1）handler_type：指定错误处理方式

• CONTINUE：遇到错误不处理，继续执行
• EXIT：遇到错误立即退出
• UNDO：遇到错误撤回之前的操作

（2）condition_value：表示错误类型

• SQLSTATE [VALUE] sqlstate_value：包含5个字符的字符串错误值
• mysql_error_code：数值类型的错误代码
• cond_name：定义条件的名称，见第一节
• SQLWARNING：匹配所有以01开头的SQLSTATE错误代码
• NOT FOUND：匹配所有以02开头的SQLSTATE错误代码
• SQL EXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码

（3）sp_statement

程序语句段，表示在遇到定义的错误时执行的存储过程或函数。

2.示例

-- 捕获SQLSTATE
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';
-- 捕获mysql_error_code
DECLARE CONTINUE HANDLER FOR SQLSTATE '1146' SET @info='NO_SUCH_TABLE' ;
-- 捕获定义条件
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR no_such_table SET @info='NO_SUCH_TABLE';
-- 捕获SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING set @info='ERROR';
-- 捕获NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND set @info='NO_SUCH_TABLE';
-- 捕获SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION set @info='ERROR';
-- 捕获所有异常
DECLARE EXIT HANDLER FOR SQLWARNING, NOT FOUND,SQLEXCEPTION BEGIN ... END;

三、存储过程示例

1. 创建存储过程

主要完成删除用户操作。定义出错处理程序，出现错误设置t_error标记为1，默认0，程序继续执行；根据t_error设置msg并返回影响行数

DELIMITER $$
CREATE PROCEDURE proc_del_user(IN uid INT, OUT rowCount INT, OUT msg VARCHAR(200))
BEGIN
	-- 出错标记
	DECLARE t_error INT DEFAULT 0;
	-- 定义出错处理程序
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
	
	-- 删除用户
	DELETE FROM t_user WHERE id = uid;
	
	-- 异常处理
	IF t_error = 0 THEN
		SET msg = 'success';
	ELSE
		SET msg = 'error';
	END IF;
	
	SET rowCount = ROW_COUNT();
	SELECT rowCount, msg;
END $$

2.调用

CALL proc_del_user(1, @rowCount, @msg);
SELECT @rowCount, @msg;