-- year_end_raise2.sql

CREATE OR REPLACE PROCEDURE year_end_raises2 (st store.store_number%TYPE)
AS
  CURSOR emp_cursor(s  store.store_number%TYPE DEFAULT 1) IS
    SELECT id, state, pay_type_code
      FROM person JOIN employee USING (id)
     WHERE store_number = s
       FOR UPDATE OF pay_amount;
  
  emp_rec emp_cursor%ROWTYPE;
  pay_raise NUMBER(2,2);

BEGIN

  OPEN emp_cursor(st);

  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;
/
