Overview
on the usage of Cursor Variables in PL/SQL

A
cursor in Oracle is a static object. It is bound to only one specific
query. For example the explicit and implicit cursors are static and attached
to specific queries. A cursor variable on the other hand is a pointer
to a cursor. A cursor variable can hold many queries within a particular
program execution.

They can be passed and returned as formal parameters to subprograms. It
can also be opened in one subprogram and processed in another on server
side. The main advantages of using cursor variables are encapsulation,
ease of maintenance and convenient security.

While the
declaration of cursor variable and open statement syntax is enhanced from
the syntax used for static cursors, the cursor operations for the cursor
variables is similar to that of static cursors. The cursor attributes
%ISOPEN, %FOUND, %NOTFOUND and %ROWCOUNT can also be used with similar
syntax as used for static cursors.

Datatype
of a cursor variable is REF_CURSOR. There are two steps to be followed
in creation of a cursor variable. First a referenced cursor TYPE has to
be created and the actual cursor variable declared based on that type.
The REF CURSOR type may be strong or weak.
The syntax for creating a referenced cursor type is

TYPE cursor_type_name
IS REF CURSOR [RETURN return_type];

Where cursor_type_name
is the name given to cursor type and return_type is the data specification
for the data returned. The return type can be any valid data structure
relevant for a cursor. This is usually defined using %ROWTYPE attribute
or previously defined record TYPE.

The RETURN
clause is optional. If RETURN clause is specified it is said to be of
strong type of REF CURSOR. If the RETURN clause is missing it is called
a weak type. Weak REF CURSOR type cursors are more flexible and can be
used with any query, row type structure and can vary within the scope
of a single program unit.

In the case of weak REF CURSOR type PL/SQL compiler associates cursor
variable to any query. In case of strong REF CURSOR type PL/SQL associates
cursor variables to queries that only generate correct set of columns.

The syntax
for the declaration of cursor variable is as below.

cursor_name
cursor_type_name;

Here cursor_name is the name of the cursor and cursor_type_name is name
of the type of cursor previously declared. When the cursor variable is
declared the cursor object is not created. Only with the OPEN statement
a new cursor object is created and assigned to the variable.

A value needs
to be assigned when the cursor variable is opened. The syntax for opening
cursor variables is

OPEN cursor_name
FOR select_statement;

Here cursor_name
is the name of the cursor and select_statement is a SQL SELECT statement.
For strong REF_CURSOR type the structure of select statement specified
must be compatible with return data type specified.

The FETCH
statement is used to fetch data from a cursor variable. The syntax is

FETCH cursor_name
INTO variable_list;

The variable
list should be compatible with data type structure specified in query
associated with cursor variable for both strong REF_CURSOR type and weak
REF_CURSOR types. When the cursor variable is of strong REF_CURSOR type
the compiler does the check whereas for weak REF_CURSOR type the check
happens at run time.

If there
are incompatible record structures PL/SQL will raise the exception ROWTYPE_MISMATCH.

Just like
explicit cursor the CLOSE statement is used to close cursor variable.

CLOSE cursor_name;

where cursor_name
is the name of the cursor to be closed.

PL/SQL allows
assignment operations with cursor variables and also to pass them as arguments
to procedures and functions. However in order to do so the cursor variables
need to follow set of predefined compile time and runtime rowtype matching
rules. If one cursor variable is assigned to another they develop into
aliases for the same cursor object. Any action taken on the cursor object
through one variable is also obtainable and reflected in the other.

The scope
of a cursor variable is similar to that of a static cursor. It is limited
to the PL/SQL block in which the variable is declared. If declared in
a package it becomes globally accessible. The scope of a cursor object
remains as long as one active cursor variable refers to that cursor object.
In different terms, a cursor object can be created in one PL/SQL block
and assigned to a cursor variable. This can be further assigned to another
cursor variable with different scope and remain accessible even if first
cursor variable goes out of scope.

While cursor
variables are subject to certain restrictions such as they cannot be used
with dynamic SQL, cannot be declared in a package, cannot be stored in
database columns etc, they have the advantages of letting the programmer
associate a cursor variable with different queries at different points
in execution, share the result of a cursor variable by passing it as argument
to a procedure or function, assign the contents of one cursor variable
to another and also allow the usage of the full functionality of static
cursors. Thus using cursor variables help to develop performance and to
streamline the code.

FREE
Subscription

Subscribe to our mailing list and receive new articles
through email. Keep yourself updated with latest
developments in the industry.

Name:Email:

Note
: We never rent, trade, or sell my email lists toanyone.
We assure that your privacy is respected
and protected.