Cursors – an overview

Writing SQL in PL/SQL is one of the critical parts of database programming. All SQL statements embedded within a PL/SQL block are executed as a cursor. A cursor is a private memory area, temporarily allocated in the session's User Global Area (UGA), that is used for processing SQL statements. The private memory stores the result set retrieved from the SQL execution and cursor attributes. Cursors can be classified as implicit and explicit cursors.

Oracle creates an implicit cursor for all the SQL statements included in the executable section of a PL/SQL block. In this case, the cursor lifecycle is maintained by the Oracle Database.

For explicit cursors, the execution cycle can be controlled by the user. Database developers can explicitly declare an implicit cursor under the DECLARE section along with a SELECT query.

The cursor execution cycle

A cursor moves through the following stages during execution. Note that, in the case of an implicit cursor, all the steps are carried out by the Oracle Database. Let's take a quick look at the execution stages OPEN, FETCH, and CLOSE.

• The OPEN stage allocates the context area in the session's User Global Area for performing SQL processing. The SQL processing starts with parsing and binding, followed by statement execution. In the case of the SELECT query, the record pointer points to the first record in the result set.

• The FETCH stage pulls the data from the query result set. If the result set is a multi-record set, the record pointer moves incrementally with every fetch. The fetch stage is alive 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 UGA.

Cursor attributes

Cursor attributes hold the information about the cursor processing at each stage of its execution:

• %ROWCOUNT: Number of rows fetched until the last fetch or impacted by the last DML operation. Applicable for SELECT as well as DML statements.

• %ISOPEN: Boolean TRUE if the cursor is still open, if not FALSE. For an implicit cursor, this attribute is always FALSE.

• %FOUND: Boolean TRUE, if the fetch operation switches and points to a record; if not, 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 that is accessible outside the cursor execution cycle.

The following program uses the cursor attributes %ISOPEN, %NOTFOUND, and %ROWCOUNT to fetch the data from the EMP 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 EMP;
   L_EMPNO EMP.EMPNO%TYPE;
   L_ENAME EMP.ENAME%TYPE;
BEGIN

/*Check if the cursor is already open*/
   IF NOT C_EMP%ISOPEN THEN
     DBMS_OUTPUT.PUT_LINE('***Displaying Employee Info***');
   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;
/

***Displaying Employee Info***

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

Looping through all the records of a cursor object can be facilitated with the use of the FOR loop. A FOR loop opening a cursor directly is known as a CURSOR FOR loop. The usage of the CURSOR FOR loop reduces the overhead of manually specifying the OPEN, FETCH, and CLOSE stages of a cursor.

The CURSOR FOR loop will best compact the code when working with multi-row explicit cursors. The following PL/SQL block demonstrates the purpose:

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SET SERVEROUTPUT ON

/*Start the PL/SQL block*/
DECLARE
/*Declare an explicit cursor to select employee information*/
   CURSOR CUR_EMP IS
      SELECT ename, sal
      FROM emp;
BEGIN
/*FOR Loop uses the cursor CUR_EMP directly*/
   FOR EMP IN CUR_EMP
   LOOP
/*Display message*/
   DBMS_OUTPUT.PUT_LINE(EMP.ename||' earns '||EMP.sal||' per month');
   END LOOP;
END;
/

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

PL/SQL procedure successfully completed.

Note that, with the CURSOR FOR loop, you do not need to declare the block variables to capture the cursor columns. The CURSOR FOR loop index implicitly acts as a record of the cursor type. Also, you do not need to explicitly open or close the cursor in the PL/SQL program.