oracle存储过程

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;