很多时候我们都会面临每天执行一个批量SQL,比如更新用户表中的某一列数据,本文介绍如何使用游标来更新这些数据,并且每行数据的更新都会带有在日志表中插入数据的功能
DECLARE @Id int,@CurMoney int,@ConMoney int,@Interest int DECLARE UpdateMember_Cursor CURSOR --定义游标FOR (SELECT Id,CurMoney,ConMoney,ConMoney*(select Convert(decimal(9,3), (select Interest from SiteConfig))) as Interest FROM Member where ConMoney>0) --查出需要的集合放到游标中OPEN UpdateMember_Cursor; --打开游标FETCH NEXT FROM UpdateMember_Cursor INTO @Id,@CurMoney,@ConMoney,@Interest; --读取第一行数据WHILE @@FETCH_STATUS = 0 BEGINPrint @Interestupdate Member set CurMoney=CurMoney+@Interest where CURRENT OF UpdateMember_Cursor;insert into Rec (RecType,DataTime,Num,Remark,[Action],Users) values (5,GETDATE(),@Interest,'每日+'+Convert(nvarchar(100),@Interest),'每日+'+Convert(nvarchar(100),@Interest),@Id) FETCH NEXT FROM UpdateMember_Cursor INTO @Id,@CurMoney,@ConMoney,@Interest;--读取下一行数据 ENDCLOSE UpdateMember_Cursor; --关闭游标DEALLOCATE UpdateMember_Cursor; --释放游标GO
这样我们就创建了一个游标,并且拿出相应的数据在游标中遍历拿出的数据,这样就可以猥琐欲为了