第一回写oracle的存储过程，不知道那里错了很多

shizhusz110 2008-02-27 02:50:50

create or replace procedure UpdateSHGL_CJ(
i_cjrq in date,
i_hm in varchar(20),
i_dqbh in number,
i_hmlb in char(1),
i_wxcj in number(10,2),
i_bdhf in number(10,2),
i_bdct in number(10,2),
i_bdgj in number(10,2),
i_yhl in number(10,2),
i_dlsbh_sb in number(10,2)
)
as
begin
--------------------------------------------------------
declare h1 in number(10,2);
declare s1 in number(10,2);
declare h2 in number(10,2);
declare s2 in number(10,2);
declare total1 in number(10,2);
declare total2 in number(10,2);
declare total3 in number(10,2);
declare A1 in number(10,2);
declare B1 in number(10,2);
declare C1 in number(10,2);
declare D1 in number(10,2);
declare F1 in int;
declare G1 in int ;
declare I1 in number(10,2);
declare F2 in int ;
declare G2 in int;
declare I2 in number(10,2);
declare E1 in int;
declare E2 in int;
declare i_yfcj in number(10,2);
declare i_hmcj number(10,2);
declare i_sbcj in number(10,2);
declare i_dlsbh in number;
declare i_sbdls in number;
declare i_hmcj in number(10,2);
declare i_sbcj in number(10,2);
----------------------------------------------------------------
set i_dlsbh = (select sum(dlsbh) from SHGL_CJQD where cjrq=i_cjrq);
set i_sbdls=(select sum(dlsbh_sb) from SHGL_CJQD where cjrq=i_cjrq);
set E1=30;
set E2=60;
set F1=100000;
set G1=100;
set I1=0.05;
set F2=200000;
set G2=100;
set I2=0.10;
set A1=i_bdhf;
set B1=i_bdgj;
set C1=i_bdct;
set D1=i_yhl;
set h1=0.49;
set s1=0.41;
set h2=0.25;
set s2=0.25;
set total1 = A1+B1+C1;
set total2= i_bdct+(1/(3*B1));
set total3=(1-D1)*total1;
---------------------------------------
if i_hmlb='1' then
if total2 >=E1 then
i_hmcj:=H1 * total3;
i_sbcj:=S1* total3;
end if;
else
if total1>=F2 then
if total1 >= G2 then
i_hmcj:=(H2+I2)*total1;
i_sbcj:=S2*total1;
end if;
if (total2>=E2 and total<G2) or (i_dls=i_sbdls) then
i_hmcj:=H2*total1;
i_sbcj:=S2*total1;
end if;
end if;
if total1>=F1 and total1<F2 then
if total1 >=G1 then
i_hmcj:= (H2+I1)*total1;
i_sbcj:=S2*total1;
end if;
if (total2>=E2 and total2<G1)or (i_dls=i_sbdls) then
i_hmcj:=H2 * total1;
i_sbcj:=S2*total1;
end if;
end if;
if total1<F1 then
i_hmcj:=H2*total1;
i_sbcj:=S2*total1;
end if;
end if;
--------------------------
if i_dls=i_sbdls then
i_yfcj:=i_hmcj+i_sbcj;
else
i_yfcj:=i_hmcj;
end if;
---------------------------------
update SHGL_CJQD
set
total=total1,
yfcj=i_yfcj,
hmcj=i_hmcj,
sbcj=i_sbcj
where hm=i_hm;
end;

...全文