If I use a cursor with the for... IN ...end loop syntax....like


for rec_base in cInterval
if rec_base.trans_id = blah blah blah...
end loop

will the cursor close automatically after it finds it's last row. I would like
to use the same cursor a number of times in a single procedure. Each time I use it I need the cursor to run the select again to make sure
it's result set is current.


Thank you

Hi,
Use a ref cursor for this and give
open c1 for <select stmt> each time. this will give you the
latest data.

Yes, the cursor will be closed automatically after the last row is fetched, or if you exit from the loop prematurely for any reason. And each time you open the cursor it will get the current data. So you have no problems with this approach.

My only comment would be that if your example above was the requirement, it would be more efficient if possible to code:

DECLARE
cursor cInterval is
select .... from ... where ...
and rec_base.trans_id = blah blah blah...
BEGIN
for rec_base in cInterval
...
end loop;

Thank you for the answers. Nice to know.