Using a Cursor to Loop a Result Set
Retrieving Data from a Set Using Cursors and Cursor Variables
4-34 Oracle Database 2 Day Developer's Guide
The
CLOSE
statement completes cursor processing and closes the cursor. Note that
once a cursor is closed you cannot retrieve additional records from the result set.
You can implement procedure
eval_department
, which you declared in
on page 4-11, using a cursor for each employee record that matches the
query.
Example 411 Using a cursor to retrieve rows form a result set
The cursor
emp_cursor
fetches individual rows from the result set. Depending on the
value of the
eval_frequency
function for each row and the time of the year that the
eval_department
procedure runs, a new evaluation record is created for the
employee by invoking the
add_eval
procedure. Note that the buffer variable,
emp_
record
, is defined as a
%ROWTYPE
.
In the
emp_eval
package specification, edit the declaration of procedure
eval_
department
:
PROCEDURE eval_department(department_id IN employees.department_id%TYPE);
In the
emp_eval
Body pane, edit
eval_department
procedure.
PROCEDURE eval_department(department_id IN employees.department_id%TYPE) AS
-- declaring buffer variables for cursor data
emp_record employees%ROWTYPE;
-- declaring variable to monitor if all employees need evaluations
all_evals BOOLEAN;
-- today's date
today DATE;
-- declaring the cursor
CURSOR emp_cursor IS SELECT * FROM employees e
WHERE department_id = e.department_id;
BEGIN
-- determine if all evaluations must be done or just for newer employees;
-- this depends on time of the year
today := SYSDATE;
IF (EXTRACT(MONTH FROM today) < 6) THEN all_evals := FALSE;
ELSE all_evals := TRUE;
END IF;
OPEN emp_cursor;
-- start creating employee evaluations in a specific department
DBMS_OUTPUT.PUT_LINE('Determining evaluations necessary in department # ' ||
department_id);
LOOP
FETCH emp_cursor INTO emp_record; -- getting specific record
EXIT WHEN emp_cursor%NOTFOUND; -- all records are been processed
IF all_evals THEN
add_eval(emp_record.employee_id, today); -- create evals for all
ELSIF (eval_frequency(emp_record.employee_id) = 2) THEN
add_eval(emp_record.employee_id, today); -- create evals; newer employees
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
CLOSE emp_cursor;
END eval_department;
