-- hr3.sql

CREATE OR REPLACE PACKAGE hr3 AS
  TYPE emp_cur_type IS REF CURSOR RETURN employee%ROWTYPE;
  PROCEDURE get_emps(store_num NUMBER, emp_cur IN OUT emp_cur_type);
  PROCEDURE give_raises(emp_cur emp_cur_type);
END hr3;
/

CREATE OR REPLACE PACKAGE BODY hr3 AS
  PROCEDURE get_emps(store_num NUMBER, emp_cur IN OUT emp_cur_type) AS
  BEGIN
     OPEN emp_cur FOR SELECT * FROM employee 
                       WHERE store_number = store_num FOR UPDATE;
  END get_emps;

  PROCEDURE give_raises(emp_cur emp_cur_type) AS
     emp employee%ROWTYPE;
  BEGIN
    FETCH emp_cur INTO emp; 
    WHILE emp_cur%FOUND LOOP
      IF emp.pay_type_code = 'S' THEN
           UPDATE employee 
              SET pay_amount = pay_amount * 1.2
            WHERE id = emp.id;
      END IF;
      FETCH emp_cur INTO emp; 
    END LOOP;
  END give_raises;
END hr3;
/
