MySQL-定义条件和处理程序

定义条件

1. 几个概念

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

2. 定义条件语法

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

DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）

3. 错误码

• MySQL_error_code是数值类型错误代码。
• sqlstate_value是长度为5的字符串类型错误代码
• 例如，在ERROR 1418 (HY000)中，1418是MySQL_error_code，'HY000'是sqlstate_value

4. 示例

• 定义“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';

定义处理程序

# 为SQL执行过程中发生的某种类型的错误定义特殊的处理程序
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句

1. 处理方式 的取值

• CONTINUE ：表示遇到错误不处理，继续执行。
• EXIT ：表示遇到错误马上退出。
• UNDO ：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作

2. 错误类型（即条件）可以有如下取值

• SQLSTATE '字符串错误码' ：表示长度为5的sqlstate_value类型的错误代码；
• MySQL_error_code ：匹配数值类型错误代码；
• 错误名称 ：表示DECLARE ... CONDITION定义的错误条件名称。
• SQLWARNING ：匹配所有以01开头的SQLSTATE错误代码；
• NOT FOUND ：匹配所有以02开头的SQLSTATE错误代码；
• SQLEXCEPTION ：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码

3. 处理语句

• 如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句
• 语句可以是像“ 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';

4. 完整示例

CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;
# 增加唯一约束
ALTER TABLE departments
ADD CONSTRAINT uk_dept_name UNIQUE(department_id);

DELIMITER //

CREATE PROCEDURE InsertDataWithCondition()
BEGIN
    # 定义条件
    DECLARE duplicate_entry CONDITION FOR SQLSTATE '23000' ;
    # 定义处理程序
    DECLARE EXIT HANDLER FOR duplicate_entry SET @proc_value = -1;

    SET @x = 1;
    INSERT INTO departments(department_name) VALUES('测试');
    SET @x = 2;
    # 违反唯一约束，报错，执行定义的EXIT处理程序 
    INSERT INTO departments(department_name) VALUES('测试');
    SET @x = 3;
END //

DELIMITER ;

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