Explicit Cursor Declaration and Definition

You can use an explicit cursor to retrieve the rows of a result set one at a time.

Before using an explicit cursor, you must declare and define it. You can either declare it first (with cursor_declaration) and then define it later in the same block, subprogram, or package (with cursor_definition) or declare and define it at the same time (with cursor_definition).

An explicit cursor declaration and definition are also called a cursor specification and cursor body, respectively.

Note:

An explicit cursor declared in a package specification is affected by the AUTHID clause of the package. For more information, see "CREATE PACKAGE Statement".

Topics

Syntax

cursor_declaration ::=

Description of the illustration cursor_declaration.eps

cursor_definition ::=

Description of the illustration cursor_definition.eps

cursor_parameter_dec ::=

Description of the illustration cursor_parameter_dec.eps

rowtype ::=

Description of the illustration rowtype.eps

Semantics

cursor_declaration

cursor

Name of the explicit cursor that you are declaring now and will define later in the same block, subprogram, or package. cursor can be any identifier except the reserved word SQL. Oracle recommends against giving a cursor the same name as a database table.

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

cursor_definition

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

cursor

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. cursor can be any identifier except the reserved word SQL. Oracle recommends against giving a cursor the same name as a database table.

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.

Restriction on select_statement

This select_statement cannot have a WITH clause.

See:

Oracle Database SQL Language Reference for SELECT statement syntax

cursor_parameter_dec

A cursor parameter declaration.

parameter

The name of the formal cursor parameter that you are declaring. This name can appear anywhere in select_statement that a constant can appear.

IN

Whether or not you specify IN, a formal cursor parameter has the characteristics of an IN subprogram parameter, which are summarized in Table 8-1. When the cursor opens, the value of the formal parameter is that of either its actual parameter or default value.

datatype

The data type of the parameter.

Restriction on datatype

This datatype cannot have constraints (for example, NOT NULL, or precision and scale for a number, or length for a string).

expression

Specifies the default value for the formal cursor parameter. The data types of expression and the formal cursor parameter must be compatible.

If an OPEN statement does not specify an actual parameter for the formal cursor parameter, then the statement evaluates expression and assigns its value to the formal cursor parameter.

If an OPEN statement does specify an actual parameter for the formal cursor parameter, then the statement assigns the value of the actual parameter to the formal cursor parameter and does not evaluate expression.

rowtype

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.

db_table_or_view

Name of a database table or view, which must be accessible when the declaration is elaborated.

cursor

Name of a previously declared explicit cursor.

cursor_variable

Name of a previously declared cursor variable.

record

Name of a previously declared record variable.

record_type

Name of a user-defined type that was defined with the data type specifier RECORD.

Examples

Example 6-5, "Explicit Cursor Declaration and Definition"
Example 6-8, "Variable in Explicit Cursor Query—No Result Set Change"
Example 6-9, "Variable in Explicit Cursor Query—Result Set Change"
Example 6-10, "Explicit Cursor with Virtual Column that Needs Alias"
Example 6-11, "Explicit Cursor that Accepts Parameters"
Example 6-12, "Cursor Parameters with Default Values"
Example 6-13, "Adding Formal Parameter to Existing Cursor"
Example 6-22, "Subquery in FROM Clause of Parent Query"
Example 6-23, "Correlated Subquery"
Example 6-35, "CURSOR Expression"
Example 6-41, "FETCH with FOR UPDATE Cursor After COMMIT Statement"