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. The program will delete every tuple whose first component is less than the second, and insert the reverse tuple into T1.

There are two types of cursors in PL/SQL:
Implicit cursors:
These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.

Explicit cursors:
They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.

Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.

1) DECLARE
/* Output variables to hold the result of the query: */
2)     a T1.e%TYPE;
3)     b T1.f%TYPE;
/* Cursor declaration: */
4)     CURSOR T1Cursor IS
5)         SELECT e, f
6)         FROM T1
7)         WHERE e < f
8)         FOR UPDATE;
9) BEGIN
10)     OPEN T1Cursor;
11)     LOOP
/* Retrieve each row of the result of the above query
into PL/SQL variables: */
12)         FETCH T1Cursor INTO a, b;
/* If there are no more rows to fetch, exit the loop: */
13)         EXIT WHEN T1Cursor%NOTFOUND;
/* Delete the current tuple: */
14)         DELETE FROM T1 WHERE CURRENT OF T1Cursor;
/* Insert the reverse tuple: */
15)         INSERT INTO T1 VALUES(b, a);
16)     END LOOP;
/* Free cursor used by the query. */
17)     CLOSE T1Cursor;
18) END;
19) .
20) run;