Using Cursor Variables: REF Cursors
Retrieving Data from a Set Using Cursors and Cursor Variables
Developing and Using Stored Procedures 4-35
Compile the
emp_eval
package specification, and then the
emp_eval
Body.
The following message appears in the Messages-Log panes:
EMP_EVAL Body Compiled
Using Cursor Variables: REF Cursors
Cursors are static, as they are defined by the queries that create them. In some cases,
the queries themselves are created at runtime. A cursor variable, known as a
REF
CURSOR
, is more flexible than a cursor because it is independent of a specific query. It
can be opened for a query, can process the result set, and can be re-used for a query
that returns the same set of columns. This also makes
REF CURSOR
s ideal for passing
results of a query between subprograms.
REF CURSORS
can be declared with a return type that specifies the form of the result
set (strongly typed), or without a return type to retrieve any result set (weakly-typed).
Oracle recommends that you declare a
REF CURSOR
with a return type as it is less
prone to error because of its strong association with correctly formulated queries. If
you need a more flexible cursor that may be associated with several interchangeable
types, use the predefined type
SYS_REFCURSOR
.
The general form for using a
REF CURSOR
s follows.
DECLARE
TYPE
cursor_type
IS REF CURSOR RETURN
return_type
;
cursor_variable cursor_type
;
single_record return_type
;
OPEN
cursor_variable
FOR
query_definition
;
LOOP
FETCH
record
;
EXIT WHEN
cursor_name
%NOTFOUND;
...; -- process fetched row
END LOOP;
CLOSE
cursor_name
;
This is what happens during the life time of a
REF CURSOR
and a cursor variable:
The
REF CURSOR
type [with a return type] is declared.
The cursor variable that matches the cursor type is declared.
The variable for processing individual rows of the result set is declared; its type
must be the same as the return type of the
REF CURSOR
type definition.
The
OPEN
statement parses the query to the cursor variable.
The
FETCH
statement inside the loop runs the query, and retrieves the matching
rows into the local variable of the same type as the return type of the
REF CURSOR
for further processing.
The
CLOSE
statement completes cursor processing and closes the
REF CURSOR
.
In
eval_department
retrieves
a result set, processes it using a cursor, closes the cursor, and ends. If you declare the
cursor as a
REF CURSOR
type, you could modify it to process more departments (for
example, three consecutive departments) by re-using the cursor.
See Also:
Oracle Database PL/SQL Language Reference for information on
declaring cursors
