MSSQL 游标使用实例

DECLARE  MyCURSOR  CURSOR
FOR
select  iGlideNum,sAccFlag,a.sDeptNo,sAccMan,ENT_Mobil,dAccDate,dSFinishDate  from  EG_SHL_ACCEPT  as  a,EG_SYS_ENTRY  as  e  where  sAccMan = ENT_Name  and  sAccFlag  like   ' ____x[3,4,5] '
OPEN  MyCURSOR
declare   @iGlideNum   varchar ( 20 )
declare   @sAccFlag   varchar ( 6 )
declare   @sDeptNo   varchar ( 20 )
declare   @sAccMan   varchar ( 10 )
declare   @ENT_Mobil   varchar ( 20 )
declare   @dAccDate   DateTime
declare   @dSFinishDate   DateTime
fetch   next   from  MyCURSOR  into   @iGlideNum , @sAccFlag , @sDeptNo , @sAccMan , @ENT_Mobil , @dAccDate , @dSFinishDate
while ( @@fetch_status = 0 )
   begin
     declare   @iOverTime   int
     declare   @NowDate   DateTime
     select   top   1   @iOverTime = iOverTime  from  EG_SYS_PARAM
     select   @NowDate = getdate ();
     if   cast ( convert ( varchar ( 10 ), @NowDate , 120 )  as   DateTime ) >   @dSFinishDate   and   cast ( convert ( varchar ( 10 ), @dSFinishDate , 120 )  as   DateTime ) <  ( cast ( convert ( varchar ( 10 ), @NowDate , 120 )  as   DateTime ) + @iOverTime )
     begin
         if   @dSFinishDate + @iOverTime < cast ( convert ( varchar ( 10 ), @NowDate , 120 )  as   DateTime )
         begin
             insert   into  EG_DB_WARNINGINFO(iGlideNum,sWarnNode,sWarnType,sWarnText,sWarnMan,sWarnObject,sWarnCategory,sDeptNo,sWarnWay)  values ( @iGlideNum , @sAccFlag , 1 , ' 你受理的项目( ' + @iGlideNum + ' )结办日期为 ' + convert ( varchar ( 10 ), @dSFinishDate , 120 ) + ' ，现已超过超期警告办理时间，给予一张红牌警告，请尽快办理。 ' , ' 自动 ' , @sAccMan , 0 , @sDeptNo , 0 )
             insert   into  EG_DB_WARNINGBACK(sWarnText,sWarnMolOrMail,sWarnTpyes)  values ( ' 你受理的项目( ' + @iGlideNum + ' )结办日期为 ' + convert ( varchar ( 10 ), @dSFinishDate , 120 ) + ' ，现已超过超期警告办理时间，给予一张红牌警告，请尽快办理。 ' , @ENT_Mobil , 0 )
         end   else
         begin
             insert   into  EG_DB_WARNINGINFO(iGlideNum,sWarnNode,sWarnType,sWarnText,sWarnMan,sWarnObject,sWarnCategory,sDeptNo,sWarnWay)  values ( @iGlideNum , @sAccFlag , 0 , ' 你受理的项目( ' + @iGlideNum + ' )结办日期为 ' + convert ( varchar ( 10 ), @dSFinishDate , 120 ) + ' ，现已超期，给予一张黄牌警告，请尽快办理。 ' , ' 自动 ' , @sAccMan , 0 , @sDeptNo , 0 )
             insert   into  EG_DB_WARNINGBACK(sWarnText,sWarnMolOrMail,sWarnTpyes)  values ( ' 你受理的项目( ' + @iGlideNum + ' )结办日期为 ' + convert ( varchar ( 10 ), @dSFinishDate , 120 ) + ' ，现已超期，给予一张黄牌警告，请尽快办理。 ' , @ENT_Mobil , 0 )
         end     
     end
         fetch   next   from  MyCURSOR  into   @iGlideNum , @sAccFlag , @sDeptNo , @sAccMan , @ENT_Mobil , @dAccDate , @dSFinishDate
   end
close  MyCURSOR
deallocate  MyCURSOR