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:
- 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.
- If the cursor is open, %ISOPEN returns TRUE, otherwise FALSE. This property is always FALSE for an implicit cursor.
- As a boolean, %FOUND returns TRUE if the fetch operation has successfully switched to a record, and FALSE otherwise.
- 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.
