Overview on Implicit and Explicit Cursors in PL/SQL

PL/SQL supports two types of cursors. Implicit Cursor and Explicit cursors based on whether the cursor is user defined or system defined. Cursors is a temporary work area created in the system memory to store results of a SQL statement and are used by oracle to manipulate data when many rows are fetched from a query.

The rows held by the cursor are called the active set. The Implicit Cursor is the cursor that Oracle internally opens when a SELECT..INTO that returns a single row or any DML statement like UPDATE, DELETE or INSERT is issued. Explicit cursor is defined in PL/SQL program by the programmer and used when more than one row is returned by a query.

The Implicit cursor is so called because Oracle itself carries out the cursor operations such as OPEN, FETCH and CLOSE operations. In the case of an UPDATE, DELETE or INSERT statement PL/SQL automatically provides an implicit cursor. In case of SELECT statements PL/SQL employs implicit cursors for statements that return a single row. In the case when an implicit SELECT statement returns more than one row the TOO_MANY_ROWS exception is raised. The exception to this is the usage of SELECT BULK COLLECT INTO statement that allows multiple rows of data to be retrieved with a single implicit query. For a DML statement such as given in below example,

UPDATE EMP_TBL SET EMP_SAL = 1000 where EMP_ID = 101;

PL/SQL automatically creates an implicit cursor to identify the rows in table changed by the UPDATE.

The key limitations of an implicit cursor is low efficiency, low programmatic control and leading to error prone code. The reason for the low efficiency is that in PL/SQL version 2.2 and earlier , Oracle ‘s SQL followed ANSI standard which enforced a single row query to perform two fetches, one to return the desired row and the second to check if query returns more than one row.

This has been tried to be optimized in later versions of PL/SQL but the increase in efficiency of the implicit cursor is insignificant. We cannot rely on the implicit cursor that is used to retrieve a single row fully as any changes in data can lead to exceptions being raised. The two common exceptions are the NO_DATA_FOUND exception when no rows match the specified criteria and TOO_MANY_ROWS exception when more than one row is retrieved in this kind of SELECT statement. The open, fetch and close operations of the implicit cursor cannot be manipulated leading to low programmatic control.

Explicit cursors are defined in the declaration part of PL/SQL block, sub program or package. They allow data to be fetched from the table and manipulated one row at a time. They need to be declared, opened, fetched and closed.
The general syntax is as shown below,

CURSOR cursor_name [ ( [ parameter [, parameter ...] ) ]
[ RETURN return_specification ]
IS SELECT_statement;

Where cursor_name is the name of the cursor. By option, parameters can also be passed to cursors and return data type can also be specified in cursor definition.

An explicit cursor has to be opened before fetching data from it. The syntax is
OPEN <cursor_name> [ ( argument [, argument ...] ) ];

Arguments are the values to be passed if the cursor was declared with a parameter list.

A cursor can be opened only in execution or exception sections of the block.

The FETCH statement is used to retrieve rows from the cursor. The Fetch statement should fetch the data into compatible data type variables.
The general syntax is

FETCH <cursor_name> INTO <record_or_variable_list>;

Here cursor_name is the name of the cursor from which data is fetched and record or variable_list is the data structure to which data is retrieved. The record is generally declared with a %ROWTYPE attribute or TYPE declaration.

The explicit cursor can be closed by the statement
CLOSE cursor_name;
Where cursor_name is the name of the cursor that is being closed.

Both implicit and explicit cursors have four attributes which reports the status of the latest FETCH executed against a cursor. These are %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN. %FOUND attribute returns value of TRUE if record was fetched successfully else it returns a value of FALSE.

%NOTFOUND attribute returns value of TRUE if record was not fetched successfully else it returns a value of FALSE. %ROWCOUNT attribute is used to determine the number of rows fetched from the cursor. %ISOPEN returns value of TRUE if the cursor is currently open else it returns FALSE.

Explicit cursors should be the preferred mode of use as it allows the programmer to exercise complete control on how the information is accessed. The various cursor operations are also under programmatic control. There is less vulnerability to data errors.

| Characterization of Materialized views and its types in Oracle10g | Introduction to Capability Maturity Model (CMM) | Introduction to Data Blocks, Extents and Segments –Logical Storage Structures in Oracle 10g Server | Oracle Application Server10g – Business Intelligence Services | Oracle Application Server10g – Content Management Services | Overview of Commit and Rollback – The Transaction Control Statements in Oracle 10g | Overview of Flashback Features in Oracle10g | Overview on Database Change Notification in Oracle10g | Overview on Implicit and Explicit Cursors in PL/SQL | Overview on Sequential Control and Conditional Control Statements in PL/SQL | Overview on some of the new features in Oracle Database 10g | Overview on the usage of Cursor Variables in PL/SQL | Performance Tuning in Oracle10g – Best Practices in Application Development | Representing Conditional Expressions as Data in Oracle10g | Understanding Indexes and their key benefits in Oracle10g | Understanding Oracle10g Application Server –Portal Services |