-
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;
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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?
-
-
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?
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
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
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
-
you could avoid that problem with serializable transactions, but it would still be a slower solution i think.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|