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.

view source
print?
01DECLARE
02  /* Output variables to hold the result of the query: */
03  a  T1.e%type;
04  b  T1.f%type;
05  /* Cursor declaration: */
06  CURSOR  T1Cursor  IS SELECT  e, f  FROM  T1  WHERE  e < f  FOR UPDATE;
07BEGIN
08  OPEN  T1Cursor;
09  LOOP
10    /* Retrieve each row of the result of the above query into PL/SQL variables: */
11    FETCH  T1Cursor  INTO  a, b;
12    /* If there are no more rows to fetch, exit the loop: */
13    EXIT WHEN  T1Cursor%NOTFOUND;
14    /* Delete the current tuple: */
15    DELETE FROM  T1  WHERE CURRENT OF  T1Cursor;
16    /* Insert the reverse tuple: */
17    INSERT INTO  T1  VALUES( b, a );
18  END LOOP;
19  /* Free cursor used by the query. */
20  CLOSE  T1Cursor;
21END;




      A recent immigrant from Poland needed a drivers license, and went to the state licensing bureau.    
      When it was time for his eye test, the examiner asked him to read a chart of small letters:
W C Z E W T O S I C Z
      “Can you see that clearly and read it back to me?”    
      “Can I read it?” said the immigrant. “That guy is my best friend.”