Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)

Part Number E10472-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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.

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 ::=

cursor_spec
Description of the illustration cursor_declaration.gif

cursor_definition ::=

cursor_declaration
Description of the illustration cursor_definition.gif

See rowtype ::=.

cursor_parameter_declaration ::=

cursor_parameter_declaration
Description of the illustration cursor_param_declaration.gif

See:

rowtype ::=

rowtype
Description of the illustration rowtype.gif

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.

Note:

If you supply an actual parameter for parameter_name when you open the cursor, then expression is not evaluated.

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.

See:

Oracle Database SQL Language Reference for SELECT statement syntax

%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

Related Topics

In this chapter:

In other chapters: