MySQL存储过程如何实现双重for循环遍历结果集？_Mysql

背景

有这么一个需求：对以下的类型结果集进行更新。

更新的原则是type为c的currentvalue的值= (type为b的currentvalue) / ((type为b的currentvalue) + (type为a的currentvalue)) *100。

上面这个需求有很多种实现方法，看到这个需求的时候，我想到的双重for循环：先查询第一个结果集，第一个结果集合里面包含oid字段。

然后对第一个结果集进行遍历，把oid作为参数更新到第二个sql语句中进行更新。

本文是用定义存储过程的方式实现对结果集的遍历，也就是我所希望的双重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 );

这两行代码是定义变量，为什么要定义变量呢，是因为我们查询出的结果集要放到变量中进行二次操作。

这里需要注意的是，变量名的命名规则除了跟普通变量一样以外，还不能够跟结果集中对应的字段名重复。

比如 select id，name from student;这个sql中有两个字段，id和name。那么定义变量的时候就不要再定义id和name了。可以换成idtemp 和nametemp。

另外，变量的类型要和查询结果集的字段类型对应。

declare s int default 0; 这行sql比较特别是定义循环变量s的，下面的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; 声明当游标遍历完后将标志变量置成某个值

（6）. open [游标名]; 这个不用过多解释，上面定义完游标后，这里打开游标。

（7）.fetch [游标名] into [变量名 1 ],[变量名 2 ]; 这段sql是将我们查询的结果集与我们定义的变量进行关联，注意顺序应该一一对应。比如下面这样

fetch stu into idtemp,nametemp;

这里的idtemp就表示上面结果集中的id，nametemp就表示上面结果集中的nametemp

（8）.

while s <> 1 do
....
end while;

这段代码是while循环。

（9）.

[你想操作的sql语句]

这个地方就是内部for循环了，比如我们在这个地方写一个update语句。

update student set score='91' where id=idtemp and name = nametemp;

这个语句就会去寻找上方结果集中的id和name,然后把值代入到idtemp中和nametemp中，进行操作。

（10）.

fetch [游标名] into [变量名 1 ],[变量名 2 ];

这段表示将游标中的值再赋值给变量，供下次循环使用。

当定义完了以后，执行sql。然后就是navicat的函数，找个我们刚才定义的函数进行执行。

完成需求

上面已经说明白了整个过程的含义，下面我们来完成本文的需求。

create procedure processdata()
begin
declare 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;

结果