弱水三千的答复:(帮忙发的) open c1; fetch c1 into v_newdate; if c1%notfound then v_wxsys:=0 end if v_currentdate:=v_newdate; loop v_olddate:=v_newdate; fetch c1 into v_newdate; exit when c1%notfound; if v_olddate>v_newdate then v_currentdate:=v_olddate; else v_currentdate:=v_newdate; end if; end loop; close c1;这么长的第一段其实就是一句话: select max(cgrq1) into v_currentdate from t_syryxxfc where grbm=v_grbm;整个存储过程也是一句话: select yxsys - sjxsys into v_wxsys from t_syryxxfc x where grbm=v_grbm and cgrq1=(select max(cgrq1) from t_syryxxfc where grbm=x.grbm);
是个语法错误,仔细check: create or replace procedure p_wxsys (v_grbm in varchar2,v_wxsys out number) as v_olddate date; v_newdate date; v_currentdate date;--最近封存时间 cursor c1 is select cgrq1 from t_syryxxfc where grbm=v_grbm; begin open c1; fetch c1 into v_newdate; if c1%notfound then v_wxsys:=0; end if; v_currentdate:=v_newdate; loop v_olddate:=v_newdate; fetch c1 into v_newdate; exit when c1%notfound; if v_olddate>v_newdate then v_currentdate:=v_olddate; else v_currentdate:=v_newdate; end if; end loop; close c1; select yxsys - sjxsys into v_wxsys from t_syryxxfc where grbm=v_grbm and cgrq1=v_currentdate; end p_wxsys;
v_wxsys:=0;
end if
open c1;
fetch c1 into v_newdate;
if c1%notfound then
v_wxsys:=0
end if
v_currentdate:=v_newdate;
loop
v_olddate:=v_newdate;
fetch c1 into v_newdate;
exit when c1%notfound;
if v_olddate>v_newdate then
v_currentdate:=v_olddate;
else
v_currentdate:=v_newdate;
end if;
end loop;
close c1;这么长的第一段其实就是一句话:
select max(cgrq1) into v_currentdate
from t_syryxxfc
where grbm=v_grbm;整个存储过程也是一句话:
select yxsys - sjxsys into v_wxsys
from t_syryxxfc x
where grbm=v_grbm
and cgrq1=(select max(cgrq1) from t_syryxxfc
where grbm=x.grbm);
create or replace procedure p_wxsys (v_grbm in varchar2,v_wxsys out number)
as
v_olddate date;
v_newdate date;
v_currentdate date;--最近封存时间
cursor c1 is select cgrq1 from t_syryxxfc where grbm=v_grbm;
begin
open c1;
fetch c1 into v_newdate;
if c1%notfound then
v_wxsys:=0;
end if;
v_currentdate:=v_newdate;
loop
v_olddate:=v_newdate;
fetch c1 into v_newdate;
exit when c1%notfound;
if v_olddate>v_newdate then
v_currentdate:=v_olddate;
else
v_currentdate:=v_newdate;
end if;
end loop;
close c1;
select yxsys - sjxsys into v_wxsys from t_syryxxfc where grbm=v_grbm and cgrq1=v_currentdate;
end p_wxsys;