Oracle® Database PL/SQL Language Reference 11g Release 2 (11.2) Part Number E25519-05 |
|
|
PDF · Mobi · ePub |
An explicit cursor is a named pointer to a private SQL area that stores information for processing a specific query or DML statement—typically, one that returns or affects multiple rows. 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 theAUTHID
clause of the package. For more information, see "CREATE PACKAGE Statement".LNPLS1240Topics
LNPLS1088cursor_declaration ::=
LNPLS1087cursor_definition ::=
LNPLS1090cursor_parameter_dec ::=
LNPLS1241rowtype ::=
LNPLS1242cursor_declaration
LNPLS1244cursor
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").
LNPLS1245cursor_definition
Either defines an explicit cursor that was declared earlier or both declares and defines an explicit cursor.
LNPLS1092cursor
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.
LNPLS1102select_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.
LNPLS1246cursor_parameter_dec
LNPLS1247parameter
The name of the formal cursor parameter that you are declaring. This name can appear anywhere in select_statement
that a constant can appear.
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.
LNPLS1094datatype
The data type of the parameter.
LNPLS1262Restriction on datatype This datatype
cannot have constraints (for example, NOT
NULL
, or precision and scale for a number, or length for a string).
LNPLS1267expression
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
.
LNPLS1268rowtype
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.
LNPLS1269db_table_or_view
Name of a database table or view, which must be accessible when the declaration is elaborated.
LNPLS1273cursor
Name of a previously declared explicit cursor.
LNPLS1274cursor_variable
Name of a previously declared cursor variable.
LNPLS1275record
Name of a previously declared record variable.
LNPLS1276record_type
Name of a user-defined type that was defined with the data type specifier RECORD
.
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-40, "FOR UPDATE Cursor in CURRENT OF Clause of UPDATE Statement"
Example 6-41, "SELECT FOR UPDATE Statement for Multiple Tables"
Example 6-42, "FETCH with FOR UPDATE Cursor After COMMIT Statement"
LNPLS1277In this chapter:
LNPLS1281In other chapters: