-- year_end_raise.sql

CREATE OR REPLACE PROCEDURE year_end_raises
AS
  CURSOR emp_cursor IS
    SELECT id, state, pay_type_code
      FROM employee JOIN person USING (id)
         FOR UPDATE OF pay_amount;
  
  emp_rec emp_cursor%ROWTYPE;
  pay_raise NUMBER(2,2);

BEGIN

  OPEN emp_cursor;

  FETCH emp_cursor
   INTO emp_rec;

  WHILE emp_cursor%FOUND LOOP
    IF emp_rec.pay_type_code IN ('H', 'S') AND emp_rec.state = 'CA' THEN
      pay_raise := 0.15;
    ELSIF emp_rec.pay_type_code IN ('H', 'S') THEN
      pay_raise := 0.12;
    ELSIF emp_rec.pay_type_code IN ('C','T') THEN
      pay_raise := 0.1;
    ELSE
      pay_raise := 0;
    END IF;

    raise_pay(emp_rec.id, pay_raise);

    FETCH emp_cursor
     INTO emp_rec;

  END LOOP;
  CLOSE emp_cursor;
END;
/
