REF CURSOR Example
Retrieving Data from a Set Using Cursors and Cursor Variables
4-36 Oracle Database 2 Day Developer's Guide
Note that the fetching loop is part of the new
eval_fetch_control
procedure, that
uses the cursor variable as input. This has an additional benefit of separating the
processing of the result set from the definition of the query. You could write a
procedure (
eval_everyone
) that initiates evaluations for all employees in the
company, not just on a department basis.
Note also that
eval_department
uses a single field of a record to call procedure
add_eval
, which runs three separate queries on the same record. This is very
inefficient; you will re-write the
add_eval
to use the entire record buffer of the
REF
CURSOR
.
To use a REF CURSOR:
1.
In the
emp_eval
specification, add the
REF CURSOR
type definition,
emp_
refcursor_type
. The type is defined at package level for visibility for all
subprograms. Also add a declaration for procedure
eval_everyone
. The new
code is in bold font.
create or replace
PACKAGE emp_eval AS
PROCEDURE eval_department (department_id IN employees.department_id%TYPE);
PROCEDURE eval_everyone;
FUNCTION calculate_score(eval_id IN scores.evaluation_id%TYPE
, perf_id IN scores.performance_id%TYPE)
RETURN NUMBER;
TYPE SAL_INFO IS RECORD -- type for salary, limits, raises, and adjustments
( job_id jobs.job_id%type
, sal_min jobs.min_salary%type
, sal_max jobs.max_salary%type
, salary employees.salary%type
, sal_raise NUMBER(3,3));
TYPE emp_refcursor_type IS REF CURSOR RETURN employees%ROWTYPE;
-- the REF CURSOR type for result set fetches
END emp_eval;
2.
In the
emp_eval
Body pane, add a forward declaration for procedure
eval_
loop_control
and edit the declaration of procedure
add_eval
. New code is in
bold font.
CREATE OR REPLACE PACKAGE BODY emp_eval AS
/* local subprogram declarations */
FUNCTION eval_frequency (employee_id IN employees.employee_id%TYPE)
RETURN NUMBER;
PROCEDURE salary_schedule(emp IN sal_info);
PROCEDURE add_eval(emp_record IN employees%ROWTYPE, today IN DATE);
PROCEDURE eval_loop_control(emp_cursor IN emp_refcursor_type);
...
3.
In the
emp_eval
Body pane, edit
eval_department
procedure to retrieve three
separate result sets based on the department, and to call the
eval_loop_
control
procedure.
PROCEDURE eval_department(department_id IN employees.department_id%TYPE) AS
-- declaring the REF CURSOR
emp_cursor emp_refcursor_type;
department_curr departments.department_id%TYPE;
BEGIN
department_curr := department_id; -- starting with the first department
FOR loop_c IN 1..3 LOOP
OPEN emp_cursor FOR
