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
