How to move backwards

[vikas000000a]

I have a problem regarding PL/SQL. I want to know how to move backwards while scanning a cursor:

The prototype of my procedure is as follows:

create or replace procedure xxx is
      cursor c1 is select thmaster.rowid, thmaster.* from thmaster;
      num_recs number;
      prev_txn number;
      curr_txn number;
begin
      prev_txn:=0;
      for r1 in c1
      loop
           curr_txn:=r1.txncntr;
           if curr_txn<>prev_txn+1 then
                 -- [B]Here I want to fetch previous record and update some field of that record.[/B]                 
                 commit;
          end if;
--
          prev_txn:=curr_txn;
      end loop;
end;

In the if statement above, I want to fetch previous record and update some field of that record. But I dont seem to find any syntax for that. Actually, I am looking for something like

recordset.moveprevious

method of Visual Basic.

Thanks in anticipation.

[debasisdas]

there is no way you can go back using oracle cursor. there is no such method or facility.

[vikas000000a]

there is no way you can go back using oracle cursor. there is no such method or facility.

It may not be in your knowledge, but one thing I can bet for is that there must be something like that to achieve my requirement. Beacuse it is not a very rare kind of requirement. It is generally needed for day-to-day programming.

May be I will have to search somewhere else.

[Dave44]

It may not be in your knowledge, but one thing I can bet for is that there must be something like that to achieve my requirement. Beacuse it is not a very rare kind of requirement. It is generally needed for day-to-day programming.

May be I will have to search somewhere else.

Yes, there is always more than one way to skin a cat ;)

In your algorithm, will it be the most previous row that you want? In oracle there isnt a way to start looping through a cursor record set and then suddently reverse direction and loop back again.

But there are alternatives or other ways. How do you define the order of the record set you want to loop through? See if you suddenly wanted to reverse direction it would indicate that you reached some specific point that indicated such a need. I dont see an order by in your cursor. the reason i want to know is that we can use analytic functions like lag() or row_Number() or rank() to order the rows a specific way.
For example lag() can be used to return the previous rows value of a certain field based upon some order defined. likewise lead() will return the next rows value of a certain field based upon a sort order given.

can you give a little more info on what it is you need to do?

[debasisdas]

It may not be in your knowledge, but one thing I can bet for is that there must be something like that to achieve my requirement. Beacuse it is not a very rare kind of requirement. It is generally needed for day-to-day programming.

May be I will have to search somewhere else.

I would really appreceate ,if you can post the answer and a way to move backwards in oracle cursor.

[amitpatel66]

You have almost got the solution of accessing or refering to the previous record by selecting a rowid in your cursor. what you just need to do further is this:

[code=oracle]

SQL> ed
Wrote file afiedt.buf

1 declare
2 cursor c1 is select rowid,emp_det.* from emp_det ORDER BY emp_det.empno;
3 empno_prev NUMBER := 0;
4 empno_curr NUMBER := 0;
5 empd emp_det%ROWTYPE ;
6 prev_rowid VARCHAR2(100) := NULL;
7 begin
8 FOR I IN C1 LOOP
9 empno_curr:= I.empno;
10 IF empno_curr <> empno_prev + 1 THEN
11 SELECT x.* INTO empd FROM emp_Det x where rowid = prev_rowid;
12 dbms_output.put _line('Previous Empno:'||empd.e mpno||' Current Empno:'|| I.empno);
13 ELSE
14 dbms_output.put _line('current and prev trans equal');
15 END IF;
16 prev_rowid := I.rowid;
17 END LOOP;
18* END;
SQL> /
current and prev trans equal
Previous Empno:1 Current Empno:2
Previous Empno:2 Current Empno:3
Previous Empno:3 Current Empno:4
Previous Empno:4 Current Empno:5
Previous Empno:5 Current Empno:6
Previous Empno:6 Current Empno:12
Previous Empno:12 Current Empno:17
Previous Empno:17 Current Empno:20
Previous Empno:20 Current Empno:211
Previous Empno:211 Current Empno:1002

PL/SQL procedure successfully completed.

SQL>

[/code]

We cannot play with the cursor the way you want to in oracle but the above approach can be an alternative to what you require. Make use of ROWID and get your results.

I hope this helps!!

[vikas000000a]

You have almost got the solution of accessing or refering to the previous record by selecting a rowid in your cursor. what you just need to do further is this:

[code=oracle]

SQL> ed
Wrote file afiedt.buf

1 declare
2 cursor c1 is select rowid,emp_det.* from emp_det ORDER BY emp_det.empno;
3 empno_prev NUMBER := 0;
4 empno_curr NUMBER := 0;
5 empd emp_det%ROWTYPE ;
6 prev_rowid VARCHAR2(100) := NULL;
7 begin
8 FOR I IN C1 LOOP
9 empno_curr:= I.empno;
10 IF empno_curr <> empno_prev + 1 THEN
11 SELECT x.* INTO empd FROM emp_Det x where rowid = prev_rowid;
12 dbms_output.put _line('Previous Empno:'||empd.e mpno||' Current Empno:'|| I.empno);
13 ELSE
14 dbms_output.put _line('current and prev trans equal');
15 END IF;
16 prev_rowid := I.rowid;
17 END LOOP;
18* END;
SQL> /
current and prev trans equal
Previous Empno:1 Current Empno:2
Previous Empno:2 Current Empno:3
Previous Empno:3 Current Empno:4
Previous Empno:4 Current Empno:5
Previous Empno:5 Current Empno:6
Previous Empno:6 Current Empno:12
Previous Empno:12 Current Empno:17
Previous Empno:17 Current Empno:20
Previous Empno:20 Current Empno:211
Previous Empno:211 Current Empno:1002

PL/SQL procedure successfully completed.

SQL>

[/code]

We cannot play with the cursor the way you want to in oracle but the above approach can be an alternative to what you require. Make use of ROWID and get your results.

I hope this helps!!

Thanks Amit. It really did help me. This is exactly I was looking for.

[amitpatel66]

Thanks Amit. It really did help me. This is exactly I was looking for.

You are welcome :)

Happy programming :)