Get two records from PLSQL cursor

My question is short. I create a cursor to get some values from my table. I want to get the curre开发者_如何转开发nt 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?

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.

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;

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.

You can compare two variables by saving one into local and then compare it with cursor value for example

declare
l_local1 table_name%type := null;

begin

for c1 in (select 
             * 
           from 
             table_name 
           where some clause)
loop
-- do comparation
  if l_local1 is not null and c1.field_value is not null then
    -- do something to compare
     .
     .
     .
    -- empty local1 variable
    l_local1 := null;
  end if; 
  -- get the value of loop in local variable
  l_local1 := c1.field_value;
end loop;

end;