Tutorial: Using a Cursor Variable to Retrieve Result Set Rows One at a Time

This tutorial shows how to change the EMP_EVAL.eval_department procedure so that it uses a cursor variable instead of an explicit cursor, which lets it process multiple departments. The change includes adding a procedure that uses the cursor variable.

This tutorial also shows how to make EMP_EVAL.eval_department and EMP_EVAL.add_eval more efficient: Instead of passing one field of a record to add_eval and having add_eval use three queries to extract three other fields of the same record, eval_department passes the entire record to add_eval, and add_eval uses dot notation to access the values of the other three fields.

To change the EMP_EVAL.eval_department procedure to use a cursor variable:

1. In the EMP_EVAL package specification, add the procedure declaration and the REF CURSOR type definition, as shown in bold font:

   create or replace
   PACKAGE emp_eval AS
   
     PROCEDURE eval_department (dept_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
         ( j_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;
   END emp_eval;
   

   (For instructions for changing a package specification, see "Tutorial: Changing a Package Specification".)

2. In the EMP_EVAL package body, add a forward declaration for the procedure eval_loop_control and change the declaration of the procedure add_eval, as shown in bold font:

   create or replace
   PACKAGE BODY EMP_EVAL AS
   
     FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
       RETURN PLS_INTEGER;
   
     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);
   ...
   

   (For a step-by-step example of changing a package body, see "Tutorial: Declaring Variables and Constants in a Subprogram".)

3. Change the eval_department procedure to retrieve three separate result sets based on the department, and to call the eval_loop_control procedure, as shown in bold font:

   PROCEDURE eval_department(dept_id IN employees.department_id%TYPE) AS
     emp_cursor    emp_refcursor_type;
     current_dept  departments.department_id%TYPE;
   
   BEGIN
     current_dept := dept_id;
   
     FOR loop_c IN 1..3 LOOP
       OPEN emp_cursor FOR
         SELECT * 
         FROM employees
         WHERE current_dept = dept_id;
   
       DBMS_OUTPUT.PUT_LINE
         ('Determining necessary evaluations in department #' ||
          current_dept);
   
       eval_loop_control(emp_cursor);
   
       DBMS_OUTPUT.PUT_LINE
         ('Processed ' || emp_cursor%ROWCOUNT || ' records.');
   
       CLOSE emp_cursor;
       current_dept := current_dept + 10; 
     END LOOP;
   END eval_department;
   

4. Change the add_eval as shown in bold font:

   PROCEDURE add_eval(emp_record IN employees%ROWTYPE, today IN DATE)
   AS
   -- (Delete local variables)
   BEGIN
     INSERT INTO EVALUATIONS (
       evaluation_id,
       employee_id,
       evaluation_date,
       job_id,
       manager_id,
       department_id,
       total_score
     )
     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. Before END EMP_EVAL, add the following procedure, which fetches the individual records from the result set and processes them:

   PROCEDURE eval_loop_control (emp_cursor IN emp_refcursor_type) AS
      emp_record      EMPLOYEES%ROWTYPE;
      all_evals       BOOLEAN;
      today           DATE;
   BEGIN
     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;
       EXIT WHEN emp_cursor%NOTFOUND;
   
       IF all_evals THEN
         add_eval(emp_record, today);
       ELSIF (eval_frequency(emp_record.employee_id) = 2) THEN
         add_eval(emp_record, today);
       END IF;
     END LOOP;
   END eval_loop_control;
   

6. Before END EMP_EVAL, add the following procedure, which retrieves a result set that contains all employees in the company:

   PROCEDURE eval_everyone AS
     emp_cursor emp_refcursor_type;
   BEGIN
     OPEN emp_cursor FOR SELECT * FROM employees;
     DBMS_OUTPUT.PUT_LINE('Determining number of necessary evaluations.');
     eval_loop_control(emp_cursor);
     DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
     CLOSE emp_cursor;
   END eval_everyone;
   

7. Compile the EMP_EVAL package specification.

8. Compile the EMP_EVAL package body.