SQLserver 游标使用
时间：2021-07-01 10:21:17
帮助过：9人阅读
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
SQLserver 游标使用
标签：te pro sql group diff flag lld max 最后一天 date
