定义条件是事先定义程序执行过程中可能遇到的问题,处理程序定义了在遇到问题时应当采取的处理方式,并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力,避免程序异常停止运行。

说明:定义条件和处理程序在存储过程、存储函数中都是支持的。

案例分析

创建一个名称为“UpdateDataNoCondition”的存储过程。代码如下:

  1. DELIMITER //
  2. CREATE PROCEDURE UpdateDataNoCondition()
  3. BEGIN
  4. SET @x = 1;
  5. UPDATE employees SET email = NULL WHERE last_name = 'Abel';
  6. SET @x = 2;
  7. UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
  8. SET @x = 3;
  9. END //
  10. DELIMITER ;
  11. # 报错:Column 'email' cannot be null
  12. CALL UpdateDataNoCondition();
  13. # 结果为1
  14. SELECT @x;

可以看到,此时@x变量的值为1。结合创建存储过程的SQL语句代码可以得出:在存储过程中未定义条件和处理程序,且当存储过程中执行的SQL语句报错时,MySQL数据库会抛出错误,并退出当前SQL逻辑,不再向下继续执行。

定义条件

定义条件就是给MySQL中的错误码命名,这有助于存储的程序代码更清晰。它将一个错误名字指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句中。
定义条件使用DECLARE语句,语法格式如下:

  1. DECLARE 错误名称 CONDITION FOR 错误码(或错误条件)

错误码的说明:

  • MySQL_error_codesqlstate_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’;

  1. <a name="GG2FS"></a>
  2. # 定义处理程序
  3. 可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序。定义处理程序时,使用DECLARE语句的语法如下:
  4. ```sql
  5. 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. #方法1:捕获sqlstate_value
  2. DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
  3. #方法2:捕获mysql_error_value
  4. DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
  5. #方法3:先定义条件,再调用
  6. DECLARE no_such_table CONDITION FOR 1146;
  7. DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
  8. #方法4:使用SQLWARNING
  9. DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
  10. #方法5:使用NOT FOUND
  11. DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
  12. #方法6:使用SQLEXCEPTION
  13. DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

案例

在存储过程中,定义处理程序,捕获sqlstate_value值,当遇到MySQL_error_code值为1048时,执行CONTINUE操作,并且将@proc_value的值设置为-1。

  1. DELIMITER //
  2. CREATE PROCEDURE UpdateDataNoCondition()
  3. BEGIN
  4. #定义处理程序
  5. DECLARE CONTINUE HANDLER FOR 1048 SET @proc_value = -1;
  6. SET @x = 1;
  7. UPDATE employees SET email = NULL WHERE last_name = 'Abel';
  8. SET @x = 2;
  9. UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
  10. SET @x = 3;
  11. END //
  12. DELIMITER ;
  13. CALL UpdateDataNoCondition();
  14. # 3 -1
  15. SELECT @x,@proc_value;