@9 定义条件&处理程序&流程控制

定义条件与处理程序

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

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

案例分析

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 ;

当存储过程中未定义条件和处理程序时，若执行的 SQL 语句出错时，MySQL 数据库会抛出错误、停止之后的 SQL 语句执行（之前的执行结果不撤回）。

为了进行错误处理，MySQL 增加错误条件&处理程序。

定义条件

定义条件就是给 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。

**举例 1：**定义“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 语句的语法如下：

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 ;

调用过程：

mysql> CALL UpdateDataWithCondition();
Query OK, 0 rows affected (0.01 sec)
 
mysql> SELECT @x,@proc_value;
+------+-------------+
| @x   | @proc_value |
+------+-------------+
|    3 |       	 -1  |
+------+-------------+
1 row in set (0.00 sec)

流程控制

针对于 MySQL 的流程控制语句主要有 3 类。注意：只能用于存储程序。

条件判断语句 ：IF 语句和 CASE 语句
循环语句 ：LOOP、WHILE 和 REPEAT 语句
跳转语句 ：ITERATE 和 LEAVE 语句

分支结构之 IF

IF 表达式 1 THEN 操作 1;
[ELSEIF 表达式 2 THEN 操作 2;]
[ELSE 操作 N;]
END IF;

DELIMITER //
CREATE PROCEDURE update_salary_by_eid2(IN emp_id INT)
BEGIN
	DECLARE emp_salary DOUBLE;
	DECLARE hire_year DOUBLE;
 
	SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
 
	SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year
	FROM employees WHERE employee_id = emp_id;
 
	IF emp_salary < 8000 AND hire_year > 5
		THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
	ELSE
		UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
	END IF;
END //
DELIMITER ;

分支结构之 CASE

# 情况一 ：类似于 switch
CASE 表达式
WHEN 值 1 THEN 结果 1 或语句 1(如果是语句，需要加分号)
WHEN 值 2 THEN 结果 2 或语句 2(如果是语句，需要加分号)
...
ELSE 结果 n 或语句 n(如果是语句，需要加分号)
END [case];（如果是放在 begin end 中需要加上 case，如果放在 select 后面不需要）

# 情况二 ：类似于多重 if
CASE
WHEN 条件 1 THEN 结果 1 或语句 1(如果是语句，需要加分号)
WHEN 条件 2 THEN 结果 2 或语句 2(如果是语句，需要加分号)
...
ELSE 结果 n 或语句 n(如果是语句，需要加分号)
END [case];（如果是放在 begin end 中需要加上 case，如果放在 select 后面不需要）

CASE val
　　　WHEN 1 THEN SELECT 'val is 1';
　　　WHEN 2 THEN SELECT 'val is 2';
　　　ELSE SELECT 'val is not 1 or 2';
END CASE;

DELIMITER //
CREATE PROCEDURE update_salary_by_eid4(IN emp_id INT)
BEGIN
	DECLARE emp_sal DOUBLE;
	DECLARE bonus DECIMAL(3,2);
 
	SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
	SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id;
 
	CASE
	WHEN emp_sal<9000 THEN UPDATE employees SET salary=9000 WHERE employee_id = emp_id;
	WHEN emp_sal<10000 AND bonus IS NULL THEN UPDATE employees SET commission_pct=0.01 WHERE employee_id = emp_id;
	ELSE UPDATE employees SET salary=salary+100 WHERE employee_id = emp_id;
	END CASE;
END //
DELIMITER ;

循环结构之 LOOP

[loop_label:] LOOP
	循环执行的语句
END LOOP [loop_label];

DECLARE id INT DEFAULT 0;
add_loop:LOOP
	SET id = id +1;
	IF id >= 10 THEN LEAVE add_loop;
	END IF;
 
END LOOP add_loop;

循环结构之 WHILE

[while_label:] WHILE 循环条件  DO
	循环体
END WHILE [while_label];

循环结构之 REPEAT

[repeat_label:] REPEAT
　　　　循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label];

DELIMITER //
CREATE PROCEDURE test_repeat()
BEGIN
	DECLARE i INT DEFAULT 0;
 
	REPEAT
		SET i = i + 1;
	UNTIL i >= 10
	END REPEAT;
 
	SELECT i;
END //
DELIMITER ;

对比三种循环结构：

这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE 或 ITERATE）则必须添加名称。
LOOP：一般用于实现简单的”死”循环
WHILE：先判断后执行
REPEAT：先执行后判断，无条件至少执行一次

跳转语句之 LEAVE 语句

LEAVE 相当于 break，和 BEGIN ... END 或 循环 一起被使用，需要该结构增加 label 字段。

LEAVE 标记名;

DELIMITER //
 
CREATE PROCEDURE leave_begin(IN num INT)
 
	begin_label: BEGIN
		IF num<=0
			THEN LEAVE begin_label;
		ELSEIF num=1
			THEN SELECT AVG(salary) FROM employees;
		ELSE
			SELECT MAX(salary) FROM employees;
		END IF;
 
		SELECT COUNT(*) FROM employees;
	END //
 
DELIMITER ;

跳转语句之 ITERATE 语句

ITERATE 相当于 continue，和 循环 一起被使用，需要该结构增加 label 字段。

ITERATE label;

DELIMITER //
CREATE PROCEDURE test_iterate()
BEGIN
	DECLARE num INT DEFAULT 0;
 
	my_loop:LOOP
		SET num = num + 1;
 
		IF num < 10
			THEN ITERATE my_loop;
		ELSEIF num > 15
			THEN LEAVE my_loop;
		END IF;
 
	END LOOP my_loop;
END //
DELIMITER ;

🪴 Obsidian Publish

探索