MySQL存储过程之游标（DECLARE）原理与用法详解

什么是游标

游标（Cursor）是一种针对查询结果集的数据操作方式。它可定位于结果集中的某一行，并对该行执行某种操作。使用游标需要在MySQL存储过程中声明游标类型变量，用fetch命令操作游标获取结果集中的数据。

游标的声明

DECLARE语句用来声明一个游标变量和它的数据类型。声明游标的语法如下：

DECLARE cursor_name CURSOR FOR SELECT_statement;

可以看到，DECLARE语句中需要指定游标名字和SELECT语句。游标名字应该能够反映出这个游标的用途和含义，SELECT语句就是游标要处理的数据集合。

游标的操作

1. 打开游标

通过OPEN语句打开游标，语法如下：

OPEN cursor_name;

游标被打开以后，就可以通过fetch命令从游标中获取数据。

1. 获取数据

FETCH语句用来从游标中获取一条记录。语法如下：

FETCH cursor_name INTO variable_list;

其中，cursor_name是游标的名称，variable_list为一组变量，用来存储从游标中获取到的数据。当数据全部被获取完毕后，FETCH会返回一个NOT FOUND异常。

1. 关闭游标

CLOSE语句被用来显式地关闭游标。语法如下：

CLOSE cursor_name;

示例1：用游标计算平均数

CREATE PROCEDURE `proc_avg`(INOUT avg_num DOUBLE)
BEGIN
    DECLARE cur_cursor CURSOR FOR SELECT num FROM test_table;
    DECLARE cur_num DOUBLE;
    DECLARE total INT DEFAULT 0;
    DECLARE counter INT DEFAULT 0;
    OPEN cur_cursor;
    FETCH cur_cursor INTO cur_num;
    WHILE NOT FOUND DO
        SET total = total + cur_num;
        SET counter = counter + 1;
        FETCH cur_cursor INTO cur_num;
    END WHILE;
    SET avg_num = total / counter;
    CLOSE cur_cursor;
END;

该存储过程接受一个INOUT参数avg_num，用于输出平均数。存储过程中使用CURSOR获取表test_table中的每个数字。通过累加获取的数字和计数器，最终计算出平均数。

示例2：用游标实现指定数值的分组求和

CREATE PROCEDURE `proc_sum`(IN group_num INT, OUT result DOUBLE)
BEGIN
    DECLARE cur_cursor CURSOR FOR SELECT num FROM test_table WHERE group_id = group_num;
    DECLARE cur_num DOUBLE;
    DECLARE total DOUBLE DEFAULT 0;
    OPEN cur_cursor;
    FETCH cur_cursor INTO cur_num;
    WHILE NOT FOUND DO
        SET total = total + cur_num;
        FETCH cur_cursor INTO cur_num;
    END WHILE;
    SET result = total;
    CLOSE cur_cursor;
END;

该存储过程接受一个IN参数group_num和一个OUT参数result。存储过程中使用CURSOR获取表test_table中group_id等于group_num的所有数字。通过累加获取的数字，最终得到这个分组的总和，输出到result中。

总结

游标提供了一种针对结果集的数据操作方式，常用于有大量数据需要处理的情况下。然而，游标的使用需要格外注意数据获取顺序、循环结束条件等，避免数据重复获取或无限循环等问题。