Cursors

A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query. By fetching into the cursor each tuple of the relation, we can write a program to read and process the value of each such tuple. If the relation is stored, we can also update or delete the tuple at the current cursor position.

The example below illustrates a cursor loop. It uses our example relation T1(e, f) whose tuples are pairs of integers: 
     SQL> CREATE TABLE  T1 (
       2    e  INTEGER,
       3    f  INTEGER );
The program will delete every tuple whose first component is less than the second, and insert the reverse tuple into T1. 

DECLARE
  /* Output variables to hold the result of the query: */
  a  T1.e%type;
  b  T1.f%type;
  /* Cursor declaration: */
  CURSOR  T1Cursor  IS SELECT  e, f  FROM  T1  WHERE  e < f  FOR UPDATE;
BEGIN
  OPEN  T1Cursor;
  LOOP
    /* Retrieve each row of the result of the above query into PL/SQL variables: */
    FETCH  T1Cursor  INTO  a, b;
    /* If there are no more rows to fetch, exit the loop: */
    EXIT WHEN  T1Cursor%NOTFOUND;
    /* Delete the current tuple: */
    DELETE FROM  T1  WHERE CURRENT OF  T1Cursor;
    /* Insert the reverse tuple: */
    INSERT INTO  T1  VALUES( b, a );
  END LOOP;
  /* Free cursor used by the query. */
  CLOSE  T1Cursor;
END;