MySQL 条件处理程序（Handler）

条件处理程序

条件处理程序可以用来定义在流程控制结构中遇到问题时对应的处理步骤，语法为：

DECLARE handler_action HANDLER FOR condition_value[,condition_value]... statement;

#handler action
		continue:继续执行当前程序
		exit：终止执行当前程序
		
#condition_value 	
		SQLSTATE sqlstate_value:状态码，如02000
		SQLWARNING :所有以01开头的SQLSTATE代码的简写
		NOT FOUND:所有以02开头的SQLSTATE代码的简写
		SQLEXCEPTION:所有没有被SQLWARNING 或NOT FOUND捕获的SQLSTATE 代码的简写

具体的SQLSTATE参考MySQL官方文档

案例：解决上一篇bug

DELIMITER $

CREATE PROCEDURE myp1(IN usalary DOUBLE)
BEGIN
	DECLARE empname VARCHAR(50);
	DECLARE empdid INT;
	DECLARE u_cursor CURSOR FOR 
	SELECT CONCAT(last_name,' ',first_name),department_id 
	FROM employees 
	WHERE salary<=usalary;
	#定义条件处理程序，当满足条件，就退出并关闭游标
	DECLARE EXIT HANDLER FOR NOT FOUND CLOSE u_cursor;
	#或DECLARE EXIT HANDLER FOR SQLSTATE '02000' CLOSE u_cursor;

	DROP TABLE IF EXISTS emp_under_sal;
	CREATE TABLE IF NOT EXISTS emp_under_sal(
		id INT PRIMARY KEY AUTO_INCREMENT,
		NAME VARCHAR(50),
		department_id INT
		);
	
	OPEN u_cursor;
	WHILE TRUE DO 
		FETCH u_cursor INTO empname,empdid;
		INSERT INTO emp_under_sal VALUES(NULL,empname,empdid);
	END WHILE;
	CLOSE u_cursor;
END$

CALL myp1(5000);