Oracle Cursor Issue

Question

Anyone have any idea how to do the following?

declare cursor open cursor fetch cursor << Start reading the cursor in a LOOP >> Lets say the cursor have 10 records. Read until 5th record then go to the 6th record and do some checking.

Now, is it possible to go back to 5th record from 6th record ?

Answer 1

How far do you need to go back? If you only need a look-ahead of one row, you could buffer just the previous row in your loop (application-side).

Answer 2

Depends on the requirements.

You can use the LAG() and LEAD() analytic functions to get information for the next and prior rows, i.e.

SQL> ed
Wrote file afiedt.buf

  1  select ename,
  2         sal,
  3         lead(sal) over (order by ename) next_sal,
  4         lag(sal) over (order by ename) prior_sal
  5    from emp
  6*  order by ename
SQL> /

ENAME             SAL   NEXT_SAL  PRIOR_SAL
---------- ---------- ---------- ----------
ADAMS            1100       1600
ALLEN            1600       2850       1100
BLAKE            2850       2450       1600
CLARK            2450       3000       2850
FORD             3000        950       2450
JAMES             950       2975       3000
JONES            2975       5000        950
KING             5000       1250       2975
MARTIN           1250       1300       5000
MILLER           1300       3000       1250
SCOTT            3000        800       1300

ENAME             SAL   NEXT_SAL  PRIOR_SAL
---------- ---------- ---------- ----------
SMITH             800       1500       3000
TURNER           1500       1250        800
WARD             1250                  1500

14 rows selected.

If you don't want to use analytic functions, you can use PL/SQL collections, BULK COLLECT the data into those collections (using the LIMIT clause if you have more data than you want to store in your PGA) and then move forward and backward through your collections.