My Second PL/SQL Procedure
来源:互联网 发布:c语言小游戏 编辑:程序博客网 时间:2024/05/22 12:32
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;
- My Second PL/SQL Procedure
- My First PL/SQL Procedure
- Problem - Slow PL/SQL procedure
- Oracle Procedure (PL/SQL) 实践归纳
- PL/SQL-procedure-function-package创建
- Assignment 1: PL/SQL stored procedure P9
- PL/SQL Procedure out varchar2 最大长度
- ADF BC. PL/SQL procedure params.
- PL/SQL-procedure-function-package创建
- VBA宏调用Oracle PL/SQL Procedure
- Calling a PL/SQL procedure in ODI
- PL/SQL 测试 调用PROCEDURE 和FUNCTION
- my procedure :)
- My PL/SQL practice --8/2/10
- Oracle PL/SQL Language Pocket Reference, Second Edition
- PL/SQL的procedure中DBMS_OUTPUT.put_line没有显示
- pl/sql存储过程(procedure)傻瓜入门教程
- oracle PL/SQL 下用command Window执行procedure
- windows下编译和使用libpq
- 在WEB页面上将EXCEL文件导入、导出到数据库中
- 游戏音乐制作的硬件环境
- 程序员如何防止脑疲劳:下午补充一些干果
- sadfa
- My Second PL/SQL Procedure
- 回到从前
- 对于防采集的一个思路
- 宽带 ISDN 用户部分(BISUP)--网络大典
- 搜索引擎原理
- 在arm上编译madplay
- 中国各地骗子大曝光(骗术揭秘)
- 在c++中模以”委托“
- 网络搜索引擎原理简介 [转]