MySQL存储过程实现双重循环遍历结果集的方法

需求背景与实现思路

在实际开发中，我遇到了一个需要遍历结果集并进行复杂更新的场景：对以下类型的数据集合进行计算更新。

更新规则为：当type为c时，其currentValue = (type为b的currentValue) / ((type为b的currentValue) + (type为a的currentValue)) * 100。

这类需求有多种解法。面对这个场景，我首先联想到双重for循环的思路：先查询第一个结果集（包含oid字段），然后遍历该结果集，将每个oid作为参数代入第二个SQL语句进行更新操作。

本文将采用定义MySQL存储过程的方式来实现对结果集的遍历，也就是通常所说的“双重for循环”模式。

使用的工具：Navicat。

数据准备如下：

DROP TABLE IF EXISTS `report_data`;CREATE TABLE `report_data` (  `id` int(255) NOT NULL,    `oid` int(255) NOT NULL,  `type` varchar(10)  not NULL,  `currentValue` double not NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `report_data` (`id`, `oid`, `type`, `currentValue`) VALUES (1, 1, 'a', 1);INSERT INTO `report_data` (`id`, `oid`, `type`, `currentValue`) VALUES (2, 1, 'b', 2);INSERT INTO `report_data` (`id`, `oid`, `type`, `currentValue`) VALUES (3, 1, 'c', 3);INSERT INTO `report_data` (`id`, `oid`, `type`, `currentValue`) VALUES (4, 1, 'd', 4);INSERT INTO `report_data` (`id`, `oid`, `type`, `currentValue`) VALUES (5, 2, 'a', 5);INSERT INTO `report_data` (`id`, `oid`, `type`, `currentValue`) VALUES (6, 2, 'b', 6);INSERT INTO `report_data` (`id`, `oid`, `type`, `currentValue`) VALUES (7, 2, 'c', 7);INSERT INTO `report_data` (`id`, `oid`, `type`, `currentValue`) VALUES (8, 2, 'd', 8);

如何遍历查询结果集

首先来看一个通用的存储过程模板，有助于后续理解具体实现：

CREATE PROCEDURE [存储过程名称()]BEGIN        DECLARE s int DEFAULT 0;    DECLARE [变量名 1 ] INT DEFAULT 0;    DECLARE [变量名 2 ] VARCHAR ( 255 );    DECLARE [游标名] CURSOR FOR [包含结果集的 SQL ]         DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;    OPEN [游标名];    FETCH [游标名] INTO [变量名 1 ],[变量名 2 ];    WHILE s <> 1 DO        [你想操作的 SQL语句 ]     FETCH [游标名] INTO [变量名 1 ],[变量名 2 ];    END WHILE;CLOSE [游标名];END;

下面逐部分进行详细解释：

（1）CREATE PROCEDURE [存储过程名称()] 表示创建一个存储过程。例如，如果我们将存储过程命名为processdata，则对应语句为 CREATE PROCEDURE processdata()。

（2）BEGIN 和 END 分别标识存储过程体的开始与结束。

（3）

DECLARE[变量名 1 ] INT DEFAULT 0;DECLARE[变量名 2 ] VARCHAR ( 255 );

这两行用于定义变量，目的是将查询结果集中的数据存放至变量中，以便后续进行二次操作。需要特别注意：变量名不能与结果集中的字段名重复，例如结果集中包含id和name，则变量最好命名为idTemp、nameTemp。同时，变量类型必须与字段类型对应。另外，DECLARE s int DEFAULT 0; 是定义循环控制变量，用于后续while循环的判断。

（4）

DECLARE [游标名] CURSOR FOR [包含结果集的 SQL ]

这行用于定义游标，游标中存放我们需要遍历的结果集SQL。例如：

DECLARE stu CURSOR FOR select id,name from student group by id;

这样第一个结果集就定义完成了，接下来就是遍历该结果集。

（5）DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1; 声明当游标遍历完所有数据后，将标志变量s的值设置为1，作为循环结束的条件。

（6）OPEN [游标名]; 打开游标，准备开始遍历。

（7）FETCH [游标名] INTO [变量名 1 ],[变量名 2 ]; 将游标当前指向行的数据依次赋值给对应的变量，顺序必须一一对应。例如：

FETCH stu INTO idTemp,nameTemp;

这里idTemp对应结果集中的id字段，nameTemp对应name字段。

（8）

WHILE s <> 1 DO....END WHILE;

这是while循环体，当s不等于1时持续执行循环内的操作。

（9）[你想操作的sql语句] 就是内部循环需要执行的具体操作，例如编写一个update语句：

update student set score='91' where id=idTemp and name = nameTemp;

该语句会引用上方游标获取到的id和name值，代入条件进行更新。

（10）FETCH [游标名] INTO [变量名 1 ],[变量名 2 ]; 在循环体内再次执行fetch，将游标指针向后移动，以便下一次循环读取下一行数据。

定义完成后，执行存储过程，在Navicat的函数列表中找到刚才定义的函数并执行即可。

实现具体需求

上面的模板已经清晰展示了整个流程，现在直接套用来解决本文开头提出的需求。

CREATE PROCEDURE processData()BEGINDECLARE s int DEFAULT 0;DECLARE oidTemp int DEFAULT 20;DECLARE report CURSOR FOR  SELECT oid from report_data  GROUP BY oid;DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;    open report;    fetch report into oidTemp;    while s<>1 do            SET @fenzi=   (SELECT currentValue from report_data WHERE type='b' and oid =oidTemp);      set @fenmu=   (SELECT currentValue from report_data WHERE type='a' and oid =oidTemp) +                                     (SELECT currentValue from report_data WHERE type='b' and oid =oidTemp);            set  @result =  @fenzi/@fenmu *100;                          update report_data set currentvalue = @result WHERE oid =oidTemp and type='c';        fetch report into  oidTemp;    end while;    close report;END;

执行后的结果如下图所示：

总结

以上就是通过MySQL存储过程实现双重for循环遍历结果集的完整示例。核心思路为：先利用游标获取外层结果集，然后在循环体内根据外层变量执行内层SQL操作。掌握这个通用模板后，类似的数据更新需求都可以快速套用并高效实现。