Sponsored Links
cursor, record, next, peek
cursor, record, next, peekI
have a procedure that uses a cursor to process the record in a table
serially from beginning to end. Some of the logical records span more
than one row and I need a way to peek at the next record with out
changing the position of my cursor.
I was thinking of something like:
Select * into new_record from table where rowid = rowid_of_current_record + 1
Can I use rowid to do this? Is there a better way?
A:
rowid = rowid_of_current_record + 1
Rowids
are not sequential. And the above expression doesnt guatantee any order
of your result set. Each record in your table gets assigned to a new
rowid as an when the record goes into the table. So, if you code a
program to peek into the next record using ROWID, the program may look
into one record today. and if the record gets deleted tomorrow, your
program will peek into another record tomorrow.
So, what exactly is the problem you are trying to solve here.
>> peek at the next row, what are the columns that decides the next row you want to peek at?
Now, you can use the LEAD operator to solve this problem.
Have a look at this example:
SQL> select * from tbl2 order by id;
ID VAL
---------- --------------------
20 two
40 four
50 five
70 seven
100 ten
SQL> select id, val,
2 lead(id, 1) over(order by id) next_id,
3 lead(val, 1) over(order by id) next_val
4 from tbl2
5 /
ID VAL NEXT_ID NEXT_VAL
---------- -------------------- ---------- --------
20 two 40 four
40 four 50 five
50 five 70 seven
70 seven 100 ten
100 ten
You can also use ROWID in this logic(but make sure that you get the correct results).
SQL> select * from tbl2 order by rowid;
ID VAL
---------- --------------------
100 ten
20 two
40 four
50 five
70 seven
SQL> select id, val,
2 lead(id, 1) over(order by rowid) next_id,
3 lead(val, 1) over(order by rowid) next_val
4 from tbl2
5 /
ID VAL NEXT_ID NEXT_VAL
---------- -------------------- ---------- -----------
100 ten 20 two
20 two 40 four
40 four 50 five
50 five 70 seven
70 seven
Page 1
|
| 
