定义条件是事先定义程序执行过程中可能遇到的问题,处理程序定义了在遇到问题时应当采取的处理方式,并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力,避免程序异常停止运行。
案例分析
创建一个名称为“UpdateDataNoCondition”的存储过程。代码如下:
DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
SET @x = 1;
UPDATE employees SET email = NULL WHERE last_name = 'Abel';
SET @x = 2;
UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
SET @x = 3;
END //
DELIMITER ;
# 报错:Column 'email' cannot be null
CALL UpdateDataNoCondition();
# 结果为1
SELECT @x;
可以看到,此时@x变量的值为1。结合创建存储过程的SQL语句代码可以得出:在存储过程中未定义条件和处理程序,且当存储过程中执行的SQL语句报错时,MySQL数据库会抛出错误,并退出当前SQL逻辑,不再向下继续执行。
定义条件
定义条件就是给MySQL中的错误码命名,这有助于存储的程序代码更清晰。它将一个错误名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句中。
定义条件使用DECLARE语句,语法格式如下:
DECLARE 错误名称 CONDITION FOR 错误码(或错误条件)
错误码的说明:
MySQL_error_code
和sqlstate_value
都可以表示MySQL的错误。- MySQL_error_code是数值类型错误代码。
- sqlstate_value是长度为5的字符串类型错误代码。
- 例如,在ERROR 1418 (HY000)中,1418是MySQL_error_code,HY000是sqlstate_value。
- 例如,在ERROR 1142(42000)中,1142是MySQL_error_code,42000是sqlstate_value。
```sql
举例1:定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型
是“ERROR 1048 (23000)”对应。
方式1:使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
方式2:使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE ‘23000’;
举例2:定义”ERROR 1148(42000)”错误,名称为command_not_allowed。
方式1:使用MySQL_error_code
DECLARE command_not_allowed CONDITION FOR 1148;
方式2:使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE ‘42000’;
<a name="GG2FS"></a>
# 定义处理程序
可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序。定义处理程序时,使用DECLARE语句的语法如下:
```sql
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句
- 处理方式:处理方式有3个取值:CONTINUE、EXIT、UNDO。
CONTINUE
:表示遇到错误不处理,继续执行。EXIT
:表示遇到错误马上退出。UNDO
:表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。
- 错误类型(即条件)可以有如下取值:
SQLSTATE '字符串错误码'
:表示长度为5的sqlstate_value类型的错误代码;MySQL_error_code
:匹配数值类型错误代码;错误名称
:表示DECLARE … CONDITION定义的错误条件名称。SQLWARNING
:匹配所有以01开头的SQLSTATE错误代码;NOT FOUND
:匹配所有以02开头的SQLSTATE错误代码;SQLEXCEPTION
:匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码;
- 处理语句:如果出现上述条件之一,则采用对应的处理方式,并执行指定的处理语句。语句可以是像“
SET 变量 = 值
”这样的简单语句,也可以是使用BEGIN ... END
编写的复合语句。
定义处理程序的几种方法如下:
#方法1:捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
#方法2:捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
#方法3:先定义条件,再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
#方法4:使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
#方法5:使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
#方法6:使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';
案例
在存储过程中,定义处理程序,捕获sqlstate_value值,当遇到MySQL_error_code值为1048时,执行CONTINUE操作,并且将@proc_value的值设置为-1。
DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
#定义处理程序
DECLARE CONTINUE HANDLER FOR 1048 SET @proc_value = -1;
SET @x = 1;
UPDATE employees SET email = NULL WHERE last_name = 'Abel';
SET @x = 2;
UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
SET @x = 3;
END //
DELIMITER ;
CALL UpdateDataNoCondition();
# 3 -1
SELECT @x,@proc_value;