Introduction Cursors are constructs that enable the user to name a private memory area to hold a specific statement for access at a later time.
Every SQL statement executed by the RDBMS has a private SQL area that contains information about the SQL statement and the set of data returned. In PL/SQL, a cursor is a name assigned to a specific private SQL area for a specific SQL statement. There can be either static cursors, whose SQL, the statement is determined at compile time, or dynamic cursors, whose SQL statement is determined at runtime.
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, one can write a program to read and process the value of each such tuple. If the relation is stored, one can also update or delete the tuple at the current cursor position.
In SQL procedures, a cursor makes it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application. A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only refer one row at a time, but can move to other rows of the result set as needed.
To use cursors in SQL procedures, it is needed to do the following:
1) Declare a cursor that defines a result set.
2) Open the cursor to establish the result set.
3) Fetch the data into local variables as needed from the cursor, one row at a time.
4) Close the cursor when done.
To work with cursors one must use the following SQL statements:
1) DECLARE CURSOR,
2) OPEN,
3) FETCH,
4) CLOSE.
Program 1: Cursor | Cursor Program in PL/SQL
1) DECLARE
/* Output variables to hold the result of the query: */
2) a Ti.e%TYPE;
3) b T1 .f%TYPE;
/* Cursor declaration: */
4) CURSOR T1 Cursor 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 TlCursor INTO a, b;
/* If there are no more rows to fetch, exit the loop: */
13) EXIT WHEN TlCursor%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;
Explanations:
1) Line (I) introduces the declaration section.
2) Lines (2) and (3) declare variables a and b to have types equal to the types of attributes e and f of the relation Tl. Although these types are INTEGER, it is to make sure that whatever types they may have are copied to the PL/SQL variables.
3) Lines (4) through (8) define the cursor TlCursor. It ranges over a relation defined by the SELECT-FROM-WHERE query. That query selects those tuples of Ti whose first component is less than the second component. Line (8) declares the cursor FOR UPDATE since one will modify T1 using this cursor later on Line (14). In general, FOR UPDATE is unnecessary if the cursor will not be used for modification.
4) Line (9) begins the executable section of the program.
5) Line (10) opens the cursor, an essential step.
6) Lines (11) through (16) are a PL/SQL loop. Such a loop is bracketed by LOOP and END LOOP. Within the loop one finds: i) On Line (12), a fetch through the cursor into the local variables. In general, the FETCH statement must provide variables for each component of the tuple retrieved. Since the query of Lines (5) through (7) produces pairs, one has correctly provided two variables and it is known that they are of the correct type. ii) On Line (13), a test for the loop-breaking condition. %NOTFOUND after the name of a cursor is true exactly when a fetch through that cursor has failed to find any more tuples. iii) On Line (14), a SQL DELETE statement that deletes the current tuple using the special WHERE condition CURRENT OF TlCursor. iv) On Line (15), a SQL INSERT statement that inserts the reverse tuple into Ti.
7) Line (17) closes the cursor.
8) Line (18) ends the PL/SQL program.
9) Lines (19) and (20) cause the program to execute.
