MySQL 游标遍历每一行数据做处理。

 

delimiter $$  --分隔标记
CREATE  PROCEDURE process_test()
begin
    --声明变量
    declare SuoshuQY_p varchar(255);
    declare done int;
    declare cur cursor for SELECT SuoshuQY as SuoshuQY_p  FROM  diy_cabinet_list WHERE IsDeleted = 0;

    declare continue handler for not found set done = 1;    --游标执行完，即遍历结束。设置done的值为1
    open cur;
    read_loop: Loop
                    --如果done的值为1，即遍历结束，结束循环
                    if done = 1 then
                         leave read_loop;
                     end if;
                Fetch cur into SuoshuQY_p; --从游标中取出SuoshuQY_p
                SELECT SuoshuQY_p; --打印输出结果
        end loop read_loop; --关闭循环
    close cur;
end
end $$  --关闭分隔标记

--Demo
--校验表里 diy_cabinet_list 相同SuoshuQY下的相同PingguiH，需要在CuowuXY 显示'同一区域屏柜号重复'，反之不显示内容
CREATE PROCEDURE process_data()
begin
    declare SuoshuQY_p varchar(255);
    declare done int;
    declare cur cursor for SELECT SuoshuQY as SuoshuQY_p  FROM  diy_cabinet_list WHERE IsDeleted = 0  GROUP BY  SuoshuQY HAVING COUNT(*)>1;
    declare continue handler for not found set done = 1;
    DROP TEMPORARY TABLE if EXISTS SuoshuQY_single;
    CREATE TEMPORARY TABLE SuoshuQY_single
    SELECT SuoshuQY  FROM  diy_cabinet_list WHERE IsDeleted = 0  GROUP BY  SuoshuQY HAVING COUNT(*)=1;
    update diy_cabinet_list SET CuowuXY ='' WHERE SuoshuQY in (select SuoshuQY from SuoshuQY_single);##单个区域更新
    open cur;
    read_loop: Loop
                        if done = 1 then
                         leave read_loop;
                     end if;
                Fetch cur into SuoshuQY_p;
                SELECT SuoshuQY_p;
                DROP TEMPORARY TABLE if EXISTS PingguiHs;
                CREATE TEMPORARY TABLE PingguiHs 
                SELECT PingguiH  FROM  diy_cabinet_list WHERE IsDeleted = 0  GROUP BY  PingguiH ,SuoshuQY HAVING COUNT(PingguiH)>1 AND SuoshuQY  =SuoshuQY_p;
                UPDATE diy_cabinet_list SET CuowuXY ='同一区域屏柜号重复' WHERE PingguiH IN (
                select  PingguiH from PingguiHs) and SuoshuQY = SuoshuQY_p;##多个屏柜号更新
                UPDATE diy_cabinet_list SET CuowuXY ='' WHERE PingguiH not IN (
                select  PingguiH from PingguiHs) and SuoshuQY = SuoshuQY_p;##单个屏柜号更新
        end loop read_loop;
    close cur;
    
end