SQLserver 游标使用
--CREATE PROCEDURE PF_ETL_BA_AR_KH (@BeginDate datetime, @IncreaseFlag int, @DataSource nvarchar(40)) --as if object_id('tempdb..#temp_u8_ar_age_kh') is not null drop table #temp_u8_ar_age_kh create table #temp_u8_ar_age_kh( --AccountTime bigint NULL, --CodeID nvarchar(128) NULL, Code nvarchar(64) NULL, --DayCount int NULL, ArMoney decimal(27,8) NULL, --mc decimal(27,8) NULL, CustomerID nvarchar(64) NULL, dbilldate datetime null ) if object_id('tempdb..#temp_u8_ar_kh_mc') is not null drop table #temp_u8_ar_kh_mc create table #temp_u8_ar_kh_mc( Code nvarchar(64) NULL, mc decimal(27,8) NULL, CustomerID nvarchar(64) NULL ) --获取帐套的起始日期 和模块的启用日期 declare @dbname nvarchar(32) , @AccNo nvarchar(32) , @dbStartYear int ,@dbEndYear int , @dbStartDate datetime ,@dbEndDate datetime,@ModelStartDate datetime, @QueryDate datetime,@MonthLastday datetime set @dbname=db_name() SET @AccNo = SUBSTRING(@dbname,8,3) --物理库起始日期 select @dbStartYear=year(GETDATE()) ,@dbEndYear = isnull(iEndYear,2099) from ufsystem..ua_accountdatabase where cDatabase=@dbname select @dbStartDate = min(dBegin), @dbEndDate = max(dEnd), @dbEndYear = max(iYear) from ufsystem..ua_period where iYear >=@dbStartYear and iYear <= @dbEndYear and cAcc_Id=@AccNo set @MonthLastday=CONVERT(varchar(100), DATEADD(Day,-1,CONVERT(char(8),DATEADD(month,1,@MonthLastday),120)+'1'), 23)--最后一天 print @dbStartYear print @dbStartDate print @dbEndDate print @dbEndYear declare @a_ccusid nvarchar(50) declare @a_ccode nvarchar(50) declare @a_dbilldate nvarchar(50) declare @a_md decimal(27,8) declare @a_mc decimal(27,8) insert into #temp_u8_ar_kh_mc(CustomerID,Code,mc) select gl_accvouch.ccus_id,gl_accvouch.ccode,SUM(mc) from gl_accvouch where ccus_id is not null and (iflag=2 or iflag is null) and ibook=1 and not ibook is Null and (cCode like '1122%' or cCode like '1221%') and iYear between 2019 and 2020 and iperiod<=12 --@dbStartYear and (bdelete=0 or (bdelete=1 and left(cast(wllqperiod as nvarchar(6)),4) >2020) ) --@dbStartYear and dbill_date<= '2020-06-30' and ccode in (select ccode from code where iyear=2020) and isnull(mc,0) !=0 --@dbStartYear --and gl_accvouch.ccus_id='200011' and gl_accvouch.ccode='112204' group by gl_accvouch.ccus_id,gl_accvouch.ccode DECLARE Cur_findRef CURSOR --定义游标 FOR select gl_accvouch.ccus_id,gl_accvouch.ccode,gl_accvouch.dbill_date, --datediff(day,gl_accvouch.dbill_date,convert(datetime,'2020-06-30',102)) ts, sum(isnull(md,0)) from gl_accvouch where ccus_id is not null and (iflag=2 or iflag is null) and ibook=1 and not ibook is Null and (cCode like '1122%' or cCode like '1221%') and iYear between 2019 and 2020 and iperiod<=12 and (bdelete=0 or (bdelete=1 and left(cast(wllqperiod as nvarchar(6)),4) >2020) ) and dbill_date<= '2020-06-30' and ccode in (select ccode from code where iyear=2020) and isnull(md,0) !=0 --and gl_accvouch.ccus_id='200011' and gl_accvouch.ccode='112204' group by gl_accvouch.ccus_id,gl_accvouch.ccode,gl_accvouch.dbill_date order by gl_accvouch.ccus_id,gl_accvouch.ccode,gl_accvouch.dbill_date open Cur_findRef --打开游标 fetch next from Cur_findRef into @a_ccusid ,@a_ccode, @a_dbilldate, @a_md --下一条游标数据 WHILE @@FETCH_STATUS =0 BEGIN print 1 -- print @@FETCH_STATUS --select @a_ccusid+'_'+@a_ccode+'_'+@a_dbilldate+'_'+convert(varchar(30),@a_md) select @a_mc=sum(isnull(mc,0)) from #temp_u8_ar_kh_mc where CustomerID = @a_ccusid and Code=@a_ccode print 'qmc:' +convert(varchar(30),@a_mc) print 'qmd:' +convert(varchar(30),@a_md) print 'q'+@a_ccusid+'_'+@a_ccode+'_'+@a_dbilldate+'_'+convert(varchar(30),@a_md) if(@a_mc>=@a_md ) begin print 1 insert into #temp_u8_ar_age_kh(CustomerID,Code,dbilldate,ArMoney) select @a_ccusid,@a_ccode,@a_dbilldate,0 print 'mc:' +convert(varchar(30),@a_mc)+@a_ccusid+@a_ccode print 'md:' +convert(varchar(30),@a_md)+@a_ccusid+@a_ccode update a set a.mc=@a_mc-@a_md from #temp_u8_ar_kh_mc a where CustomerID = @a_ccusid and Code=@a_ccode end else begin print 2 insert into #temp_u8_ar_age_kh(CustomerID,Code,dbilldate,ArMoney) select @a_ccusid,@a_ccode,@a_dbilldate,(@a_mc-@a_md)*-1 print 'mc:' +convert(varchar(30),@a_mc) print 'md:' +convert(varchar(30),@a_md) update a set a.mc=0 from #temp_u8_ar_kh_mc a where CustomerID = @a_ccusid and Code=@a_ccode end fetch next from Cur_findRef into @a_ccusid ,@a_ccode, @a_dbilldate, @a_md--下一条游标数据 end CLOSE Cur_findRef--关闭游标 DEALLOCATE Cur_findRef--释放游标 select * from #temp_u8_ar_age_kh --go