-- weak_cursor.sql
CREATE OR REPLACE FUNCTION get_emps (snum employee.store_number%TYPE)
  RETURN SYS_REFCURSOR
AS
  ret_curs SYS_REFCURSOR;
BEGIN
  OPEN ret_curs FOR 
    SELECT * FROM employee WHERE store_number = snum FOR UPDATE;
  RETURN ret_curs;
END;
/

DECLARE
  mycurs SYS_REFCURSOR;
  TYPE emp_rec IS RECORD (
    id employee.id%TYPE,
    store_number employee.store_number%TYPE,
    pay_type_code employee.pay_type_code%TYPE,
    pay_amount employee.pay_amount%TYPE,
    title employee.title%TYPE,
    supervisor_id employee.supervisor_id%TYPE
  );
  emp emp_rec;
BEGIN
  mycurs := get_emps(5);
  FETCH mycurs
   INTO emp;
  WHILE mycurs%FOUND LOOP
    IF (emp.pay_type_code = 'H') THEN
      UPDATE employee
         SET pay_amount = pay_amount * 1.1
       WHERE id = emp.id;
    END IF;
    FETCH mycurs
      INTO emp;
  END LOOP;
END;
/
