存储过程补充——定义条件、处理程序及游标使用

---

在 MySQL 数据库的开发过程中，为了提升程序的健壮性、可靠性以及数据处理的灵活性，常常需要面对错误处理和结果集逐行操作的问题。定义条件与处理程序、游标就是 MySQL 提供的强大工具，它们分别用于更精细地控制和处理错误异常情况，以及对查询结果集中的每一行数据进行访问和操作。本文将深入介绍这些重要特性，通过详细的语法说明和丰富的案例演示，帮助大家更好地掌握和运用它们。

---

1. 定义条件与处理程序

在 MySQL 中，定义条件 与 处理程序 主要用于存储过程、函数、触发器等复杂的程序单元里，帮助开发者对错误和异常情况进行更精细的控制和处理，从而提升程序的健壮性和可靠性。

• 定义条件 是事先定义程序执行过程中可能遇到的问题；
• 处理程序 定义了在遇到问题时应当采取的处理方式，保证存储过程或函数在遇到警告或错误时能继续执行。

1.1 定义条件

定义条件是一种命名异常的方式，将一个错误码或错误条件与一个自定义的名称关联起来，让代码更具可读性和可维护性。

当程序执行过程中出现错误时，使用自定义的名称来指代特定的错误，避免在代码里直接使用错误码，使代码更易理解和管理。

• 定义条件使用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。

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

1.2 处理程序

处理程序用于指定当特定错误或异常发生时要执行的操作。在程序运行过程中，一旦出现预定义的错误或异常，就会执行相应的处理程序，从而避免程序崩溃，同时可以根据具体情况进行错误日志记录、回滚事务等操作。

• 定义处理程序时，使用 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';

1.3 案例演示

在存储过程中，定义处理程序，捕获 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)

在存储过程中，定义处理程序，当执行 SELECT * FROM non_existent_table; 语句时，如果表不存在（错误码 1146），就会触发处理程序，记录错误日志，然后继续执行后续语句。

-- 创建错误日志表
CREATE TABLE error_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    error_message VARCHAR(255),
    error_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //

CREATE PROCEDURE test_procedure()
BEGIN
    -- 定义条件
    DECLARE table_not_found CONDITION FOR 1146;

    -- 定义处理程序
    DECLARE CONTINUE HANDLER FOR table_not_found
    BEGIN
        -- 记录错误日志
        INSERT INTO error_log (error_message) VALUES ('Table not found');
        -- 可以在这里添加其他处理逻辑
    END;

    -- 尝试查询一个可能不存在的表
    SELECT * FROM non_existent_table;

    -- 继续执行后续语句
    SELECT 'Procedure continues...';
END //

DELIMITER ;    

2. 游标

游标是指向查询结果集中某一行的指针，通过游标可以在结果集中进行移动，从而对每一行数据进行访问和处理。在 SQL 语句中，查询操作通常会返回一个结果集，而游标提供了一种机制，允许按顺序逐个处理结果集中的行，这在需要对每一行数据进行复杂操作时非常有用。

2.1 使用游标

游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明。

第一步，声明游标

在MySQL中，使用 DECLARE 关键字来声明游标，其语法的基本形式如下：

DECLARE cursor_name CURSOR FOR select_statement;

这个语法适用于 MySQL，SQL Server，DB2 和 MariaDB。如果是用 Oracle 或者 PostgreSQL，需要写成：

DECLARE cursor_name CURSOR IS select_statement;

要使用 SELECT 语句来获取数据结果集，而此时还没有开始遍历数据，这里 select_statement 代表的是 SELECT 语句，返回一个用于创建游标的结果集。比如：

DECLARE cur_emp CURSOR FOR
SELECT employee_id,salary FROM employees;

DECLARE cursor_fruit CURSOR FOR
SELECT f_name, f_price FROM fruits ;

第二步，打开游标

打开游标的语法如下：

OPEN cursor_name;

当我们定义好游标之后，如果想要使用游标，必须先打开游标。打开游标的时候 SELECT 语句的查询结果集就会送到游标工作区，为后面游标的逐条读取结果集中的记录做准备。

OPEN cur_emp ;

第三步，使用游标（从游标中取得数据）

从游标中取得数据语法如下：

FETCH cursor_name INTO var_name [, var_name] ...

这句的作用是使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这个变量中，游标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。

注意：var_name必须在声明游标之前就定义好。

FETCH cur_emp INTO emp_id, emp_sal ;

注意：游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致，否则，在存储过程执行的时候，MySQL 会提示错误。

第四步，关闭游标

关闭游标语法如下：

CLOSE cursor_name;

当使用完游标后需要关闭掉该游标。因为游标会占用系统资源 ，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。而关闭游标的操作，会释放游标占用的系统资源。

关闭游标之后，我们就不能再检索查询结果中的数据行，如果需要检索只能再次打开游标。

CLOSE cur_emp;

2.2 举例

创建存储过程“get_count_by_limit_total_salary()”，声明IN参数 limit_total_salary，DOUBLE类型；声明 OUT参数total_count，INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和 达到limit_total_salary参数的值，返回累加的人数给total_count。

DELIMITER //

CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)
BEGIN
	DECLARE sum_salary DOUBLE DEFAULT 0; #记录累加的总工资
	DECLARE cursor_salary DOUBLE DEFAULT 0; #记录某一个工资值
	DECLARE emp_count INT DEFAULT 0; #记录循环个数
	#定义游标
	DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
	#打开游标
	OPEN emp_cursor;
	
	REPEAT
		#使用游标（从游标中获取数据）
		FETCH emp_cursor INTO cursor_salary;
		
		SET sum_salary = sum_salary + cursor_salary;
		SET emp_count = emp_count + 1;
		
		UNTIL sum_salary >= limit_total_salary
	END REPEAT;
	
	SET total_count = emp_count;
	#关闭游标
	CLOSE emp_cursor;
END //
DELIMITER ;

2.3 小结

游标为逐条读取结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。

但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行加锁 ，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源 ，造成内存不足，这是因为游标是在内存中进行的处理。

建议养成用完之后就关闭的习惯，这样才能提高系统的整体效率。

---

总之，定义条件与处理程序、游标在 MySQL 开发中各有其独特价值。前者让错误处理更精细，增强程序健壮性；后者为结果集逐行处理提供高效简洁方案。但使用游标时要警惕性能问题，及时关闭以提升系统效率。希望大家能借此提升 MySQL 开发能力。

---