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;

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;
:D

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.

Thank you, jmodic.

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.

Isn't it easier to count them all before for loop?

Quote:

---

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?

Of course :o
Party already started :D

you could avoid that problem with serializable transactions, but it would still be a slower solution i think.