方案:
declare
cursor cur is
//得到需要循环的结果集
begin
for raw_row in cur
loop
//循环继续相应的操作
end loop;
end;
例如:
declare
cursor cur is
select distinct o.ordernumber,p.periodflag,p.status from
t_order o inner join t_orderdetail od on o.ordernumber=od.ordernumber
inner join t_period p on od.periodflag=p.periodflag
where o.paynumber= p_PayNumber;
begin
for raw_row in cur
loop
if raw_row.Status=1 then
v_OrderDetailStatus:=2;
elsif raw_row.Status=2 then
v_OrderDetailStatus:=2;
update t_PeriodBuy set Status=3
where OrderNumber2 in ( select OrderNumber2 from t_OrderDetail where ordernumber=raw_row.ordernumber);
elsif raw_row.Status=4 or raw_row.Status=5 then
v_OrderDetailStatus:=3;
update t_PeriodBuy set Status=4
where OrderNumber2 in ( select OrderNumber2 from t_OrderDetail where ordernumber=raw_row.ordernumber);
elsif raw_row.Status=3 or raw_row.Status=6 then
v_OrderDetailStatus:=2;
end if;
update t_OrderDetail set Status=v_OrderDetailStatus where OrderNumber=raw_row.ordernumber;
end loop;
end;