My Second PL/SQL Procedure

CREATE OR REPLACE PROCEDURE Mk_Csyb_Xxsc(p_region VARCHAR2, p_ny VARCHAR2, p_loginid VARCHAR2, p_deptno VARCHAR2) as   p_nf Varchar2(4) := substr(p_ny,0,4);BEGIN   delete from mk_csyb_temp where LOGINID = p_loginid and DEPTNO = p_deptno and REGION = p_region;----------------------------------------------東部市場-----------------------------------------   if p_region = @#db@# then            DBMS_OUTPUT.PUT_LINE(@#東部Before!!!@#|| p_region ||@#  @#|| p_ny ||@#  @#|| p_loginid ||@#  @#|| p_deptno);            insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw)      ( /* 東部市場創收月報mk_csyb_dbsc中的指標完成 */        select p_loginid LOGINID, p_deptno DEPTNO, p_region REGION,                bm_ytdw.bkdm BKDM, bm_ytdw.bk BKMC, nvl(bm_ytdw.xh,199) GSXH, bm_ytdw.mc GSMC, p_ny NY, @#1@# QYXH, A.QY NEWQY,                mk_csyb_dbsc.ZBE ZBE, mk_csyb_dbsc.ZBDW ZBDW        from bm_ytdw,mk_csyb_dbsc, (select * from (select distinct(QY) QY from mk_csyb_dbsc where ny = p_ny) qy, (select distinct(dwdm) dwdm from mk_csyb_dbsc where ny = p_ny) dwdm) A        where bm_ytdw.dm = A.dwdm          and mk_csyb_dbsc.ny(+) = p_ny          and mk_csyb_dbsc.qy(+) = A.qy          and mk_csyb_dbsc.dwdm(+) = A.dwdm      );

      insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw)      ( /* mk_cszb_db表中所有的東部創收年度指標 */        select p_loginid LOGINID, p_deptno DEPTNO, p_region REGION,                bm_ytdw.bkdm BKDM, bm_ytdw.bk BKMC, nvl(bm_ytdw.xh,199) GSXH, bm_ytdw.mc GSMC, p_ny NY, @#0@# QYXH, @#年度指標@# NEWQY,                mk_cszb_db.DBZB ZBE, @#@# ZBDW        from bm_ytdw,mk_cszb_db        where bm_ytdw.dm = mk_cszb_db.dw          and mk_cszb_db.nf = p_nf      );            insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw)      ( /* 每個公司的指標完成率 */        select p_loginid LOGINID, p_deptno DEPTNO, p_region REGION,                max(bm_ytdw.bkdm) BKDM, bm_ytdw.bk BKMC, nvl(max(bm_ytdw.xh),199) GSXH, bm_ytdw.mc GSMC, p_ny NY, @#3@# QYXH, @#完成率@# NEWQY,               decode(max(mk_cszb_db.DBZB),0,0,(sum(mk_csyb_dbsc.ZBE)/max(mk_cszb_db.DBZB))*100) ZBE, max(mk_csyb_dbsc.ZBDW) ZBDW        from bm_ytdw,mk_csyb_dbsc,mk_cszb_db        where bm_ytdw.dm = mk_csyb_dbsc.dwdm          and bm_ytdw.dm = mk_cszb_db.dw          and mk_csyb_dbsc.ny = p_ny          and mk_cszb_db.nf = p_nf        group by bm_ytdw.bk,bm_ytdw.mc      );            insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw)      ( /* 每個公司的指標完成總計 */        select p_loginid LOGINID, p_deptno DEPTNO, p_region REGION,                max(bm_ytdw.bkdm) BKDM,bm_ytdw.bk BKMC, nvl(max(bm_ytdw.xh),199) GSXH, bm_ytdw.mc GSMC, p_ny NY, @#2@# QYXH, @#總計@# NEWQY,               sum(mk_csyb_dbsc.ZBE) ZBE, max(mk_csyb_dbsc.ZBDW) ZBDW        from bm_ytdw,mk_csyb_dbsc        where bm_ytdw.dm = mk_csyb_dbsc.dwdm          and mk_csyb_dbsc.ny = p_ny        group by bm_ytdw.bk,bm_ytdw.mc      );            insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw)      ( /*********************************************/        /****************開始   小 計 ****************/        /*按區塊和區域分組的  各油區指標完成小計 */        select p_loginid LOGINID, p_deptno DEPTNO, p_region REGION,                max(bm_ytdw.bkdm) BKDM,bm_ytdw.bk BKMC, 200 GSXH, @#**小  計**@# GSMC, p_ny NY, @#1@# QYXH, mk_csyb_dbsc.QY NEWQY,               sum(mk_csyb_dbsc.ZBE) ZBE, max(mk_csyb_dbsc.ZBDW) ZBDW        from bm_ytdw,mk_csyb_dbsc        where bm_ytdw.dm = mk_csyb_dbsc.dwdm          and mk_csyb_dbsc.ny = p_ny        group by bm_ytdw.bk,mk_csyb_dbsc.QY      );            insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw)      ( /* 按區塊劃分的  年度指標小計 */        select p_loginid LOGINID, p_deptno DEPTNO, p_region REGION,                max(bm_ytdw.bkdm) BKDM,bm_ytdw.bk BKMC, 200 GSXH, @#**小  計**@# GSMC, p_ny NY, @#0@# QYXH, @#年度指標@# NEWQY,               sum(mk_cszb_db.DBZB) ZBE, @#@# ZBDW        from bm_ytdw,mk_cszb_db        where bm_ytdw.dm = mk_cszb_db.dw          and mk_cszb_db.nf = p_nf        group by bm_ytdw.bk      );            insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw)      ( /* 按區塊分組的  指標完成總計 */        select p_loginid LOGINID, p_deptno DEPTNO, p_region REGION,                max(bm_ytdw.bkdm) BKDM,bm_ytdw.bk BKMC, 200 GSXH, @#**小  計**@# GSMC, p_ny NY, @#2@# QYXH, @#總計@# NEWQY,               sum(mk_csyb_dbsc.ZBE) ZBE, max(mk_csyb_dbsc.ZBDW) ZBDW        from bm_ytdw,mk_csyb_dbsc        where bm_ytdw.dm = mk_csyb_dbsc.dwdm          and mk_csyb_dbsc.ny = p_ny        group by bm_ytdw.bk      );            insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw)      ( /* 按區塊分組的  小計的指標完成率 */        select p_loginid LOGINID, p_deptno DEPTNO, p_region REGION,                A.BKDM BKDM, A.BKMC BKMC, A.GSXH GSXH, A.GSMC GSMC, A.NY NY, A.QYXH QYXH, A.QY NEWQY,               decode(A.zbe,0,0,nvl(B.zbe,0)/A.zbe*100) ZBE, @#@# ZBDW        from             /*----- 按區塊劃分的  年度指標小計*/             (select max(bm_ytdw.bkdm) BKDM,bm_ytdw.bk BKMC, 200 GSXH, @#**小  計**@# GSMC, p_ny NY, @#3@# QYXH, @#完成率@# QY,                     sum(mk_cszb_db.DBZB) ZBE, @#@# ZBDW              from bm_ytdw,mk_cszb_db              where bm_ytdw.dm = mk_cszb_db.dw                and mk_cszb_db.nf = p_nf              group by bm_ytdw.bk) A,             /*----- 按區塊分組的  指標完成總計*/             (select max(bm_ytdw.bkdm) BKDM, sum(mk_csyb_dbsc.ZBE) ZBE              from bm_ytdw,mk_csyb_dbsc              where bm_ytdw.dm = mk_csyb_dbsc.dwdm                and mk_csyb_dbsc.ny = p_ny              group by bm_ytdw.bk) B        where A.BKDM = B.BKDM(+)      /****************結束   小 計 ****************/      );            insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw)      ( /****************開始   總 計 ****************/        /* 年度指標額的總計 */        select p_loginid LOGINID, p_deptno DEPTNO, p_region REGION,                @#ZZZZ@# BKDM,@#=====總  計=====@# BKMC, 200 GSXH, @# @# GSMC, p_ny NY, @#0@# QYXH, @#年度指標@# NEWQY,               sum(mk_cszb_db.DBZB) ZBE, @#@# ZBDW        from bm_ytdw,mk_cszb_db        where bm_ytdw.dm = mk_cszb_db.dw          and mk_cszb_db.nf = p_nf      );            insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw)      ( /* 各個域區的指標完成總計 */        select p_loginid LOGINID, p_deptno DEPTNO, p_region REGION,                @#ZZZZ@# BKDM, @#=====總  計=====@# BKMC, 200 GSXH, @# @# GSMC, p_ny NY, @#1@# QYXH, mk_csyb_dbsc.QY NEWQY,                sum(mk_csyb_dbsc.ZBE) ZBE, @#@# ZBDW        from bm_ytdw,mk_csyb_dbsc        where bm_ytdw.dm = mk_csyb_dbsc.dwdm          and mk_csyb_dbsc.ny = p_ny        group by mk_csyb_dbsc.QY      );            insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw)      ( /* 指標完成總計 */        select p_loginid LOGINID, p_deptno DEPTNO, p_region REGION,                @#ZZZZ@# BKDM, @#=====總  計=====@# BKMC, 200 GSXH, @# @# GSMC, p_ny NY, @#2@# QYXH, @#總計@# NEWQY,               sum(mk_csyb_dbsc.ZBE) ZBE, @#@# ZBDW        from bm_ytdw,mk_csyb_dbsc        where bm_ytdw.dm = mk_csyb_dbsc.dwdm          and mk_csyb_dbsc.ny = p_ny      );            insert into mk_csyb_temp (loginid, deptno, region, bkdm, bkmc, gsxh, gsmc, ny, qyxh, qy, zbe, zbdw)      ( /* 總計完成率 */        select p_loginid LOGINID, p_deptno DEPTNO, p_region REGION,                A.BKDM BKDM, A.BKMC BKMC, A.GSXH GSXH, A.GSMC GSMC, A.NY NY, A.QYXH QYXH, A.QY NEWQY,               decode(A.zbe,0,0,nvl(B.zbe,0)/A.zbe*100) ZBE, @#@# ZBDW        from             /* 年度指標額的總計 */             (select @#ZZZZ@# BKDM,@#=====總  計=====@# BKMC, 200 GSXH, @# @# GSMC, p_ny NY, @#3@# QYXH, @#完成率@# QY,                     sum(mk_cszb_db.DBZB) ZBE, @#@# ZBDW              from bm_ytdw,mk_cszb_db              where bm_ytdw.dm = mk_cszb_db.dw                and mk_cszb_db.nf = p_nf) A,             /* 指標完成總計 */             (select @#ZZZZ@# BKDM, sum(mk_csyb_dbsc.ZBE) ZBE              from bm_ytdw,mk_csyb_dbsc              where bm_ytdw.dm = mk_csyb_dbsc.dwdm                and mk_csyb_dbsc.ny = p_ny) B      );        /****************結束   總 計 ****************/

      DBMS_OUTPUT.PUT_LINE(@#東部Suclearcase/" target="_blank" >ccess!!!@#|| p_region ||@#  @#|| p_ny ||@#  @#|| p_loginid ||@#  @#|| p_deptno);   end if;

     commit;     exception when others then     rollback;END Mk_Csyb_Xxsc;

原文轉自：http://www.kjueaiud.com