My Second PL/SQL Procedure

原创 2005年04月25日 12:42:00

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('东部Success!!!'|| p_region ||'  '|| p_ny ||'  '|| p_loginid ||'  '|| p_deptno);
   end if;

     commit;
     exception when others then
     rollback;
END Mk_Csyb_Xxsc;

执行方法为PL/SQL Stored Procedure的并发程序

第一步:写一个过程,可以把参数及相应的信息写入ouput file或log file,fnd_file.PUT_LINE(which IN number, buff IN varchar2)的参数中,...

Oracle PL/SQL package(包),Procedure(存储过程),Function,Exception(例外处理)简单的总结

本博客仅仅个人学习总结,非权威文档,如果您在阅读的过程中发现文中有错误,请不惜指教,在评论区写下您发现的错误,本人会及时更正,谢谢。...

PL/SQL,創建procedure或function時,提示V$視圖不存在。

用戶可以在sql中查詢V$視圖,但是創建procedure或function時,提示V$視圖不存在。 SQL> create function get_stat_val 2 ( 3 ...
  • yfb8530
  • yfb8530
  • 2013年03月26日 16:59
  • 603

Problem - Slow PL/SQL procedure

 http://www.oraclerecipes.com/tuning/using-dbms-profiler-to-find-bottleneck-in-a-procedure/ When...

PL/SQL Developer登录后默认选中My Objects

设置方法:Tools -> Object Browser Filters 选择My objects 设置Default即可同样也可在 Tools -> Object Browser Folders 中...

PL/SQL Developer 9.0 用户指南

  • 2017年12月06日 16:37
  • 3.65MB
  • 下载

PL/SQL Developer 12.0.4 有效注册码

  • 2017年12月12日 11:02
  • 124B
  • 下载

PL/SQL 变量与常用数据类型

1.基础数据类型 以ORACLE8i为例,可以使用的基础数据类型有: 类型 子类 说     明 范   围 ORACLE限制 ...

PL/SQL Developer 12.0.6.1832

  • 2017年11月18日 20:25
  • 38.32MB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:My Second PL/SQL Procedure
举报原因:
原因补充:

(最多只允许输入30个字)