Get two records from PLSQL cursor

Question

My question is short. I create a cursor to get some values from my table. I want to get the current record of cursor (the fetched record) and the next record from the cursor without fetching it, because I want to make a calculation over the current record and the next record. In traditional programming it's a simple operation; you can do it with a for index by increasing it by 1.

Do you have any suggestions?

Answer 1

I'm not sure you can do that without moving the cursor, but you should be able to accomplish the same goal like this (psuedocode):

open cursor;
fetch cursor to rec1;
if cursor%found then
   loop      
      fetch cursor to rec2;
      exit when cursor%notfound;
      perform calculations with rec1 and rec2;
      rec1 := rec2;
   end loop;
end if;

Answer 2

I want to make a calculation over the current record and the next record.

Presuming you are using Oracle, this can be done quite simply in SQL using analtyical functions, specifically the lead() function. This retrieves a column's value from the next nth record (default n=1).

SQL> select empno
  2         , sal as this_sal
  3         , lead(sal) over (order by empno) as next_sal
  4  from emp
  5  order by empno
  6  /

     EMPNO   THIS_SAL   NEXT_SAL
---------- ---------- ----------
      7369        800       1600
      7499       1600       1250
      7521       1250       2975
      7566       2975       1250
      7654       1250       2850
      7698       2850       2450
....

This query can be using in a cursor or any other mechanism for retrieving records.

Answer 3

To answer you specific question, you could use BULK COLLECT and its limit clause into a collection (table) variable.

DECLARE
  CURSOR my_cursor IS
    [YOUR SQL HERE];

  TYPE t_my_type IS TABLE OF my_cursor%ROWTYPE INDEX BY BINARY_INTEGER;
  v_my_var   t_my_type;
BEGIN
  FETCH my_cursor BULK COLLECT INTO v_my_var LIMIT 2;
  dbms_output.put_line('My first value: ' || v_my_var(1).some_column);
  dbms_output.put_line('My second value: ' || v_my_var(2).some_column);
END;

The limit clause will cause only the first two records to be fetched and stored. The first record will have an index of 1 and the second will be 2.