postgresSQL下游标相关问题

[yeak2001]

DECLARE discount NUMERIC;
DECLARE price NUMERIC;
DECLARE temp NUMERIC;
DECLARE percent NUMERIC;
DECLARE my_cursor refcursor;
DECLARE pay_out NUMERIC;

BEGIN
temp = 0;
OPEN my_cursor FOR SELECT pricing.prc_price_component.price
FROM pricing.prc_price_component
INNER JOIN
(SELECT pricing.prc_agreement_item.agreement_item_id FROM pricing.prc_agreement_item WHERE
pricing.prc_agreement_item.agreement_id = $1 AND
pricing.prc_agreement_item.role_type_id = 'SALES_REP')
_sr_agreement_item_ ON _sr_agreement_item_.agreement_item_id =
pricing.prc_price_component.agreement_item_id;
LOOP
FETCH my_cursor into price;
IF(discount>=price AND price>=temp) THEN
temp = price;
pay_out = percent;
END IF;
END LOOP;
return pay_out;
CLOSE my_cursor;
END;

在FETCH记录集的时候提示MISS"IN/FROM" at the end of the statement at line 19 near ";"就是 FETCH my_cursor into price;这个写的有问题吗？请高手帮忙看看，谢谢

[yeak2001]

没人回答吗，自己顶下，语法有问题吗？

[trainee]

你的LOOP没有退出机制

其实不要用CURSOR, 直接用for ... loop, plpgsql在内部会按CURSOR来处理
declare myrec record;

for myrec in select .... loop

....
end loop

[lupanlupan]

你的LOOP没有退出机制

其实不要用CURSOR, 直接用for ... loop, plpgsql在内部会按CURSOR来处理
declare myrec record;

for myrec in select .... loop

....
end loop