PL/SQL - Cursor Overview

The SQL statements embedded within a PL/SQL block are executed as a cursor. A cursor is a private memory area, which is allocated in the session's User Global Area (UGA).

Cursors can be classified as implicit and explicit cursors.

Implicit cursor is created by Oracle itself for all the SQL statements in executable section of a PL/SQL block. In this case, the cursor lifecycle is maintained by the Oracle Database.

The execution cycle of Explicit cursor can be controlled by the user. Database developers can explicitly declare an implicit cursor under the DECLARE section along with a SELECT query.

The cursor execution cycle

A cursor goes through the following stages during execution.

• The OPEN stage - a context area in the session's User Global Area is allocated, SQL statements are parsed and bounded. In case of the SELECT query, the record pointer points to the first record in the result set.
• The FETCH stage - fetches the data from the query result set. The record pointer moves incrementally towrads next record in case of multi-record set on every fetch. Cursor will be in fetch stage until the last record is reached in the result set.
• The CLOSE stage - closes the cursor, flushes the context area, and releases the memory back to the UGA.

Note that, in the case of an implicit cursor, all the steps are carried out by the Oracle Database.

Cursor attributes

You can find information about the cursor stages execution by looking into Cursor attributes:

• %ROWCOUNT: Applicble for Select and DML statements. It denotes the number of rows fetched until the last fetch(in case of SELECT) or impacted by the last DML operation.
• %ISOPEN: If the cursor is still open it return Boolean TRUE, otherwise FALSE. For an implicit cursor, this attribute is always FALSE.
• %FOUND: If the fetch operation switches and points to a record, it returns Boolean TRUE, otherwise FALSE.
• %NOTFOUND: Opposite to %FOUND attribute. Returns TRUE when the cursor pointer switches but does not point to a record in the result set.

Only %ISOPEN is the attribute that is accessible outside the execution cylce. Remaining three attributes you could not access outside.

Related Post(s):

• PL/SQL - Cursor Attributes and Loop
• PL/SQL - Cursor FOR loop