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

This tutorial shows how to implement the procedure EMP_EVAL.eval_department, which uses an explicit cursor, emp_cursor.

To implement the EMP_EVAL.eval_department procedure:

1. In the EMP_EVAL package specification, change the declaration of the eval_department procedure as shown in bold font:

   PROCEDURE eval_department(dept_id IN employees.department_id%TYPE);
   

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

2. In the EMP_EVAL package body, change the definition of the eval_department procedure as shown in bold font:

   PROCEDURE eval_department (dept_id IN employees.department_id%TYPE)
   AS
     CURSOR emp_cursor IS
       SELECT * FROM EMPLOYEES
       WHERE DEPARTMENT_ID = dept_id;
   
     emp_record  EMPLOYEES%ROWTYPE;  -- for row returned by cursor
     all_evals   BOOLEAN;  -- true if all employees in dept need evaluations
     today       DATE;
   
   BEGIN
     today := SYSDATE;
   
     IF (EXTRACT(MONTH FROM today) < 6) THEN
       all_evals := FALSE; -- only new employees need evaluations
     ELSE
       all_evals := TRUE;  -- all employees need evaluations
     END IF;
   
     OPEN emp_cursor;
   
     DBMS_OUTPUT.PUT_LINE (
       'Determining evaluations necessary in department # ' ||
       dept_id );
   
     LOOP
       FETCH emp_cursor INTO emp_record;
       EXIT WHEN emp_cursor%NOTFOUND;
   
       IF all_evals THEN
         add_eval(emp_record.employee_id, today);
       ELSIF (eval_frequency(emp_record.employee_id) = 2) THEN
         add_eval(emp_record.employee_id, today);
       END IF;
     END LOOP;
   
     DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
   
     CLOSE emp_cursor;
   END eval_department;
   

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

3. Compile the EMP_EVAL package specification.

4. Compile the EMP_EVAL package body.