Home » PLSQL » What is Cursor in PL/SQL?

What is Cursor in PL/SQL?

In this tutorial, you will learn, what is cursor in PL/SQL and how it works.

One of the most important aspects of database programming is writing SQL in PL/SQL. All SQL statements contained within a PL/SQL block are processed as cursors.

A cursor is a private memory area used for SQL statement processing that is temporarily allocated in the session's User Global Area (UGA).

The result set retrieved from the SQL execution and cursor attributes are stored in private memory. Cursors are divided into two types: implicit and explicit.

Implicit Cursor

Oracle generates an implicit cursor for all SQL statements in a PL/SQL block's executable section. The Oracle Database manages the cursor lifecycle in this case.

Explicit Cursor

The execution cycle of explicit cursors can be controlled by the user. Along with a SELECT query, database developers can explicitly declare an implicit cursor in the DECLARE section.

The Cursor Execution Cycle in PL/SQL

Execution proceeds through the following stages as indicated by the cursor. Keep in mind that in the case of an implicit cursor, the Oracle Database handles everything automatically.

The three phases of an operation's execution are OPEN, FETCH, and CLOSE.

  • OPEN
    • Parse SQL
    • Bind SQL
    • Execute Query
  • Fetch
  • Close

OPEN

During the OPEN procedure, the session's User Global Area context is allotted for use in executing SQL statements. Statement execution follows the parsing and binding phases of SQL processing. The record pointer in a SELECT query is always set to the first record in the result set.

FETCH

The FETCH stage retrieves information from the query result set. If the result set contains multiple records, the record pointer advances incrementally with each fetch. The fetch stage is active until the last record in the result set is reached.

CLOSE

CLOSE closes the cursor, flushes the context area, and returns memory to the UGA.

Cursor Attributes

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

  1. The %ROWCOUNT variable stores the number of rows that have been fetched or affected by the most recent DML operation. Works with both DML and SELECT queries.
  2. If the cursor is open, %ISOPEN returns TRUE, otherwise FALSE. This property is always FALSE for an implicit cursor.
  3. As a boolean, %FOUND returns TRUE if the fetch operation has successfully switched to a record, and FALSE otherwise.
  4. When the cursor moves but doesn't land on a record in the results set, %NOTFOUND returns the false boolean value.

PL/SQL Cursor Example

The following code utilizes the %ISOPEN, %NOTFOUND, and %ROWCOUNT cursor attributes to retrieve and display data from the EMP table:

/*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;

Output:

***Displaying Employee Info***

Display Information for
employee:1
Employee Id:7839
Employee Name:KING

Display Information for
employee:2
Employee Id:7698
Employee Name:BLAKE

Display Information for
employee:3
Employee Id:7782
Employee Name:CLARK

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

Display Information for
employee:5
Employee Id:7788
Employee Name:SCOTT

Display Information for
employee:6
Employee Id:7902
Employee Name:FORD

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

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

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

Display Information for
employee:10
Employee Id:7654
Employee Name:MARTIN

Display Information for
employee:11
Employee Id:7844
Employee Name:TURNER

Display Information for
employee:12
Employee Id:7876
Employee Name:ADAMS

Display Information for
employee:13
Employee Id:7900
Employee Name:JAMES

Display Information for
employee:14
Employee Id:7934
Employee Name:MILLER

Statement processed.

Read also: