The first and last record in a cursor for loop
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: The first and last record in a cursor for loop

  1. #1
    Join Date
    Mar 2001
    Posts
    286

    The first and last record in a cursor for loop

    I am using the following approach to tell if a record is the first record in the cursor for loop. Is there a better way to do this? Also, is there a way to see if the record is the last record?

    for rec in (select tmp.*, rownum
    from (select COLUMN_NAME
    from user_tab_columns where table_name=upper('&1') ) tmp)
    loop
    if rec.rownum = 1 then
    ----------------
    else
    ----------------
    end if;

    end loop;

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,469

    Cool

    Try this:

    for rec in (select COLUMN_NAME, COLUMN_ID
    from user_tab_columns
    where table_name=upper('&1')
    order by column_id)
    loop
    if rec.column_id = 1 then
    ----------------
    else
    ----------------
    end if;

    end loop;
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Dba_admin, in your code you should (IMHO) do the following:

    - declare your cursor explicitely, so that it has a name
    - use that named cursor in your cursor-for-loop
    - use cursor_name%ROWCOUNT attribute to find out if it is the first record returned

    Something like that:
    Code:
    DECLARE
      CURSOR my_cur IS 
        SELECT column_name FROM user_tab_columns
        WHERE table_name=UPPER('&1');
    BEGIN
      FOR rec IN my_cur LOOP
        IF my_cur%ROWCOUNT = 1 THEN 
          ----------------
        ELSE
          ----------------
        END IF;
      END LOOP;
      ---------------
    END;
    I think this is much more readable and elegant than using inline view inside the implicitly declared cursor.

    To see if the currently returned record is the last record in the resultset? No, you can't tell that in advance, not from inside the cursor.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Mar 2001
    Posts
    286
    Thank you, jmodic.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Hooray for me.

    Code:
    SQL> create table my_table (col1 number, col2 number,col3 number);
    
    Table created.
    
    SQL>  DECLARE
      2    CURSOR my_cur IS
      3      SELECT column_name ,count(*) over () tot_rows FROM user_tab_columns
      4      WHERE table_name=UPPER('&1');
      5  BEGIN
      6    FOR rec IN my_cur LOOP
      7      IF my_cur%ROWCOUNT = 1 THEN
      8        dbms_output.put_line('FIRST_ROW '||rec.column_name);
      9      ELSIF my_cur%rowcount = rec.tot_rows then
     10        dbms_output.put_line('LAST_ROW '||rec.column_name);
     11      END IF;
     12    END LOOP;
     13* END;
    SQL> /
    Enter value for 1: MY_TABLE
    old   4:     WHERE table_name=UPPER('&1');
    new   4:     WHERE table_name=UPPER('MY_TABLE');
    FIRST_ROW COL1
    LAST_ROW COL3
    
    PL/SQL procedure successfully completed.
    Since the "count(*) over ()" tells you the number of rows in the result set, you can use it within the loop to find out if you have reached the last record or not.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Isn't it easier to count them all before for loop?
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by TomazZ
    Isn't it easier to count them all before for loop?
    The number could change between the counting query and starting the cursor - couldn't it?

  8. #8
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Of course
    Party already started
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    you could avoid that problem with serializable transactions, but it would still be a slower solution i think.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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



Click Here to Expand Forum to Full Width