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

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

2.1 案例分析

案例分析：创建一个名称为“UpdateDataNoCondition”的存储过程。代码如下：

  1. DELIMITER //
  3. CREATE PROCEDURE UpdateDataNoCondition()
  4.     BEGIN
  5.         SET @x = 1;
  6.         UPDATE employees SET email = NULL WHERE last_name = 'Abel';
  7.         SET @x = 2;
  8.         UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
  9.         SET @x = 3;
  10.     END //
  12. DELIMITER ;

调用存储过程：

  1. mysql> CALL UpdateDataNoCondition();
  2. ERROR 1048 (23000): Column 'email' cannot be null
  4. mysql> SELECT @x;
  5. +------+
  6. | @x   |
  7. +------+
  8. |   1  |
  9. +------+
  10. 1 row in set (0.00 sec)

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

2.2 定义条件

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

定义条件使用DECLARE语句，语法格式如下：

  1. 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。

举例1：定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型是“ERROR 1048 (23000)”对应。

  1. #使用MySQL_error_code
  2. DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
  4. #使用sqlstate_value
  5. DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

举例2：定义”ERROR 1148(42000)”错误，名称为command_not_allowed。

  1. #使用MySQL_error_code
  2. DECLARE command_not_allowed CONDITION FOR 1148;
  4. #使用sqlstate_value
  5. DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';

2.3 定义处理程序

可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序。定义处理程序时，使用DECLARE语句的语法如下：

  1. 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';
  4. #方法2：捕获mysql_error_value
  5. DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
  7. #方法3：先定义条件，再调用
  8. DECLARE no_such_table CONDITION FOR 1146;
  9. DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
  11. #方法4：使用SQLWARNING
  12. DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
  14. #方法5：使用NOT FOUND
  15. DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
  17. #方法6：使用SQLEXCEPTION
  18. DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

2.4 案例解决

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

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

调用过程：

  1. mysql> CALL UpdateDataWithCondition();
  2. Query OK, 0 rows affected (0.01 sec)
  4. mysql> SELECT @x,@proc_value;
  5. +------+-------------+
  6. | @x   | @proc_value |
  7. +------+-------------+
  8. |    3 |            -1  |
  9. +------+-------------+
  10. 1 row in set (0.00 sec)

举例：

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

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

  1. #准备工作
  2. CREATE TABLE departments
  3. AS
  4. SELECT * FROM atguigudb.`departments`;
  6. ALTER TABLE departments
  7. ADD CONSTRAINT uk_dept_name UNIQUE(department_id);
  1. DELIMITER //
  3. CREATE PROCEDURE InsertDataWithCondition()
  4.     BEGIN
  5.         DECLARE duplicate_entry CONDITION FOR SQLSTATE '23000' ;
  6.         DECLARE EXIT HANDLER FOR duplicate_entry SET @proc_value = -1;
  8.         SET @x = 1;
  9.         INSERT INTO departments(department_name) VALUES('测试');
  10.         SET @x = 2;
  11.         INSERT INTO departments(department_name) VALUES('测试');
  12.         SET @x = 3;
  13.     END //
  15. DELIMITER ;

调用存储过程：

  1. mysql> CALL InsertDataWithCondition();
  2. Query OK, 0 rows affected (0.01 sec)
  4. mysql> SELECT @x,@proc_value;
  5. +------+-------------+
  6. | @x   | @proc_value |
  7. +------+-------------+
  8. |    2 |            -1  |
  9. +------+-------------+
  10. 1 row in set (0.00 sec)