... v1 kid.in%rowtype; v2 varchar2(20); --用于存放rowidv_sqlstring varchar2(1024); --sql语句 cursor kid_cur is select last,rowid from kid order by loto; begin open kid_cur loop fetch kid_cur into v1,v2; exit when kid_cur%notfound; if (want_out>=v1) then v_sqlstring :='update kid set out=last where rowid='||v2; execute immediate v_sqlstring; commit; v_sqlstring :='update kid set last=last-out' where rowid='||v2; commit; want_out :=want_out-v1; --want_out 出仓量,函数(过程)的输入变量 else v_sqlstring :='update kid set out='||v1||' where rowid='||v2; execute immediate v_sqlstring; commit; v_sqlstring :='update kid set last=last-out'; execute immediate v_sqlstring; exit; --跳出循环 end if; exception ... end; /
v1 kid.in%rowtype;
v2 varchar2(20); --用于存放rowidv_sqlstring varchar2(1024); --sql语句
cursor kid_cur is select last,rowid from kid order by loto;
begin
open kid_cur
loop
fetch kid_cur into v1,v2;
exit when kid_cur%notfound; if (want_out>=v1) then
v_sqlstring :='update kid set out=last where rowid='||v2;
execute immediate v_sqlstring;
commit;
v_sqlstring :='update kid set last=last-out' where rowid='||v2;
commit;
want_out :=want_out-v1; --want_out 出仓量,函数(过程)的输入变量
else
v_sqlstring :='update kid set out='||v1||' where rowid='||v2;
execute immediate v_sqlstring;
commit;
v_sqlstring :='update kid set last=last-out';
execute immediate v_sqlstring;
exit; --跳出循环
end if;
exception
...
end;
/
end if;
上面的一个循环里的update语句也可以合成一个语句,不过要注意逻辑。