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),
i_yfcj 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_cjrq;--代理商编号
select sum(DLSBH_SB) into i_sbdls from SHGL_CJQD where CJRQ=i_cjrq;--设备代理商编号
--------------------------------
execute immediate tableStr;
execute immediate changeHuafei;
execute immediate changeTableDataStr;
insert into ChangeData(hm,a1,hmlb,b1,c1,d1,total,total1,total2) (select HM,BDHF,HMLB,BDGJ,BDCT,YHL,BDHF+BDCT+BDGJ,BDCT*3/1+BDGJ,(1-YHL)*(BDHF+BDCT+BDGJ) from SHGL_CJQD where CJRQ=changedDate);
insert into CjTable(hm,total,i_hmcj,i_sbcj)(select hm,total,H1*total2,S1*total2 from ChangeData where hmlb='1' and (total>=E1 or i_dlsbh=i_sbdls));
insert into CjTable(hm,total,i_hmcj,i_sbcj)(select hm,total,H1*total2,S1*total2 from ChangeData where hmlb='2' and total>=F2 and total>=G2);
insert into CjTable(hm,total,i_hmcj,i_sbcj)(select hm,total,H1*total2,S1*total2 from ChangeData where hmlb='2' and ((total1>=E2 and total<G2 )or i_dlsbh=i_sbdls)and total>=F2);
insert into CjTable(hm,total,i_hmcj,i_sbcj)(select hm,total,H1*total2,S1*total2 from ChangeData where hmlb='2' and (total>=F1 and total<F2) and total>=G1);
insert into CjTable(hm,total,i_hmcj,i_sbcj)(select hm,total,H1*total2,S1*total2 from ChangeData where hmlb='2' and (total>=F1 and total<F2) and ((total1>=E2 and total1<G1)or (i_dlsbh=i_sbdls)));
insert into CjTable(hm,total,i_hmcj,i_sbcj)(select hm,total,H1*total2,S1*total2 from ChangeData where hmlb='2' and total<F1 and (total1>=E2 or i_dlsbh=i_sbdls));
--------------------------------------------------------------
insert into LinShi (select hm,i_hmcj+i_sbcj from CjTable where i_dlsbh=i_sbdls);
insert into LinShi (select hm,i_hmcj from CjTable where i_dlsbh!=i_sbdls);
--------------------------------------------------------------
update aa
set aa.TOTAL=a1,
aa.HMCJ=a2,
aa.SBCJ=a3
from SHGL_CJQD aa,
(select total as a1, i_hmcj as a2, i_sbcj as a3,hm from CjTable) bb
where HM=bb.hm;
---------------------------------------------------------------------
update aa
set
aa.YFCJ=a1
from SHGL_CJQD aa,
(select i_yfcj as a1,hm from LinShi) cc
where aa.HM=cc.hm;
end;
可是我好象是结束的啊!!!!!!!!!!!!!不知道update可以这样写不?????
(
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),
i_yfcj 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_cjrq;--代理商编号
select sum(DLSBH_SB) into i_sbdls from SHGL_CJQD where CJRQ=i_cjrq;--设备代理商编号
--------------------------------
execute immediate tableStr;
execute immediate changeHuafei;
execute immediate changeTableDataStr;
insert into ChangeData(hm,a1,hmlb,b1,c1,d1,total,total1,total2) (select HM,BDHF,HMLB,BDGJ,BDCT,YHL,BDHF+BDCT+BDGJ,BDCT*3/1+BDGJ,(1-YHL)*(BDHF+BDCT+BDGJ) from SHGL_CJQD where CJRQ=changedDate);
insert into CjTable(hm,total,i_hmcj,i_sbcj)(select hm,total,H1*total2,S1*total2 from ChangeData where hmlb='1' and (total>=E1 or i_dlsbh=i_sbdls));
insert into CjTable(hm,total,i_hmcj,i_sbcj)(select hm,total,H1*total2,S1*total2 from ChangeData where hmlb='2' and total>=F2 and total>=G2);
insert into CjTable(hm,total,i_hmcj,i_sbcj)(select hm,total,H1*total2,S1*total2 from ChangeData where hmlb='2' and ((total1>=E2 and total<G2 )or i_dlsbh=i_sbdls)and total>=F2);
insert into CjTable(hm,total,i_hmcj,i_sbcj)(select hm,total,H1*total2,S1*total2 from ChangeData where hmlb='2' and (total>=F1 and total<F2) and total>=G1);
insert into CjTable(hm,total,i_hmcj,i_sbcj)(select hm,total,H1*total2,S1*total2 from ChangeData where hmlb='2' and (total>=F1 and total<F2) and ((total1>=E2 and total1<G1)or (i_dlsbh=i_sbdls)));
insert into CjTable(hm,total,i_hmcj,i_sbcj)(select hm,total,H1*total2,S1*total2 from ChangeData where hmlb='2' and total<F1 and (total1>=E2 or i_dlsbh=i_sbdls));
--------------------------------------------------------------
insert into LinShi (select hm,i_hmcj+i_sbcj from CjTable where i_dlsbh=i_sbdls);
insert into LinShi (select hm,i_hmcj from CjTable where i_dlsbh!=i_sbdls);
--------------------------------------------------------------
update aa
set aa.TOTAL=a1,
aa.HMCJ=a2,
aa.SBCJ=a3
from SHGL_CJQD aa,
(select total as a1, i_hmcj as a2, i_sbcj as a3,hm from CjTable) bb
where HM=bb.hm;
---------------------------------------------------------------------
update aa
set
aa.YFCJ=a1
from SHGL_CJQD aa,
(select i_yfcj as a1,hm from LinShi) cc
where aa.HM=cc.hm;
end;
可是我好象是结束的啊!!!!!!!!!!!!!不知道update可以这样写不?????
UPDATE AA SET AA.TOTAL = BB.A1,
AA.HMCJ = BB.A2,
AA.SBCJ = BB.A3
FROM SHGL_CJQD AA,(
SELECT TOTAL A1,I_HMCJ A2,I_SBCJ A3,HM
FROM CJTABLE
) BB
WHERE BB.HM = AA.HM;但你要保证对 CJTABLE 表查找出的记录行要有唯一性(即:四个子段的组合在记录集中不能重复),否则在给 AA.TOTAL 等赋值时会出现问题。
SET TOTAL = CJTABLE.TOTAL,
HMCJ = CJTABLE.I_HMCJ,
SBCJ = CJTABLE.I_SBCJ
FROM SHGL_CJQD ,CJTABLE
WHERE SHGL_CJQD.HM = CJTABLE.HM;