A cursor variable points to the unnamed work area in which the database stores processing information when it executes a multiple-row query. With this pointer to the work area, you can access its information, and process the rows of the query individually.
A cursor variable is like a C or Pascal pointer, which holds the address of an item instead of the item itself.
To create a cursor variable, define a REF
CURSOR
type, and then declare the cursor variable to be of that type. Declaring a cursor variable creates a pointer, not an item.
ref_cursor_type_definition ::=
cursor_variable_declaration ::=
Keyword and Parameter Descriptions
An explicit cursor previously declared within the current scope.
A PL/SQL cursor variable previously declared within the current scope.
A database table or view, which must be accessible when the declaration is elaborated.
A user-defined record previously declared within the current scope.
A user-defined record type that was defined using the data type specifier RECORD
.
Cursor variables all have the data type REF
CURSOR
.
Specifies the data type of a cursor variable 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 from a cursor or strongly typed cursor variable. You can use the %TYPE
attribute to provide the data type of a previously declared record.
A record type that represents a row in a database table or a row fetched from a cursor or strongly typed cursor variable. Fields in the record and corresponding columns in the row have the same names and data types.
Provides the data type of a previously declared user-defined record.
A user-defined cursor variable type that was defined as a REF
CURSOR
.
A REF
CURSOR
type definition can appear either in the declarative part of a block, subprogram, package specification, or package body (in which case it is local to the block, subprogram, or package) or in the CREATE TYPE Statement (in which case it is a standalone stored type).
A cursor variable declaration can appear only in the declarative part of a block, subprogram, or package body (not in a package specification).
Cursor variables are available to every PL/SQL client. For example, you can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program, then pass it as a bind argument to PL/SQL. Application development tools that have a PL/SQL engine can use cursor variables entirely on the client side.
You can pass cursor variables back and forth between an application and the database server through remote procedure invokes using a database link. If you have a PL/SQL engine on the client side, you can use the cursor variable in either location. For example, you can declare a cursor variable on the client side, open and fetch from it on the server side, then continue to fetch from it back on the client side.
You use cursor variables to pass query result sets between PL/SQL stored subprograms and client programs. Neither PL/SQL nor any client program owns a result set; they share a pointer to the work area where the result set is stored. For example, an OCI program, Oracle Forms application, and the database can all refer to the same work area.
REF
CURSOR
types can be strong or weak. A strong REF
CURSOR
type definition specifies a return type, but a weak definition does not. Strong REF
CURSOR
types are less error-prone because PL/SQL lets you associate a strongly typed cursor variable only with type-compatible queries. Weak REF
CURSOR
types are more flexible because you can associate a weakly typed cursor variable with any query.
Once you define a REF
CURSOR
type, you can declare cursor variables of that type. You can use %TYPE
to provide the data type of a record variable. Also, in the RETURN
clause of a REF
CURSOR
type definition, you can use %ROWTYPE
to specify a record type that represents a row returned by a strongly (not weakly) typed cursor variable.
Currently, cursor variables are subject to several restrictions. See Restrictions on Cursor Variables.
You use three statements to control a cursor variable: OPEN-FOR
, FETCH
, and CLOSE
. First, you OPEN
a cursor variable FOR
a multiple-row query. Then, you FETCH
rows from the result set. When all the rows are processed, you CLOSE
the cursor variable.
Other OPEN-FOR
statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. When you reopen a cursor variable for a different query, the previous query is lost.
PL/SQL makes sure the return type of the cursor variable is compatible with the INTO
clause of the FETCH
statement. For each column value returned by the query associated with the cursor variable, there must be a corresponding, type-compatible field or variable in the INTO
clause. Also, the number of fields or variables must equal the number of column values. Otherwise, you get an error.
If both cursor variables involved in an assignment are strongly typed, they must have the same data type. However, if one or both cursor variables are weakly typed, they need not have the same data type.
When declaring a cursor variable as the formal parameter of a subprogram that fetches from or closes the cursor variable, you must specify the IN
or IN
OUT
mode. If the subprogram opens the cursor variable, you must specify the IN
OUT
mode.
Be careful when passing cursor variables as parameters. At run time, PL/SQL raises ROWTYPE_MISMATCH
if the return types of the actual and formal parameters are incompatible.
You can apply the cursor attributes %FOUND
, %NOTFOUND
, %ISOPEN
, and %ROWCOUNT
to a cursor variable.
If you try to fetch from, close, or apply cursor attributes to a cursor variable that does not point to a query work area, PL/SQL raises the predefined exception INVALID_CURSOR
. You can make a cursor variable (or parameter) point to a query work area in two ways:
OPEN
the cursor variable FOR
the query.
Assign to the cursor variable the value of an already OPEN
ed host cursor variable or PL/SQL cursor variable.
A query work area remains accessible as long as any cursor variable points to it. Therefore, you can pass the value of a cursor variable freely from one scope to another. For example, if you pass a host cursor variable to a PL/SQL block embedded in a Pro*C program, the work area to which the cursor variable points remains accessible after the block completes.