日期:2014-05-17  浏览次数:20790 次

第一回写oracle的存储过程,不知道那里错了很多
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;


------解决方案--------------------
我的异常网推荐解决方案:oracle存储过程,http://www.aiyiweb.com/oracle-develop/177537.html
------解决方案--------------------
到oracle上调试下
------解决方案--------------------
i_hm in varchar(20),

我下面没看 ,我列出的纠错了吧

把后面(20)去掉,

------解决方案--------------------
创建完后,在edit窗口运行一下,就知道哪里有错了,错误很多阿
------解决方案-----