BEGIN
-- 定义变量
DECLARE strModifier varchar(255);
DECLARE strModifyIdCard varchar(255);
DECLARE strModifyPhoneNum varchar(255);
DECLARE strModifyUnit varchar(256);
DECLARE strModifyIdentity varchar(256);
DECLARE done INT DEFAULT 0;
-- 定义游标,并将sql结果集赋值到游标中(取出visit_daily内容为空的人员ID)
DECLARE report CURSOR FOR
select b.STR_GUID,b.STR_CARD_NO,b.STR_ACCOUNT, r.strUnitName,r.strType
from base_user b inner join rel_cap_unit r on b.STR_CARD_NO=r.strCapIdNum
where STR_GUID in (select v.strModifier from visit_daily v
where v.intVistSource=1 and v.strActionType='thunder2' and v.strModifyIdCard='');
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
-- 打开游标
open report;
-- 执行循环
posLoop:LOOP
-- 判断是否结束循环
IF done=1 THEN
LEAVE posLoop;
END IF;
-- 将游标中的值赋值给变量,注意:变量名不要和返回的列名同名,变量顺序要和sql结果列的顺序一致
fetch report into strModifier,strModifyIdCard,strModifyPhoneNum,strModifyUnit,strModifyIdentity;
set @y=strModifier;
select @y;
-- 执行业务逻辑(根据strModifier更改visit_daily表)
UPDATE visit_daily info set info.strModifyIdCard = strModifyIdCard, info.strModifyPhoneNum = strModifyPhoneNum,
info.strModifyUnit = strModifyUnit, info.strModifyIdentity = strModifyIdentity
where info.strModifier = strModifier;
END LOOP posLoop;
-- 关闭游标
close report;
END