游标使用具体案例

 

drop procedure if exists workingday_insert ;

#创建存储过程
create procedure workingday_insert() 
begin 
declare num int default(0);
declare centguid_sp varchar(100);

#申明游标，类似集合（查询结果放在游标中）
declare centguid_list cursor for
select
    rowguid
from
    AUDIT_ORGA_SERVICECENTER;

#设置遍历结束的标志
declare continue HANDLER for not found set
num = 1;

#打开集合
open centguid_list;

#遍历集合
FETCH centguid_list INTO centguid_sp;
while num <> 1 DO

#业务逻辑
#insert into audit_orga_workingday (RowGuid,ISWORKINGDAY,centerguid,ISWEEKEND,WDATE,DATEGUID) values  (newid(),1,centguid_sp,0,'2021-12-31',newid())

#记录遍历过的数据，否则会死循环
fetch centguid_list into centguid_sp;
end while;
close centguid_list;
end;