Oracle Cursor Issue

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 ?

From stackoverflow Nick Long

• 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).

  From Thilo

• 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.

  David Aldridge : +1, especially for the use of analytics for previous-row logic.
  From Justin Cave