Cursor Variables
A cursor variable is a data structure that points to a cursor
object, which in turn points to the cursor's result set. One can use cursor
variables to more easily retrieve rows in a result set from client and server
programs. One can also use cursor variables to hide minor variable in queries.
Syntax:
TYPE ref cursor name IS REF
CURSOR
[RETURN record_type];
If a RETURN clause is not included, then a weak REF CURSOR
is declared. Cursor variables declared from weak REF CURSOR's can be associated
with any query at runtime. A REF CURSOR declaration with a RETURN clause
defines a "strong" REF CURSOR. A cursor variable based on a strong
REF CURSOR can be associated with queries whose result sets match the number
and datatype of the record structure after the RETURN at runtime. To use cursor
variables, firstly a REF_CURSOR type must be created, then a cursor variable
based on that type must be declared.
Syntax:
OPEN cursor name FOR select
statement;
FETCH and CLOSE a cursor variable using the same syntax as
for explicit cursors. There are a number of restrictions on cursor variables as
follows:
1) Cursor variables cannot be declared in a package since
they do not have a persistent state.
2) One cannot use the FOR UPDATE clause with cursor
variables.
3) One cannot assign NULLs to a cursor variable nor use
comparison operators to test for equality, inequality, or nullity.
4) Neither database columns nor collections can store cursor
5) Cursor not use RPCs to pass cursor variables from one
server to
6) Cursor variables cannot be used with the dynamic SQL
built-in package DBMS_SQL.
