Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 1 (11.1)

B28370-05
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
PDF · Mobi · ePub

Explicit Cursor

An explicit cursor names the unnamed work area in which the database stores processing information when it executes a multiple-row query. When you have named the work area, you can access its information, and process the rows of the query individually.

Syntax

cursor_declaration ::=

cursor_declaration
Description of the illustration cursor_declaration.gif

(cursor_parameter_declaration ::=, rowtype ::=)

cursor_spec ::=

cursor_spec
Description of the illustration cursor_spec.gif

(cursor_parameter_declaration ::=, rowtype ::=)

cursor_body ::=

cursor_body
Description of the illustration cursor_body.gif

cursor_parameter_declaration ::=

cursor_parameter_declaration
Description of the illustration cursor_param_declaration.gif

(expression ::=)

rowtype ::=

rowtype
Description of the illustration rowtype.gif

Keyword and Parameter Descriptions

cursor_name

An explicit cursor previously declared within the current scope.

datatype

A type specifier. For the syntax of datatype, see Constant.

db_table_name

A database table or view that must be accessible when the declaration is elaborated.

expression

A combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the 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

A variable declared as the formal parameter of a cursor. A cursor parameter can appear in a query wherever a constant can appear. The formal parameters of a cursor must be IN parameters. The query can also reference other PL/SQL variables within its scope.

record_name

A user-defined record previously declared within the current scope.

record_type_name

A user-defined record type that was defined using the data type specifier RECORD.

RETURN

Specifies the data type of a cursor return value. You can use the %ROWTYPE attribute in the RETURN clause to provide a record type that represents a row in a database table or a row returned by a previously declared cursor. Also, you can use the %TYPE attribute to provide the data type of a previously declared record.

A cursor body must have a SELECT statement and the same RETURN clause as its corresponding cursor specification. Also, the number, order, and data types of select items in the SELECT clause must match the RETURN clause.

%ROWTYPE

A record type that represents a row in a database table or a row fetched from a previously declared cursor or cursor variable. Fields in the record and corresponding columns in the row have the same names and data types.

select_statement

A SQL SELECT statement. If the cursor declaration declares parameters, each parameter must appear in select_statement.

See:

Oracle Database SQL Language Reference for SELECT statement syntax

%TYPE

Provides the data type of a previously declared user-defined record.

Usage Notes

You must declare a cursor before referencing it in an OPEN, FETCH, or CLOSE statement.

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".

You must declare a variable before referencing it in a cursor declaration. The word SQL is reserved by PL/SQL as the default name for SQL cursors, and cannot be used in a cursor declaration.

You cannot assign values to a cursor name or use it in an expression. However, cursors and variables follow the same scoping rules. For more information, see Scope and Visibility of PL/SQL Identifiers.

You retrieve data from a cursor by opening it, then fetching from it. Because the FETCH statement specifies the target variables, using an INTO clause in the SELECT statement of a cursor_declaration is redundant and invalid.

The scope of cursor parameters is local to the cursor, meaning that they can be referenced only within the query used in the cursor declaration. The values of cursor parameters are used by the associated query when the cursor is opened. The query can also reference other PL/SQL variables within its scope.

The data type of a cursor parameter must be specified without constraints, that is, without precision and scale for numbers, and without length for strings.

Examples

Related Topics