|
#2
ILoveMK2008-03-01 19:58
|
create or replace procedure UpdateAllSHGL_CJForDate
(
i_date in date
)
as
tableStr varchar(2048):='CREATE GLOBAL TEMPORARY TABLE ChangeData(
hm varchar(20),
a1 number(10,2),
hmlb char(1),
b1 number(10,2),
c1 number(10,2),
d1 number(10,2),
total number(10,2),
total1 number(10,2),
total2 number(10,2))ON COMMIT P RESERVE ROWS';
changeHuafei varchar(1024):='CREATE GLOBAL TEMPORARY TABLE CjTable(
hm varchar(20),
total number(10,2),
i_hmcj number(10,2),
i_sbcj number(10,2))ON COMMIT P RESERVE ROWS';
changeTableDataStr varchar(64):='CREATE GLOBAL TEMPORARY TABLE LinShi(
hm varchar(20),
i_yfcj number(10,2))ON COMMIT P RESERVE ROWS';
chaneDateStr varchar(16);
changedDate date;
H1 number(10,2):=0.49;--公话号码设备比率--号码
S1 number(10,2):=0.41;--公话号码设备比率--设备
H2 number(10,2):=0.25;--商话号码设备比率--号码
S2 number(10,2):=0.25;--商话号码设备比率--设备
F1 int:=100000;
G1 int:=100;
I1 number(10,2):=0.05;
F2 int:=200000;
G2 int:=100;
I2 number(10,2):=0.10;
E1 int:=30;
E2 int:=60;
i_dlsbh number;
i_sbdls number;
begin
-----------------------------
select to_char(i_date,'YYYY-MM') into chaneDateStr from dual;
select to_date(chaneDateStr,'YYYY-MM') into changedDate from dual;
-----------------------------
select sum(DLSBH) into i_dlsbh from SHGL_CJQD where CJRQ=i_date;--代理商编号
select sum(DLSBH_SB) into i_sbdls from SHGL_CJQD where CJRQ=i_date;--设备代理商编号
--------------------------------
execute immediate tableStr;
execute immediate changeHuafei;
execute immediate changeTableDataStr;
select HM,BDHF,HMLB,BDGJ,BDCT,YHL,BDHF+BDCT+BDGJ,BDCT*3/1+BDGJ,(1-YHL)*(BDHF+BDCT+BDGJ) into ChangeData.hm,ChangeData.a1,ChangeData.hmlb,ChangeData.b1,ChangeData.c1,ChangeData.d1,ChangeData.total,ChangeData.total1,ChangeData.total2 from SHGL_CJQD where CJRQ=changedDate;
select hm,total,H1*total2,S1*total2 into CjTable.hm,CjTable.total,CjTable.i_hmcj,CjTable.i_sbcj from ChangeData where hmlb='1' and (total>=E1 or i_dlsbh=i_sbdls);
select hm,total,H1*total2,S1*total2 into CjTable.hm,CjTable.total,CjTable.i_hmcj,CjTable.i_sbcj from ChangeData where hmlb='2' and total>=F2 and total>=G2;
select hm,total,H1*total2,S1*total2 into CjTable.hm,CjTable.total,CjTable.i_hmcj,CjTable.i_sbcj from ChangeData where hmlb='2' and ((total1>=E2 and total<G2 )or i_dlsbh=i_sbdls)and total>=F2;
select hm,total,H1*total2,S1*total2 into CjTable.hm,CjTable.total,CjTable.i_hmcj,CjTable.i_sbcj from ChangeData where hmlb='2' and (total>=F1 and total<F2) and total>=G1;
select hm,total,H1*total2,S1*total2 into CjTable.hm,CjTable.total,CjTable.i_hmcj,CjTable.i_sbcj from ChangeData where hmlb='2' and (total>=F1 and total<F2) and ((total1>=E2 and total1<G1)or (i_dlsbh=i_sbdls));
select hm,total,H1*total2,S1*total2 into CjTable.hm,CjTable.total,CjTable.i_hmcj,CjTable.i_sbcj from ChangeData where hmlb='2' and total<F1 and (total1>=E2 or i_dlsbh=i_sbdls);
--------------------------------------------------------------
select hm,i_hmcj+i_sbcj into LinShi from CjTable where i_dlsbh=i_sbdls;
select hm,i_hmcj into LinShi from CjTable where i_dlsbh!=i_sbdls;
--------------------------------------------------------------
update SHGL_CJQD aa
set aa.TOTAL=(select total from CjTable bb where aa.HM=bb.hm),
aa.HMCJ=(select i_hmcj from CjTable bb where aa.HM=bb.hm),
aa.SBCJ=(select i_sbcj from CjTable bb where aa.HM=bb.hm)
where exists(select 1 from CjTable bb where aa.HM=bb.hm) ;
---------------------------------------------------------------------
update SHGL_CJQD aa
set
aa.YHCJ=(select i_yfcj from LinShi bb where aa.HM=bb.hm)
where exists(select 1 from LinShi bb where aa.HM=bb.hm) ;
end;
－－－－－－－
谢谢！