SQLserver 游标使用
特价活动:>>>> 畅云管家新购、续费8折优惠，畅捷通T+cloud、好会计、易代账、好业财、好生意云产品8折优惠。
--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
