Explicit Cursor

An explicit cursor is a named pointer to a private SQL area that stores information for processing a specific SQL DML statement—usually one that returns multiple rows. You can use an explicit cursor to retrieve the rows of a result set one at a time.

You must declare and define an explicit cursor before using it. You can either declare and define it at the same time, or you can declare it first and then define it later in the same block, subprogram, or package.

Topics:

• Syntax

• Semantics

• Usage

• Examples

• Related Topics

Syntax

cursor_declaration ::=

cursor_definition ::=

See rowtype ::=.

cursor_parameter_declaration ::=

See:

• datatype ::=

• expression ::=

rowtype ::=

Semantics

cursor_declaration

Declares an explicit cursor but does not define it. The definition must appear later in the same block, subprogram, or package as the declaration.

An explicit cursor declaration is also called a cursor specification, or cursor spec.

cursor_definition

Either defines an explicit cursor that was declared earlier or both declares and defines an explicit cursor.

cursor_name

In cursor_declaration, cursor_name is the name that you are giving to the explicit cursor that you are declaring. This name can be any identifier except the reserved word SQL.

In cursor_definition and rowtype, cursor_name is the name of an explicit cursor.

cursor_variable_name

The name of a record variable.

datatype

The data type of the formal cursor parameter that you are declaring.

Restriction on datatype This datatype cannot have constraints (that is, precision and scale for a number, or length for a string).

db_table_name

The name of a database table or view that is accessible when the cursor declaration is elaborated.

expression

An expression as in "Expression". When the cursor declaration is elaborated, the value of expression is assigned to the parameter. The value and the parameter must have compatible data types.

parameter_name

The name of the formal cursor parameter that you are declaring. This IN parameter is local to the cursor. It must appear in select_statement, and can appear anywhere in select_statement that a constant can appear. When the cursor opens, select_statement uses the parameter value. For more information, see "Explicit Cursors that Accept Parameters".

record_name

The name of a record.

record_type_name

The name of a type that was defined with the data type specifier RECORD.

RETURN rowtype

Specifies the data type of the row that the cursor returns. The columns of this row must match the columns of the row that select_statement returns.

%ROWTYPE

The data type of a row of db_table_name, cursor_name, or cursor_variable_name (whichever is specified). For more information, see "%ROWTYPE Attribute".

select_statement

A SQL SELECT statement (not a PL/SQL SELECT INTO statement). If the cursor has formal parameters, each parameter must appear in select_statement. The select_statement can also reference other PL/SQL variables in its scope.

%TYPE

See "%TYPE Attribute".

Usage

After declaring an explicit cursor, you can open it (with the OPEN statement), fetch rows one at a time from the result set (with the FETCH statement), and then close it (with the CLOSE statement). After closing the cursor, you can neither fetch records from the result set nor see the cursor attribute values.

Cursors follow the same scoping rules as variables (see "Scope and Visibility of Identifiers").

You cannot assign a value to an explicit cursor or use it in an expression.

Examples

• Example 6-5, "Explicit Cursor Declaration"

• Example 6-17, "Passing Parameters to Explicit Cursors"

• Example 6-44, "FOR UPDATE Cursor in CURRENT OF Clause of UPDATE Statement"

• Example 6-46, "Trying to Fetch with FOR UPDATE Cursor After COMMIT Statement"

Related Topics

In this chapter:

• "CLOSE Statement"

• "Cursor Variable"

• "FETCH Statement"

• "Named Cursor Attribute"

• "OPEN Statement"

• "%ROWTYPE Attribute"

• "%TYPE Attribute"

In other chapters:

• "Explicit Cursors"

• "Queries"

• "SELECT FOR UPDATE and FOR UPDATE Cursors"