About Cursors

When Oracle Database executes a SQL statement, it stores the result set and processing information in an unnamed private SQL area. A pointer to this unnamed area, called a cursor, lets you retrieve the rows of the result set one at a time. Cursor attributes return information about the state of the cursor.

Every time you run either a SQL DML statement or a PL/SQL SELECT INTO statement, PL/SQL opens an implicit cursor. You can get information about this cursor from its attributes, but you cannot control it. After the statement runs, the database closes the cursor; however, its attribute values remain available until another DML or SELECT INTO statement runs.

PL/SQL also lets you declare explicit cursors. An explicit cursor has a name and is associated with a query (SQL SELECT statement)—usually one that returns multiple rows. After declaring an explicit cursor, you must open it (with the OPEN statement), fetch rows one at a time from the result set (with the FETCH statement), and close the cursor (with the CLOSE statement). After closing the cursor, you can neither fetch records from the result set nor see the cursor attribute values.

The syntax for the value of an implicit cursor attribute is SQLattribute (for example, SQL%FOUND). SQLattribute always refers to the most recently run DML or SELECT INTO statement.

The syntax for the value of an explicit cursor attribute is cursor_name immediately followed by attribute (for example, c1%FOUND).

Table: Cursor Attribute Values lists the cursor attributes and the values that they can return. (Implicit cursors have additional attributes that are beyond the scope of this book.)

Cursor Attribute Values

Attribute	Values for Explicit Cursor	Values for Implicit Cursor
`%FOUND`	If cursor is not open, `INVALID_CURSOR`. If cursor is open but no fetch was attempted, `NULL`. If the most recent fetch returned a row, `TRUE`. If the most recent fetch did not return a row, `FALSE`.	If no DML or `SELECT` `INTO` statement has run, `NULL`. If the most recent DML or `SELECT` `INTO` statement returned a row, `TRUE`. If the most recent DML or `SELECT` `INTO` statement did not return a row, `FALSE`.
`%NOTFOUND`	If cursor is not open, `INVALID_CURSOR`. If cursor is open but no fetch was attempted, `NULL`. If the most recent fetch returned a row, `FALSE`. If the most recent fetch did not return a row, `TRUE`.	If no DML or `SELECT` `INTO` statement has run, `NULL`. If the most recent DML or `SELECT` `INTO` statement returned a row, `FALSE`. If the most recent DML or `SELECT` `INTO` statement did not return a row, `TRUE`.
`%ROWCOUNT`	If cursor is not open, `INVALID_CURSOR`; otherwise, a number greater than or equal to zero.	`NULL` if no DML or `SELECT` `INTO` statement has run; otherwise, a number greater than or equal to zero.
`%ISOPEN`	If cursor is open, `TRUE`; if not, `FALSE`.	Always `FALSE`.

About Cursors

Related Topics