怎么提示dataForDate未定义
create or replace procedure UpdateSHGL_CJForDate(i_cjrq in date
)
as
type P_CURSOR is ref cursor;
dataForDate P_CURSOR;
r1 P_CURSOR;
h1 number(10,2):=0.49;--公话号码设备比率--号码
s1 number(10,2):=0.41;--公话号码设备比率--设备
h2 number(10,2):=0.25;--商话号码设备比率--号码
s2 number(10,2):=0.25;--商话号码设备比率--设备
A1 number(10,2);--国内话费
B1 number(10,2);--国际话费
C1 number(10,2);--国内长途话费
D1 number(10,2);--优惠率
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_yfcj number(10,2):=0.00;
i_hmcj number(10,2):=0.00;
i_sbcj number(10,2):=0.00;
i_dlsbh number:=0;
i_sbdls number:=0;
total1 number(10,2);--话费合计
total2 number(10,2);--长途话费合计
total3 number(10,2);--扣除优惠和话费
changeStrDate varchar(10);--转换日期字符集
changedDate date;--改变后的日期
begin
--------------------------------------------
--------------------------
select to_char(i_cjrq,'YYYY-MM') into changeStrDate from dual;
select to_date(changeStrDate,'YYYY-MM') into changedDate from dual;
---------------------------
---------------------------------------
open dataForDate for select HM,HMLB,BDHF,BDCT,BDGJ,YHL from SHGL_CJQD
where CJRQ=changedDate;
for r1 in dataForDate loop－－－－－－－－－－－－－－－－－－－－－－－－－－－－这里有错
A1:=r1.BDHF;
B1:=r1.BDCT;
C1:=r1.BDGJ;
D1:=r1.YHL;
-----------------------------------------------------------------------------
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;--设备代理商编号
------------------------------------------
total1 :=A1+B1+C1;--话费合计
total2 :=C1+(1/(3*B1));--长途话费合计
total3 :=(1-D1)*total1;--扣除优惠和话费
------------------------------------------
if r1.HMLB='1' then
begin
if (total2 >=E1) then
begin
i_hmcj:=H1 * total3;
i_sbcj:=S1* total3;
end;
end if;
end;
end if;
-----------------------------------------
if r1.HMLB='2' then --2
if total1>=F2 then--2.1
if total1 >= G2 then--2.1.1
i_hmcj:=(H2+I2)*total1;
i_sbcj:=S2*total1;
end if;
if (total2>=E2 and total1<G2) or (i_dlsbh=i_sbdls) then--2.1.2
i_hmcj:=H2*total1;
i_sbcj:=S2*total1;
end if;
end if;
if total1 >= F1 and total1 < F2 then--2.2
if (total1 >=G1) then--2.2.1
begin
i_hmcj:= (H2+I1) * total1;
i_sbcj:= S2 * total1;
end;
end if;
if (total2>=E2 and total2<G1)or (i_dlsbh=i_sbdls) then--2.2.2
begin
i_hmcj:=H2 * total1;
i_sbcj:=S2*total1;
end;
end if;
end if;
if total1<F1 then--2.3
begin
i_hmcj:=H2 * total1;
i_sbcj:=S2*total1;
end;
end if;--end2.3
end if;
--------------------------
if i_dlsbh=i_sbdls then--3
begin
i_yfcj:=i_hmcj+i_sbcj;
end;
else
begin
i_yfcj:=i_hmcj;
end;
end if;
-------------------------------------
update SHGL_CJQD
set TOTAL=total1,
YFCJ=i_yfcj,
HMCJ=i_hmcj,
SBCJ=i_sbcj
where HM=r1.HM;
end loop;
end;
页:
[1]