PL/SQL - Cursor

Card Puncher Data Processing

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





Discover More
Card Puncher Data Processing
Oracle Database - (Actual|Final) Execution Plan

The actual or final plan is the execution that was executed in order to retrieve the result of a SQL. optimizerquery planrow source generator V view contains the actual plan for a query that...
Oracle Database Sga
Oracle Database - Session (Library|Cursor) Cache

The library cache is a part of the shared pool Private Sql Area Also known as: Session cursor cache. V contains the execution plan information for each child cursor loaded in the library...
Oracle Database Sga
Oracle Database - Shared sql area

The Shared sql area is an area in the library cache that contains: the cached SQL cursors the actual plan the parse tree for a SQL statement. Only one shared SQL area exists for a unique statement....
Card Puncher Data Processing
Oracle Database - V$SQL_PLAN_STATISTICS

V provides execution statistics at the row source level for each child cursor. STATISTICS_LEVEL Join Column Equality Predicate Join...
Card Puncher Data Processing
Oracle Database - V$SQL_WORKAREA

V displays information work areas used by SQL cursors. The primary key for the view is WORKAREA_ADDRESS, the memory Address...
Card Puncher Data Processing
PL/SQL - OPEN-FOR, FETCH, and CLOSE statements

The OPEN-FOR statement executes the query associated with a cursor variable. It's an important statement of the dynamic sql Management. It allocates database resources to process the query and identifies...
Card Puncher Data Processing
PL/SQL - (Nested) Record

Records are simply a row representation in PL/SQL. Records (as rows) contain uniquely named fields (column name for row), which can have different datatypes whereas collection contains elements of the...
Card Puncher Data Processing
PL/SQL - (Procedure Language|PL) SQL

PlSql is the development language of the oracle database. SQL was designed from the start to operate on SETS (ie parallel task) whereas PL/SQL brought a lot in terms of exception handling. PL/SQL...
Card Puncher Data Processing
PL/SQL - Cursor Variables (also known as REF CURSORs)

With 7.2 on up of the database you have cursor variables. Cursor variables are cursors opened by a pl/sql routine and fetched from by another application or pl/sql routine. The cursor variables are...
Card Puncher Data Processing
PL/SQL - Explicit cursor

Unlike an cursor variable, which refers to different work areas, a explicit cursor refer always to the same query. where: Record with Cursor



Share this page:
Follow us:
Task Runner