Cursors—an overview

Cursors make a concrete conceptual ground for database professionals. In simple words, a cursor is a memory pointer to a specific private memory location where a SELECT statement is processed. This memory location is known as a context area.

Every SQL statement in a PL/SQL block can be realized as a cursor. The context area is the memory location which records the complete information about the SQL statement currently under process. The processing of the SQL statement in this private memory area involves its parsing, data fetch, and retrieval information. The data retrieved should be pulled into local variables and, henceforth, used within the program.

On the basis of their management, cursors are classified as implicit and explicit cursors.

The Oracle server is fully responsible for the complete execution cycle of an implicit cursor. Oracle implicitly creates a cursor for all SQL statements (such as SELECT, INSERT, UPDATE, and DELETE) within the PL/SQL blocks.

For explicit cursors, the execution cycle is maneuvered by database programmers. Explicit cursors are meant only for the SELECT statements which can fetch one or more rows from the database. The developers have the complete privilege and control to create a cursor, fetch data iteratively, and close the cursor.

Cursor execution cycle

Let us have a quick tour through the cursor management and execution cycle. Note that this execution cycle starts after the cursor has been prototyped in the declarative section:

• The OPEN stage allocates the context area in Process Global Area (PGA) for carrying out further processing (parsing, binding, and execution) of the SELECT statement associated with the cursor. In addition, the record pointer moves to the first record in the data set.

• The FETCH stage pulls the data from the query result set. If the result set is a multi-record set, the pointer increments with every fetch. The Fetch stage is live until the last record is reached in the result set.

• The CLOSE stage closes the cursor, flushes the context area, and releases the memory back to the PGA.

Cursor attributes

The cursor attributes, which carry important information about the cursor processing at each stage of their execution, are as follows:

• %ROWCOUNT: Number of rows returned/changed in the last executed query. Applicable for SELECT as well as DML statements.

• %ISOPEN: Boolean TRUE if the cursor is still open, else FALSE. For an implicit cursor, it is only FALSE.

• %FOUND: Boolean TRUE, if the fetch operation switches and points to a record, else FALSE.

• %NOTFOUND: Boolean FALSE when the cursor pointer switches but does not point to a record in the result set.

Note

%ISOPEN is the only cursor attribute which is accessible outside the cursor execution cycle.

We will illustrate the usage of cursor attributes with a simple PL/SQL program. The following program implements the %ISOPEN, %NOTFOUND, and %ROWCOUNT attributes to iterate the employee data from the EMPLOYEES table and display it:

/*Enable the SERVEROUTPUT to display block messages*/
SET SERVEROUTPUT ON

/*Start the PL/SQL Block*/
DECLARE

/*Declare a cursor to select employees data*/
   CURSOR C_EMP IS
      SELECT EMPNO,ENAME
	   FROM EMPLOYEES;
   L_EMPNO EMPLOYEES.EMPNO%TYPE;
   L_ENAME EMPLOYEES.ENAME%TYPE;
BEGIN
/*Check if the cursor is already open*/
   IF NOT C_EMP%ISOPEN THEN
     DBMS_OUTPUT.PUT_LINE('Cursor is closed....Cursor has to be opened');
   END IF;
/*Open the cursor and iterate in a loop*/
   OPEN C_EMP;
   LOOP
/*Fetch the cursor data into local variables*/
   FETCH C_EMP INTO L_EMPNO, L_ENAME;
   EXIT WHEN C_EMP%NOTFOUND;
/*Display the employee information*/
      DBMS_OUTPUT.PUT_LINE(chr(10)||'Display Information for employee:'||C_EMP%ROWCOUNT);
      DBMS_OUTPUT.PUT_LINE('Employee Id:'||L_EMPNO);
      DBMS_OUTPUT.PUT_LINE('Employee Name:'||L_ENAME);
   END LOOP;
END;
/

Cursor is closed....Cursor has to be opened

Display Information for employee:1
Employee Id:7369
Employee Name:SMITH

Display Information for employee:2
Employee Id:7499
Employee Name:ALLEN

Display Information for employee:3
Employee Id:7521
Employee Name:WARD

Display Information for employee:4
Employee Id:7566
Employee Name:JONES
….

PL/SQL procedure successfully completed.

Cursor FOR loop

The iterative construct, FOR loop, can be aligned to the cursor execution cycle. The benefit is that the cursor can be directly accessed without physically opening, fetching, or closing the cursor. In addition, it reduces the overhead of declaring local identifiers. The stages are handled implicitly by the FOR loop construct.

The cursor FOR loop qualifies for the best programming practices where the cursor carries multi-row set. The following program demonstrates the working of a cursor FOR loop:

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SQL> SET SERVEROUTPUT ON
/*Start the PL/SQL block*/
DECLARE
/*Declare an explicit cursor to select employee name and salary*/
   CURSOR CUR_EMP IS
      SELECT ENAME, SAL
      FROM EMPLOYEES;
BEGIN
/*FOR Loop uses the cursor CUR_EMP directly*/
   FOR EMP IN CUR_EMP
   LOOP 
/*Display appropriate message*/
   DBMS_OUTPUT.PUT_LINE('Employee '||EMP.ENAME||' earns '||EMP.SAL||' per month');
   END LOOP;
END;
/

Employee SMITH earns 800 per month
Employee ALLEN earns 1600 per month
Employee WARD earns 1250 per month
Employee JONES earns 2975 per month
Employee MARTIN earns 1250 per month
Employee BLAKE earns 2850 per month
Employee CLARK earns 2450 per month
Employee SCOTT earns 3000 per month
Employee KING earns 5000 per month
Employee TURNER earns 1500 per month
Employee ADAMS earns 1100 per month
Employee JAMES earns 950 per month
Employee FORD earns 3000 per month
Employee MILLER earns 1300 per month

PL/SQL procedure successfully completed.