Explicit Cursor

An explicit cursor is a named pointer to a private SQL area that stores information for processing a specific SQL database manipulation language (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.

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

Topics:

• Syntax

• Semantics

• Usage

• Examples

• Related Topics

Syntax

cursor_declaration ::=

cursor_definition ::=

See rowtype ::=.

cursor_parameter_declaration ::=

See:

• datatype ::=

• expression ::=

rowtype ::=

Semantics

cursor_declaration

cursor_name

The name of the explicit cursor that you are declaring now and will define later in the same block, subprogram, or package. This name can be any identifier except the reserved word SQL.

rowtype

The data type of the row that the cursor returns.

cursor_definition

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

cursor_name

Either the name of the explicit cursor that you previously declared and are now defining or the name of the explicit cursor that you are both declaring and defining.

rowtype

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.

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.

cursor_parameter_declaration

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. In select_statement, parameter_name can appear anywhere that a constant can appear. When the cursor opens, select_statement uses the parameter value. For more information, see "Explicit Cursors that Accept Parameters".

datatype

The data type of the parameter.

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

expression

The default value of the parameter. If you supply an actual parameter for parameter_name when you open the cursor, then expression is not evaluated. Otherwise, when the cursor declaration is elaborated, the value of expression is assigned to the parameter. The value of expression must have a data type that is compatible with datatype (see Table 3-10, "Possible Implicit PL/SQL Data Type Conversions").

rowtype

db_table_name

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

cursor_name

The name of another explicit cursor (not the name of the cursor that you are declaring or defining).

cursor_variable_name

The name of a cursor variable.

record_name

The name of a record.

record_type_name

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

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"