Using FETCH ... INTO Statements
Retrieving Data from a Set Using Cursors and Cursor Variables
Developing and Using Stored Procedures 4-37
SELECT *
FROM employees e
WHERE department_curr = e.department_id;
-- create employee evaluations is specific departments
DBMS_OUTPUT.PUT_LINE('Determining necessary evaluations in department #' ||
department_curr);
eval_loop_control(emp_cursor); -- call to process the result set
DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
CLOSE emp_cursor;
department_curr := department_curr + 10;
END LOOP;
END eval_department;
4.
In the
emp_eval
Body pane, edit
add_eval
procedure to use the entire retrieved
record of
employee%ROWTYPE
, instead of an
employee_id
. Note that you no
longer need any declarations at the beginning of the procedure.
PROCEDURE add_eval(
emp_record IN employees%ROWTYPE
, today IN DATE) AS
BEGIN
-- inserting a new row of values into evaluations table
INSERT INTO evaluations VALUES (
evaluations_seq.NEXTVAL, -- evaluation_id
emp_record.employee_id, -- employee_id
today, -- evaluation_date
emp_record.job_id, -- job_id
emp_record.manager_id, -- manager_id
emp_record.department_id, -- department_id
0); -- total_score
END add_eval;
5.
Towards the end of code in the
emp_eval
Body pane, add
eval_loop_control
procedure to fetch the individual records from the result set and to process them.
Note that much of this code is from an earlier definition of the
eval_
department
are in bold font.
PROCEDURE eval_loop_control(emp_cursor IN emp_refcursor_type) AS
-- declaring buffer variable for cursor data
emp_record employees%ROWTYPE;
-- declaring variable to monitor if all employees need evaluations
all_evals BOOLEAN;
-- today's date
today DATE;
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;
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, today); -- create evaluations for all
ELSIF (eval_frequency(emp_record.employee_id) = 2) THEN
add_eval(emp_record, today);
-- create evaluations for newer employees
