Summary: In this tutorial, you will learn about the PL/SQL cursor and its usage.
A cursor is a pointer that points to the result of a query. PL/SQL has two types of cursors:
- Implicit cursors.
- Explicit cursors.
Implicit cursors #
Whenever Oracle executes an SQL statement such as SELECT INTO, INSERT, UPDATE, and DELETE, it automatically creates an implicit cursor.
Oracle internally manages the whole execution cycle of implicit cursors and reveals only the cursor’s information and statuses, such as SQL%ROWCOUNT, SQL%ISOPEN, SQL%FOUND, and SQL%NOTFOUND.
The implicit cursor is not elegant when the query returns zero or multiple rows, which causes NO_DATA_FOUND or TOO_MANY_ROWS exception respectively.
Explicit cursors #
An explicit cursor is a SELECT statement declared explicitly in the declaration section of the current block or a package specification.
For an explicit cursor, you have control over its execution cycle from OPEN, FETCH, and CLOSE.
Oracle defines an execution cycle that executes an SQL statement and associates a cursor with it.
The following illustration shows the execution cycle of an explicit cursor: