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.
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
OPENstage allocates the context area in Process Global Area (PGA) for carrying out further processing (parsing, binding, and execution) of theSELECTstatement associated with the cursor. In addition, the record pointer moves to the first record in the data set.The
FETCHstage 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
CLOSEstage closes the cursor, flushes the context area, and releases the memory back to the PGA.
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 forSELECTas well as DML statements.%ISOPEN: BooleanTRUEif the cursor is still open, elseFALSE. For an implicit cursor, it is onlyFALSE.%FOUND: BooleanTRUE, if the fetch operation switches and points to a record, elseFALSE.%NOTFOUND: BooleanFALSEwhen the cursor pointer switches but does not point to a record in the result set.
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.
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.