mysql基础（十六）：定义条件、定义处理程序

• 简介

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

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

• 前置案例

# 定义1个存储过程
DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition()
    BEGIN
        SET @x = 1;
        UPDATE employees SET email = NULL WHERE last_name = 'Abel';    # 执行到这一步时，会报错，因为不能将email设置为null
        SET @x = 2;
        UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
        SET @x = 3;
    END //
DELIMITER;

# 调用存储过程
CALL UpdateDataNoCondition();

# 报错如下
ERROR 1048 (23000): Column 'email' cannot be null

• 定义条件

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

# 格式
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

• 定义条件代码案例

# 定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型是“ERROR 1048 (23000)”对应
#使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
#使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

# 定义"ERROR 1148(42000)"错误，名称为command_not_allowed
#使用MySQL_error_code
DECLARE command_not_allowed CONDITION FOR 1148;
#使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';

• 定义处理程序

可以为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';

• 完整代码案例

# 案例1
DELIMITER //    # 开始
CREATE PROCEDURE UpdateDataNoCondition()    # 定义存储过程
  BEGIN
    # 声明处理程序
    # 处理方式1：
    DECLARE CONTINUE HANDLER FOR 1048 SET @prc_value = -1;
    # 处理方式2：
    # DECLARE CONTINUE HANDLER FOR sqlstate '23000' SET @prc_value = -1;
    
    # 具体执行的sql
    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();

#查看变量：
SELECT @x,@prc_value;

# 案例2
# 创建一个名称为“InsertDataWithCondition”的存储过程
# ① 前期准备，创建表
CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;
# 设置约束
ALTER TABLE departments
ADD CONSTRAINT uk_dept_name UNIQUE(department_id);

# ② 定义存储过程：
DELIMITER //
CREATE PROCEDURE InsertDataWithCondition()    # 创建存储过程
  BEGIN   
    SET @x = 1;
    INSERT INTO departments(department_name) VALUES('测试');
    SET @x = 2;
    INSERT INTO departments(department_name) VALUES('测试');
    SET @x = 3;
  END //
DELIMITER ;

#③ 调用
CALL InsertDataWithCondition();
# 查看
SELECT @x;  

# 案例3：考虑到错误的处理程序
DELIMITER //
CREATE PROCEDURE InsertDataWithCondition()
  BEGIN   
    
    # 定义条件
    # 方式1：
    # declare exit handler for 1062 set @pro_value = -1;
    # 方式2：
    # declare exit handler for sqlstate '23000' set @pro_value = -1;
    # 方式3：
    # 定义处理程序
    DECLARE duplicate_entry CONDITION FOR 1062;
    DECLARE EXIT HANDLER FOR duplicate_entry SET @pro_value = -1;
    
    # 具体执行的sql
    SET @x = 1;   # 赋值
    INSERT INTO departments(department_name) VALUES('测试');
    SET @x = 2;
    INSERT INTO departments(department_name) VALUES('测试');
    SET @x = 3;
  END //
DELIMITER;

#调用
CALL InsertDataWithCondition();

# 查看
SELECT @x,@pro_value;