A cursor is a pointer to a private SQL area that stores information about processing a specific SELECT or DML statement. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.
There are two types of cursors:
- Implicit cursors
- Explicit cursors
A cursor that is constructed and managed by PL/SQL is an implicit cursor. A cursor that you construct and manage is an explicit cursor.
Implicit cursors are automatically created by Oracle whenever an SQL statement is executed. We cannot control an implicit cursor, but we can get information from its attributes.
The syntax of an implicit cursor attribute value is SQL%attribute (therefore, an implicit cursor is also called a SQL cursor), it always refers to the most recently run SELECT or DML statement. If no such statement has run, the value of SQL attribute is NULL.
An implicit cursor closes once its associated statement runs and its attribute values remain available until another SELECT or DML statement runs.
To save an attribute value for later use, assign it to a local variable immediately.
The implicit cursor attributes are:
SQL%ISOPEN : Is the Cursor Open?
SQL%FOUND : Were Any Rows Affected?
SQL%NOTFOUND : Were No Rows Affected?
SQL%ROWCOUNT : How Many Rows Were Affected?
SQL%BULK_ROWCOUNT : Getting Number of Rows Affected by FORALL Statement
SQL%BULK_EXCEPTIONS : Handling FORALL Exceptions After FORALL Statement Completes
You can get the number of rows that each DML statement affected from the implicit cursor attribute SQL%BULK_ROWCOUNT.
Showing Number of Rows Affected by Each DELETE in FORALL
An explicit cursor is a session cursor that you construct and manage. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. Then you can process the query result set in either of these ways:
- Open the explicit cursor with the OPEN statement, fetch rows from the result set with the FETCH statement, and close the explicit cursor with the CLOSE statement.
- Use the explicit cursor in a cursor FOR LOOP statement
Declaring the Cursor
Declaring the cursor defines the cursor with a name and the associated SELECT statement.
CURSOR cursor_name [ parameter_list ] [ RETURN return_type ] IS select_statement;
Cursor with parameters
CURSOR cursor_name (parameter_list) IS SELECT_statement;
A cursor with a return clause
CURSOR cursor_name RETURN field%ROWTYPE IS SELECT_statement;
Opening the Cursor
After declaring and defining an explicit cursor, you can open it with the OPEN statement.
Fetching Data with Explicit Cursors
After opening an explicit cursor, you can fetch the rows of the query result set with the FETCH statement.
FETCH cursor_name INTO into_clause;
Closing the Cursor
Closing the cursor means releasing the allocated memory.
Fetching Same Explicit Cursor into Different Variables
Explicit Cursor FOR LOOP Statement
Subquery in FROM Clause of Parent Query