Table of Contents

About

A cursor in the context of Oracle.

A cursor is a SQL datatype.

A cursor is a pointer to a private SQL area that stores information about processing a specific:

  • SELECT
  • or DML statement.

The cursor data are session-specific and therefore are stored in the private SQL area. A cursor contains:

Oracle does not return Result Sets but a pointer. A cursor can be passed between:

  • the RDBMS and the client,
  • or between PL/SQL and Java in the database.

It can also be returned from a query or a stored procedure.

Child

The result of a SQL statement is a cursor.

When the same SQL text statement (the parent) is executed several time in different conditions, you will get several child cursors.

The parent cursor stores the sql text of the cursor.

different conditions can be caused by:

  • different bind variable
  • session parameter (CURSOR_SHARING,optimizer_mode, …)

Different conditions leads to a different execution plan that leads to different child cursors.

Property

Cursor variable parameters are pointers. Therefore, if a subprogram assigns one cursor variable parameter to another, they refer to the same memory location. (Aliasing in Oracle term).

Management

Initialization

A cursor is not a PL/SQL variable but a SQL one, you cannot therefore assign values to a cursor.

To access data returned by a query, you define CURSORS and REF CURSORS where:

Unlike an explicit cursor, which always refers to the same query work area, a cursor variable can refer to different work areas. You cannot use a cursor variable where a cursor is expected, or vice versa.

Parameter

A cursor can take IN parameters (the constraint NOT NULL can not be imposed) 

DECLARE
   CURSOR c1 (low  NUMBER DEFAULT 0, high NUMBER DEFAULT 99) IS
              SELECT * FROM departments WHERE department_id > low
              AND department_id < high;

Control

You use three commands to control a cursor:

  • OPEN,
  • FETCH,
  • and CLOSE

Open

Opening the cursor executes the query and retrieve the result set (all rows that meet the query) and set the pointer before the first row. The FETCH statement will retrieve the first row.

For cursors declared using the FOR UPDATE clause, the OPEN statement also locks those rows.

. 

DECLARE
   CURSOR c1 IS SELECT employee_id, last_name, job_id, salary FROM employees
      WHERE salary > 2000; 
BEGIN
  OPEN C1;

Fetch

You can:

  • execute FETCH repeatedly until all rows have been retrieved (A FETCH statement retrieve the data of the next row)
  • or use the BULK COLLECT clause to fetch all rows at once.

Close

The cursor resources are released with a CLOSE statement.

Attribute

A cursor attribute can be appended to the name of a cursor or cursor variable.

  • %FOUND,
  • %NOTFOUND, Before the first fetch from an open cursor, cursor_name%NOTFOUND returns NULL. Thereafter, it returns FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row.
  • %ISOPEN,
  • %ROWCOUNT. When a cursor is opened, %ROWCOUNT is zeroed. Before the first fetch, cursor_name%ROWCOUNT returns always 0.

    Thereafter, it returns the number of rows fetched so far. The number is incremented if the latest fetch returned a row.

Metadata Information

See:

Documentation / Reference