-- varray.sql

DECLARE
  eid employee.id%TYPE;
  TYPE e_tab IS RECORD (
    id employee.id%TYPE,
    pay employee.pay_amount%TYPE,
    pay_type employee.pay_type_code%TYPE
  );
  erec e_tab;
  TYPE sal_tab IS VARRAY(200) OF e_tab;
  sal sal_tab := sal_tab();

  ecount NUMBER;
  num NUMBER := 1;
BEGIN
  SELECT count(*) INTO ecount FROM employee;
  SELECT min(id) INTO eid FROM employee;
  WHILE num <= ecount LOOP
    sal.EXTEND;
    SELECT id, pay_amount, pay_type_code INTO sal(num)
      FROM employee WHERE id = eid;
    SELECT min(id) INTO eid FROM employee WHERE id > eid;
    num := num + 1;
  END LOOP;

  FOR n IN 1..sal.LAST
  LOOP
    IF sal(n).pay_type = 'H' THEN
      sal(n).pay := sal(n).pay + .25;
    ELSIF sal(n).pay_type = 'C' THEN
      sal(n).pay := sal(n).pay * 1.05;
    END IF;
    UPDATE employee SET pay_amount = sal(n).pay
     WHERE id = sal(n).id;
  END LOOP;
END;
/


