2. 3 定义处理程序
可以为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';
2. 4 案例解决
在存储过程中，定义处理程序，捕获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)
举例：
创建一个名称为“InsertDataWithCondition”的存储过程，代码如下。
在存储过程中，定义处理程序，捕获sqlstate_value值，当遇到sqlstate_value值为 23000 时，执行EXIT操作，并且将@proc_value的值设置为-1。
#准备工作 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; INSERT INTO departments(department_name) VALUES('测试'); SET @x = 3; END // DELIMITER ;
调用存储过程：
mysql> CALL InsertDataWithCondition(); Query OK, 0 rows affected (0.01 sec) mysql> SELECT @x,@proc_value; +------+-------------+ | @x | @proc_value | +------+-------------+ | 2 | -1 | +------+-------------+ 1 row in set (0.00 sec)
3. 流程控制
解决复杂问题不可能通过一个 SQL 语句完成，我们需要执行多个 SQL 操作。流程控制语句的作用就是控制存储过程中 SQL 语句的执行顺序，是我们完成复杂操作必不可少的一部分。只要是执行的程序，流程就分为三大类：
顺序结构：程序从上往下依次执行
分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行
循环结构：程序满足一定条件下，重复执行一组语句
针对于MySQL 的流程控制语句主要有 3 类。注意：只能用于存储程序。
条件判断语句：IF 语句和 CASE 语句
循环语句：LOOP、WHILE 和 REPEAT 语句
跳转语句：ITERATE 和 LEAVE 语句
3. 1 分支结构之 IF
IF 语句的语法结构是：
IF 表达式1 THEN 操作1 [ELSEIF 表达式2 THEN 操作2]…… [ELSE 操作N] END IF
根据表达式的结果为TRUE或FALSE执行相应的语句。这里“[]”中的内容是可选的。
特点：① 不同的表达式对应不同的操作 ② 使用在begin end中
举例 1 ：
IF val IS NULL THEN SELECT 'val is null'; ELSE SELECT 'val is not null'; END IF;
举例 2 ： 声明存储过程“update_salary_by_eid1”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于 8000 元并且入职时间超过 5 年，就涨薪 500 元；否则就不变。
DELIMITER // CREATE PROCEDURE update_salary_by_eid1(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; END IF; END // DELIMITER ;
举例 3 ： 声明存储过程“update_salary_by_eid2”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于 9000 元并且入职时间超过 5 年，就涨薪 500 元；否则就涨薪 100 元。
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 ;
举例 4 ： 声明存储过程“update_salary_by_eid3”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于 9000 元，就更新薪资为 9000 元；薪资如果大于等于 9000 元且低于 10000 的，但是奖金比例为NULL的，就更新奖金比例为0.01；其他的涨薪 100 元。
DELIMITER // CREATE PROCEDURE update_salary_by_eid3(IN emp_id INT) BEGIN DECLARE emp_salary DOUBLE; DECLARE bonus DECIMAL(3,2); SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id; SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id; IF emp_salary < 9000 THEN UPDATE employees SET salary = 9000 WHERE employee_id = emp_id; 3.2 分支结构之 CASE CASE 语句的语法结构1： CASE 语句的语法结构2： 举例1： 使用CASE流程控制语句的第1种格式，判断val值等于1、等于2，或者两者都不等。 举例2： 使用CASE流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0。 举例3：声明存储过程“update_salary_by_eid4”，定义IN参数emp_id，输入员工编号。判断该员工 薪资如果低于9000元，就更新薪资为9000元；薪资大于等于9000元且低于10000的，但是奖金比例 为NULL的，就更新奖金比例为0.01；其他的涨薪100元。 ELSEIF emp_salary < 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 IF; END // DELIMITER ;
3. 2 分支结构之 CASE
CASE 语句的语法结构 1 ：
#情况一：类似于switch CASE 表达式 WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号) WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号) ... ELSE 结果n或语句n(如果是语句，需要加分号) END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
CASE 语句的语法结构 2 ：
#情况二：类似于多重if CASE WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号) WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号) ... ELSE 结果n或语句n(如果是语句，需要加分号) END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
举例 1 ：
使用CASE流程控制语句的第 1 种格式，判断val值等于 1 、等于 2 ，或者两者都不等。
#情况二：类似于多重if CASE WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号) WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号) ... ELSE 结果n或语句n(如果是语句，需要加分号) END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
举例 2 ：
使用CASE流程控制语句的第 2 种格式，判断val是否为空、小于 0 、大于 0 或者等于 0 。
CASE WHEN val IS NULL THEN SELECT 'val is null'; WHEN val < 0 THEN SELECT 'val is less than 0'; WHEN val > 0 THEN SELECT 'val is greater than 0'; ELSE SELECT 'val is 0'; END CASE;
举例 3 ： 声明存储过程“update_salary_by_eid4”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于 9000 元，就更新薪资为 9000 元；薪资大于等于 9000 元且低于 10000 的，但是奖金比例为NULL的，就更新奖金比例为0.01；其他的涨薪 100 元。
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 ;
举例 4 ：声明存储过程update_salary_by_eid5，定义IN参数emp_id，输入员工编号。判断该员工的入职年限，如果是 0 年，薪资涨 50 ；如果是 1 年，薪资涨 100 ；如果是 2 年，薪资涨 200 ；如果是 3 年，薪资涨 300 ；如果是 4 年，薪资涨 400 ；其他的涨薪 500 。
DELIMITER // CREATE PROCEDURE update_salary_by_eid5(IN emp_id INT) BEGIN DECLARE emp_sal DOUBLE; DECLARE hire_year DOUBLE; SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id; SELECT ROUND(DATEDIFF(CURDATE(),hire_date)/365) INTO hire_year FROM employees WHERE employee_id = emp_id; CASE hire_year WHEN 0 THEN UPDATE employees SET salary=salary+50 WHERE employee_id = emp_id; WHEN 1 THEN UPDATE employees SET salary=salary+100 WHERE employee_id = emp_id; WHEN 2 THEN UPDATE employees SET salary=salary+200 WHERE employee_id = emp_id; WHEN 3 THEN UPDATE employees SET salary=salary+300 WHERE employee_id = emp_id; WHEN 4 THEN UPDATE employees SET salary=salary+400 WHERE employee_id = emp_id; ELSE UPDATE employees SET salary=salary+500 WHERE employee_id = emp_id; END CASE; END // DELIMITER ;
3. 3 循环结构之LOOP
LOOP循环语句用来重复执行某些语句。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句），跳出循环过程。
LOOP语句的基本格式如下：
[loop_label:] LOOP 循环执行的语句 END LOOP [loop_label]
其中，loop_label表示LOOP语句的标注名称，该参数可以省略。
举例 1 ：
使用LOOP语句进行循环操作，id值小于 10 时将重复执行循环过程。
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;
举例 2 ： 当市场环境变好时，公司为了奖励大家，决定给大家涨工资。声明存储过程“update_salary_loop()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家涨薪，薪资涨为原来的1.1倍。直到全公司的平均薪资达到 12000 结束。并统计循环次数。
DELIMITER // CREATE PROCEDURE update_salary_loop(OUT num INT) BEGIN DECLARE avg_salary DOUBLE; DECLARE loop_count INT DEFAULT 0; SELECT AVG(salary) INTO avg_salary FROM employees; label_loop:LOOP IF avg_salary >= 12000 THEN LEAVE label_loop; END IF; UPDATE employees SET salary = salary * 1.1; SET loop_count = loop_count + 1; SELECT AVG(salary) INTO avg_salary FROM employees; END LOOP label_loop; SET num = loop_count; END // DELIMITER ;
