create or replace package PKG_NCSHBZ is
type C_cursor is ref cursor;
procedure NCSHBZ(areaCode varchar2,
SLBCODE varchar2,
iyear varchar2,
imonth varchar2,
ilevel varchar2,
c_result out C_cursor);
end PKG_NCSHBZ;
create or replace package body PKG_NCSHBZ is
procedure NCSHBZ(areaCode varchar2,
SLBCODE varchar2,
iyear varchar2,
imonth varchar2,
ilevel varchar2,
c_result out C_cursor) is
ConnectParam varchar2(50);
NY varchar2(6);
levelCode varchar2(20);
StandarLevelCode varchar2(20);
sqltemp varchar2(3000);
sqlstr0 varchar2(3000);
sqlstr1 varchar2(3000);
strstartdate varchar2(50);
strenddate varchar2(50);
sql1 varchar2(1000);
day1 varchar2(2);
tempareacode varchar2(50);
isExist integer;
begin
-----------------起始日期 结束日期-----------------
tempareacode :=areaCode;
if imonth='2' then day1:='28';
elsif imonth='4' then day1:='30';
elsif imonth='6' then day1:='30';
elsif imonth='9' then day1:='30';
elsif imonth='11' then day1:='30';
else day1:=31;
end if;
strstartdate:=iyear||'-'||imonth||'-'||'1 00:00:00';
strenddate:=iyear||'-'||imonth||'-'||day1||' 23:59:59';
--???????
NY := to_char(iyear);
if imonth < 10 then
NY := NY || '0' || to_char(imonth);
else
NY := NY || to_char(imonth);
end if;
if (ilevel = 1) then
--???
begin
levelCode := 'sShiCode';
StandarLevelCode := 'sShiCode';
ConnectParam := 'p.sID';
tempareacode :=substr(areaCode,1,2);
end;
elsif (ilevel = 2) then
begin
levelCode := 'sAreaCode';
StandarLevelCode := 'sAreaCode';
ConnectParam := 'p.sID';
tempareacode :=substr(areaCode,1,4);
end;
elsif (ilevel = 3) then
begin
levelCode := 'sTownCode';
StandarLevelCode := 'sAreaCode';
ConnectParam := 'substr(p.sID,1,6)';
end;
elsif (ilevel = 4) then
begin
levelCode := 'sVillageCode';
StandarLevelCode := 'sAreaCode';
ConnectParam := 'substr(p.sID,1,6)';
end;
end if;
select count(1) into isExist from user_tables where TABLE_NAME='TEMP_NCSHBZ_TABLE';
if(isExist>=1)then
begin
execute immediate 'truncate table TEMP_NCSHBZ_TABLE';
end;
else
begin
sqltemp := 'create global temporary table TEMP_NCSHBZ_TABLE(
sShengCode varchar2(50),
sShengName varchar2(50),
sShiCode varchar2(50),
sShiName varchar2(50),
sAreaCode varchar2(50),
sAreaName varchar2(50),
sTownCode varchar2(50),
sTownName varchar2(50),
sVillageCode varchar2(50),
sVillageName varchar2(50),
SID varchar2(50),
SNAME varchar2(50),
DBRS integer DEFAULT 0,
DBJTS integer DEFAULT 0,
NVXRS integer DEFAULT 0,
CJRS integer DEFAULT 0,
ZDCJRS integer DEFAULT 0,
LNRS integer DEFAULT 0,
YLDTJRS integer DEFAULT 0,
WLDTJRS integer DEFAULT 0,
WCNRS integer DEFAULT 0,
NRFPRS integer DEFAULT 0,
XZRS integer DEFAULT 0,
TCRS integer DEFAULT 0,
LJZC number(12,4) DEFAULT 0.00,
DBJ number(12,4) DEFAULT 0.00,
LSJZJ number(12,4) DEFAULT 0.00,
DBBZ number(12,4) DEFAULT 0.00
) on commit preserve rows';
execute immediate sqltemp;
end;
end if;
------------------------------------临时表插入基本数据-------------------------
sqltemp := 'insert into TEMP_NCSHBZ_TABLE
(sShengCode,sShengName,sShiCode,sShiName,sAreaCode,sAreaName,sTownCode,sTownName,
sVillageCode,sVillageName,sID,sName) SELECT sShengCode,sShengName,sShiCode,sShiName,
sAreaCode,sAreaName,sTownCode,sTownName,sVillageCode,sVillageName,sID,sName FROM sysArea
WHERE sSuperID = '||areacode||'';
dbms_output.put_line(sqltemp);
execute immediate sqltemp;
---------------------------------------------插入当月新增人数和当月退出低保人数-------------------------------
sqlstr0 := 'merge into TEMP_NCSHBZ_TABLE p using (
SELECT d11.' || levelCode ||',
sum(chhis.h_in_people) XZRS,sum(chhis.h_de_people) TCRS from d11_ledger_family d11 inner join
db_changehistory chhis on d11.gfamilyid=chhis.gfamilyid
WHERE d11.ideletemark=0 and chhis.ideletemark=0 and
chhis.dcreatedate>=to_date('''||strstartdate||''','||'''yyyy-MM-dd hh24:mi:ss'') and
chhis.dcreatedate<=to_date('''||strenddate||''','||'''yyyy-MM-dd hh24:mi:ss'') and
instr(chhis.slbcode,'''||SLBCODE||''')>0
and instr(chhis.sareacode,'''||tempareacode||''')>0
group by d11.' || levelCode || '
) a on (a.' || levelCode ||
' = p.sID)
when matched then update set p.XZRS=a.XZRS,p.TCRS=a.TCRS';
dbms_output.put_line(sqlstr0);
execute immediate sqlstr0;
------------------------------------------------------向临时表字段插入具体值---------------------
sqlstr1 := 'merge into TEMP_NCSHBZ_TABLE p using (
SELECT d11.' || levelCode ||',
sum(nvl(d11.issbzrks,0)) DBRS,COUNT(*) DBJTS,
sum(case d2.ssex when ''2'' then 1 else 0 end ) NVXRS,
sum(case nvl(d2.scjlbcode,0) when ''0'' then 0 else 1 end ) CJRS,
sum(case when (nvl(d2.scjdjcode,0)=''01'' or nvl(d2.scjdjcode,0)=''02'') then 1 else 0 end ) ZDCJRS,
sum(case nvl(d2.slylbcode,0) when ''1'' then 1 else 0 end ) LNRS,
sum(case nvl(d2.sWorkAbleCode,0) when ''01'' then 1 else 0 end ) YLDTJRS,
sum(case nvl(d2.sWorkAbleCode,0) when ''04'' then 1 else 0 end ) WLDTJRS,
sum(case nvl(d2.sWorkAbleCode,0) when ''3'' then 1 else 0 end ) WCNRS,
sum(case nvl(d2.iSFSX,0) when 1 then 1 else 0 end ) NRFPRS,
sum(nvl(d11.fffje,0)) DBJ
FROM D11_Ledger_Family d11 left join d2_peopleinfo d2 on
d11.gfamilyid=d2.gfamliyid
WHERE d11.ideletemark=0 and d2.ideletemark=0 and d11.iNF=''' ||
iyear || ''' and d11.iYF=''' || imonth || '''
and d11.sJTLBCode=''' || SLBCODE ||
''' group by d11.' || levelCode || '
) a on (a.' || levelCode ||
' = p.sID)
when matched then update set p.DBRS=a.DBRS,p.DBJTS=a.DBJTS,p.NVXRS=a.NVXRS,p.CJRS=a.CJRS,
p.ZDCJRS = a.ZDCJRS,p.LNRS=a.LNRS,p.YLDTJRS=a.YLDTJRS,p.WLDTJRS=a.WLDTJRS,p.WCNRS=a.WCNRS,
p.NRFPRS=a.NRFPRS,p.DBJ=a.DBJ';
dbms_output.put_line(sqlstr1);
execute immediate sqlstr1;
--------------------------------更新标准---------------------------------------------
sqlstr1:='merge into TEMP_NCSHBZ_TABLE p using (
select t1.f_standard_money DBBZ,t2.sareacode from T2_RELIEF_BUSINESS t2 left join T1_RELIEF_STANDARD t1 on t2.gID=t1.gbusiness_oid
where t1.s_standard_type_code='''||SLBCODE||''' and t1.iDeleteMark=0 and t2.iDeleteMark=0
) a on (a.sareacode= p.sareacode)
when matched then update set p.DBBZ=a.DBBZ';
dbms_output.put_line(sqlstr1);
execute immediate sqlstr1;
-------------------------------------------------------------------------------
sqltemp := 'select * from TEMP_NCSHBZ_TABLE order by sid ';
open c_result For sqltemp;
EXCEPTION
WHEN others THEN
begin
-- rollback;
dbms_output.put_line(sqlerrm);
return;
end;
end NCSHBZ;
end PKG_NCSHBZ;
--------------------非游标的存储过程
create or replace package PKG_IMPLEMENTSTATUS is
type C_cursor is ref cursor;
procedure IMPLEMENTSTATUS( areacode varchar2,
slbcode varchar2,
iyear varchar2,
imonth varchar2,
ilevel varchar2,
c_result out varchar2);
end PKG_IMPLEMENTSTATUS;
create or replace package body PKG_IMPLEMENTSTATUS is
procedure IMPLEMENTSTATUS(areacode varchar2,
slbcode varchar2,
iyear varchar2,
imonth varchar2,
ilevel varchar2,
c_result out varchar2) is
levelSearchName varchar2(20);
UPlevelSearchName varchar2(20);
SearchName varchar2(50);
nsql varchar2(4000);
isExist integer;
sqltemp varchar2(4000);
strSQL varchar2(4000);
ijb varchar2(5);
sqlstr3 varchar2(4000);
IsExists integer;
begin
select count(*) into isExist from RPT_IMPLEMENTSTATUS_TEMP where SLBCODE='80' and inf=iyear and iyf=imonth;
if(isExist>=1)then
begin
execute immediate 'delete from RPT_IMPLEMENTSTATUS_TEMP where SLBCODE=''80'' and inf='||iyear||' and
iyf='||imonth||'';
end;
end if;
if (ilevel = 1) then
begin
UPlevelSearchName := 'sShengCode';
end;
elsif (ilevel = 2) then
begin
UPlevelSearchName := 'sShiCode';
end;
elsif (ilevel = 3) then
begin
UPlevelSearchName := 'sAreaCode';
end;
elsif (ilevel = 4) then
begin
UPlevelSearchName := 'sTownCode';
end;
end if;
sqltemp := 'INSERT INTO RPT_IMPLEMENTSTATUS_TEMP( gid,SLBCODE,inf,iyf,sShengCode ,sShengName ,sShiCode ,sShiName ,sAreaCode ,sAreaName ,sTownCode ,sTownName ,sVillageCode ,sVillageName ,sID ,sName,ijb,
rks,zhs,zdjb,zvjx,nsh,hz,jtsg,knh,qtsx,ywsh,ncdb,czdb,nctk,cztk,dsr,fhjry,qtry,grsq,zdfx,zjjz,fb,zb,jzzj,dfzc,shjz,qtzj,zjzc,
zgbz ,pjbz,sqly,qtcz,qtnc )
SELECT sys_guid(),''80'','||iyear||','||imonth||', sShengCode,sShengName,sShiCode,sShiName,sAreaCode,sName,sTownCode,sTownName,sVillageCode,sVillageName,sID,sName,ilevel,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 FROM sysArea
WHERE sid <> ''000000'' and ilevel<5 and iuse = 1 and sid like ''23%''';
execute immediate sqltemp;
nsql:='merge into RPT_IMPLEMENTSTATUS_TEMP p using(
select d1.stowncode sareacode,d1.stownname sareaname,
sum(case when (d1.slbcode=''8005'' or d1.slbcode=''8006'') then 1 else 0 end) dsr,sum(case when d1.slbcode=''8007'' then 1 else 0 end) qtcz,
sum(case when d1.slbcode=''8008'' then 1 else 0 end) qtnc,sum(case when d1.slbcode=''8009'' then 1 else 0 end) fhjry,
sum(case when (d1.slbcode=''8010'' or d1.slbcode=''8011'') then 1 else 0 end) qtry,
sum(case when db.jzms=''1'' then 1 else 0 end) grsq,sum(case when db.jzms=''2'' then 1 else 0 end) zdfx,
sum(case when db.jzfs=''1'' then 1 else 0 end) zjjz, sum(case when db.jzfs=''2'' then 1 else 0 end) fb,
sum(case when db.jzfs=''3'' then 1 else 0 end) zb,
0 as dfzc,
0 as shjz,0 as qtzj,
sum(db.fbzje) zjzc,
0 as zgbz,
0 as pjbz,'''' as sqly
from d11_lsjz_hlj db inner join d11_ledger_family_lsjz d1 on (d1.gfamilyid=db.gfamilyid and
db.inf=d1.inf and db.iyf=d1.iyf) inner join D10_Ledger_Totle_Lsjz d10 on
(d1.sareacode=d10.svillagecode and d1.inf=d10.inf and d1.iyf=d10.iyf and d1.sjtlbcode=d10.sjtlbcode)
where d10.iStatus=1
and db.ideletemark=0 and d1.ideletemark=0
group by d1.stowncode ,d1.stownname
)a
on (a.sareacode = p.sID) when matched then update set
p.qtcz=a.qtcz,p.qtnc=a.qtnc,p.fhjry=a.fhjry,p.qtry=a.qtry,p.zdfx=a.zdfx,
p.grsq=a.grsq, p.zjjz=a.zjjz,p.fb=a.fb,
p.zb=a.zb,p.dfzc=a.dfzc,p.shjz=a.shjz,p.qtzj=a.qtzj,p.zjzc=a.zjzc,
p.zgbz=a.zgbz,p.pjbz=a.pjbz,p.sqly=a.sqly where inf='||iyear||' and iyf='||imonth||'';
execute immediate nsql;
nsql:='merge into RPT_IMPLEMENTSTATUS_TEMP p using(
select d1.stowncode sareacode,d1.stownname sareaname,
sum(d1.issbzrks) rks,count(*) zhs, sum(db.fbzje)jzzj,
sum(case when db.ijzsx=''1'' then 1 else 0 end) hz, sum(case when db.ijzsx=''2'' then 1 else 0 end) jtsg,
sum(case when db.ijzsx=''3'' then 1 else 0 end) ywsh, sum(case when db.ijzsx=''4'' then 1 else 0 end) zdjb,
sum(case when (db.ijzsx=''5''or db.ijzsx=''8'') then 1 else 0 end) zvjx,sum(case when db.ijzsx=''9'' then 1 else 0 end) nsh,
sum(case when (db.ijzsx=''10'' or db.ijzsx is null) then 1 else 0 end) qtsx, sum(case when db.ijzsx=''6'' then 1 else 0 end) knh,
sum(case when d1.slbcode=''8001'' then 1 else 0 end) ncdb,
sum(case when d1.slbcode=''8002'' then 1 else 0 end) czdb,
sum(case when d1.slbcode=''8003'' then 1 else 0 end) cztk,
sum(case when d1.slbcode=''8004'' then 1 else 0 end) nctk,
sum(case when (d1.slbcode =''8005'' or d1.slbcode=''8006'') then 1 else 0 end) dsr,
sum(case when d1.slbcode =''8009'' then 1 else 0 end) fhjry,
sum(case when (d1.slbcode =''8099'' or d1.slbcode =''8007'' or d1.slbcode =''8008'' or d1.slbcode =''8011'' or d1.slbcode is null) then 1 else 0 end) qtry
from d11_lsjz_hlj db inner join d11_ledger_family_lsjz d1 on (d1.gfamilyid=db.gfamilyid and
db.inf=d1.inf and db.iyf=d1.iyf) inner join D10_Ledger_Totle_Lsjz d10 on
(d1.sareacode=d10.svillagecode and d1.inf=d10.inf and d1.iyf=d10.iyf and d1.sjtlbcode=d10.sjtlbcode)
where d10.iStatus=1
and db.ideletemark=0 and d1.ideletemark=0 group
by d1.stowncode,d1.stownname
)a
on (a.sareacode = p.sID) when matched then update set p.rks=a.rks, p.zhs=a.zhs,
p.jzzj=a.jzzj,p.hz=a.hz,p.jtsg=a.jtsg,p.ywsh=a.ywsh,p.zdjb=a.zdjb,p.zvjx=a.zvjx,
p.qtsx=a.qtsx,p.ncdb=a.ncdb,p.czdb=a.czdb,p.cztk=a.cztk,p.nctk=a.nctk,p.dsr=a.dsr,p.fhjry=a.fhjry,p.qtry=a.qtry,
p.knh=a.knh,p.nsh=a.nsh where inf='||iyear||' and iyf='||imonth||'';
execute immediate nsql;
--更新县区汇总
sqlstr3 :=' merge into RPT_IMPLEMENTSTATUS_TEMP p
using (select sareacode,
sum(rks)rks ,sum(zhs)zhs ,sum(zdjb)zdjb, sum(zvjx)zvjx,sum(nsh)nsh, sum(hz)hz, sum(jtsg)jtsg, sum(knh)knh, sum(qtsx)qtsx,
sum(ywsh)ywsh, sum(ncdb)ncdb,sum(czdb)czdb, sum(nctk)nctk, sum(cztk)cztk,sum(dsr)dsr, sum(fhjry)fhjry,sum(qtry)qtry,
sum( grsq)grsq, sum(zdfx)zdfx, sum(zjjz)zjjz, sum(fb)fb, sum(zb)zb, sum(jzzj)jzzj,sum(dfzc)dfzc, sum(shjz)shjz,sum(qtzj)qtzj,
sum(zjzc)zjzc,sum(zgbz)zgbz,sum(pjbz)pjbz, sum(sqly)sqly,sum(qtcz)qtcz,
sum(qtnc)qtnc from RPT_IMPLEMENTSTATUS_TEMP where ijb=4 group by sareacode) a on (a.sareacode = p.sid)
when matched then update set
p.rks=a.rks,p.zhs=a.zhs ,p.zdjb=a.zdjb, p.zvjx=a.zvjx,p.nsh=a.nsh,p.hz=a.hz,p.jtsg=a.jtsg,
p.knh=a.knh, p.qtsx=a.qtsx,p.ywsh=a.ywsh, p.ncdb=a.ncdb,p.czdb=a.czdb,p.nctk=a.nctk,p.cztk=a.cztk,
p.dsr=a.dsr,p.fhjry=a.fhjry,p.qtry=a.qtry, p.grsq=a.grsq,p.zdfx=a.zdfx,p.zjjz=a.zjjz,p.fb=a.fb,
p.zb=a.zb,p.jzzj=a.jzzj,p.dfzc=a.dfzc,p.shjz=a.shjz,p.qtzj=a.qtzj,p.zjzc=a.zjzc,p.zgbz=a.zgbz,p.pjbz=a.pjbz,p.sqly=a.sqly,p.qtcz=a.qtcz,
p.qtnc=a.qtnc where inf='||iyear||' and iyf='||imonth||'';
execute immediate sqlstr3;
--更新市汇总
sqlstr3 :=' merge into RPT_IMPLEMENTSTATUS_TEMP p
using (select sshicode, sum(rks)rks ,sum(zhs)zhs ,sum(zdjb)zdjb, sum(zvjx)zvjx,sum(nsh)nsh,
sum(hz)hz, sum(jtsg)jtsg, sum(knh)knh, sum(qtsx)qtsx, sum(ywsh)ywsh, sum(ncdb)ncdb,sum(czdb)czdb, sum(nctk)nctk,
sum(cztk)cztk,sum(dsr)dsr, sum(fhjry)fhjry,sum(qtry)qtry, sum( grsq)grsq, sum(zdfx)zdfx, sum(zjjz)zjjz, sum(fb)fb,
sum(zb)zb, sum(jzzj)jzzj,sum(dfzc)dfzc, sum(shjz)shjz,sum(qtzj)qtzj, sum(zjzc)zjzc,sum(zgbz)zgbz,sum(pjbz)pjbz, sum(sqly)sqly,sum(qtcz)qtcz,
sum(qtnc)qtnc from RPT_IMPLEMENTSTATUS_TEMP where ijb=3 group by sshicode) a on (a.sshicode = p.sid)
when matched then update set p.rks=a.rks,p.zhs=a.zhs ,p.zdjb=a.zdjb,
p.zvjx=a.zvjx,p.nsh=a.nsh,p.hz=a.hz,p.jtsg=a.jtsg, p.knh=a.knh, p.qtsx=a.qtsx,p.ywsh=a.ywsh,
p.ncdb=a.ncdb,p.czdb=a.czdb,p.nctk=a.nctk,p.cztk=a.cztk, p.dsr=a.dsr,p.fhjry=a.fhjry,p.qtry=a.qtry,
p.grsq=a.grsq,p.zdfx=a.zdfx,p.zjjz=a.zjjz,p.fb=a.fb, p.zb=a.zb,p.jzzj=a.jzzj,p.dfzc=a.dfzc,p.shjz=a.shjz,p.qtzj=a.qtzj,
p.zjzc=a.zjzc,p.zgbz=a.zgbz,p.pjbz=a.pjbz,p.sqly=a.sqly,p.qtcz=a.qtcz, p.qtnc=a.qtnc
where inf='||iyear||' and iyf='||imonth||'';
execute immediate sqlstr3;
--更新省汇总
sqlstr3 :=' merge into RPT_IMPLEMENTSTATUS_TEMP p
using (select sshengcode, sum(rks)rks ,sum(zhs)zhs ,sum(zdjb)zdjb,
sum(zvjx)zvjx,sum(nsh)nsh, sum(hz)hz, sum(jtsg)jtsg, sum(knh)knh, sum(qtsx)qtsx,
sum(ywsh)ywsh, sum(ncdb)ncdb,sum(czdb)czdb, sum(nctk)nctk, sum(cztk)cztk,sum(dsr)dsr, sum(fhjry)fhjry,sum(qtry)qtry,
sum( grsq)grsq, sum(zdfx)zdfx, sum(zjjz)zjjz, sum(fb)fb,sum(zb)zb, sum(jzzj)jzzj,sum(dfzc)dfzc, sum(shjz)shjz,sum(qtzj)qtzj,
sum(zjzc)zjzc,sum(zgbz)zgbz,sum(pjbz)pjbz, sum(sqly)sqly,sum(qtcz)qtcz, sum(qtnc)qtnc
from RPT_IMPLEMENTSTATUS_TEMP where ijb=2 group by sshengcode) a on (a.sshengcode = p.sid)
when matched then update set
p.rks=a.rks,p.zhs=a.zhs ,p.zdjb=a.zdjb, p.zvjx=a.zvjx,p.nsh=a.nsh,p.hz=a.hz,p.jtsg=a.jtsg,
p.knh=a.knh, p.qtsx=a.qtsx,p.ywsh=a.ywsh, p.ncdb=a.ncdb,p.czdb=a.czdb,p.nctk=a.nctk,p.cztk=a.cztk,
p.dsr=a.dsr,p.fhjry=a.fhjry,p.qtry=a.qtry, p.grsq=a.grsq,p.zdfx=a.zdfx,p.zjjz=a.zjjz,p.fb=a.fb,
p.zb=a.zb,p.jzzj=a.jzzj,p.dfzc=a.dfzc,p.shjz=a.shjz,p.qtzj=a.qtzj, p.zjzc=a.zjzc,p.zgbz=a.zgbz,p.pjbz=a.pjbz,p.sqly=a.sqly,p.qtcz=a.qtcz,
p.qtnc=a.qtnc where inf='||iyear||' and iyf='||imonth||'';
execute immediate sqlstr3;
update db_rpt set istate=1 where inf=iyear and iyf=imonth and sjtlbcode='' || slbcode || '' and sprocname= 'PKG_IMPLEMENTSTATUS.IMPLEMENTSTATUS';
c_result:='成功生成';
commit;
/* sqltemp :='SELECT * FROM RPT_IMPLEMENTSTATUS_TEMP where '||strSQL||' and '||UPlevelSearchName||'=''' || areaCode || ''' ORDER BY SID';*/
/*
open sResult for sqltemp;*/
end IMPLEMENTSTATUS;
end PKG_IMPLEMENTSTATUS;