Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    8

    Question Unanswered: Using Cursor with for... In......end loop syntax

    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

  2. #2
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107

    Re: Using Cursor with for... In......end loop syntax

    Originally posted by pdt
    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.
    nn

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Using Cursor with for... In......end loop syntax

    Originally posted by pdt
    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
    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;

  4. #4
    Join Date
    Nov 2002
    Posts
    8

    Re: Using Cursor with for... In......end loop syntax

    Originally posted by andrewst
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •