-- hr2.sql

CREATE OR REPLACE PACKAGE hr2 AS
  TYPE min_wage_type IS TABLE OF NUMBER INDEX BY VARCHAR2(2);
  TYPE emp_type IS RECORD (eid NUMBER, pay NUMBER, ptc CHAR);
  TYPE emp_array_type IS TABLE OF emp_type;
  SUBTYPE money_type IS NUMBER(9,2);
  CURSOR emp_cur(store NUMBER) RETURN emp_type;
  CURSOR emp_cur2(state CHAR) RETURN emp_type;
  CURSOR emp_cur3(city VARCHAR2) RETURN emp_type;
  pay_below_min_wage EXCEPTION;
  invalid_store EXCEPTION;
  invalid_state EXCEPTION;
  FUNCTION get_emps(store_num NUMBER) RETURN emp_array_type;
  FUNCTION get_emps(state CHAR) RETURN emp_array_type;
  FUNCTION get_emps(city VARCHAR2) RETURN emp_array_type;
  FUNCTION get_manager(store_num NUMBER) RETURN NUMBER;
  FUNCTION get_state_min_wage(state CHAR) RETURN NUMBER;
END hr2;
/

CREATE OR REPLACE PACKAGE BODY hr2 AS
  min_wages min_wage_type;   -- private variable. Use get_state_min_wage to 
                             -- access it from outside program.
  CURSOR emp_cur(store NUMBER) RETURN emp_type IS 
    SELECT id, pay_amount, pay_type_code
      FROM employee
     WHERE store_number = store
     ORDER BY id;

  CURSOR emp_cur2(state CHAR) RETURN emp_type IS 
    SELECT id, pay_amount, pay_type_code
      FROM employee
     WHERE store_number IN (SELECT store_number
                              FROM store
                              WHERE state = state) 
     ORDER BY id;

  CURSOR emp_cur3(city VARCHAR2) RETURN emp_type IS 
    SELECT id, pay_amount, pay_type_code
      FROM employee
     WHERE store_number IN (SELECT store_number
                              FROM store
                             WHERE city = city) 
     ORDER BY id;

  /* Forward Declarations of private subprograms */
  FUNCTION get_store_state(st_num NUMBER) RETURN VARCHAR2;

  FUNCTION get_state_min_wage(state CHAR) RETURN NUMBER AS
    min_wage NUMBER := min_wages(state);
  BEGIN
    IF min_wage IS NULL THEN
      RAISE invalid_state;
    END IF;
    RETURN min_wages(state);
  END get_state_min_wage;

  FUNCTION get_emps(store_num NUMBER) RETURN emp_array_type AS
    emps emp_array_type := emp_array_type();
    i NUMBER := 1;  --counter
  BEGIN
    FOR rec IN emp_cur(store_num) LOOP
      emps.EXTEND;
      emps(i) := rec;
      i := i + 1;
    END LOOP;
    RETURN emps;
  END get_emps;

  FUNCTION get_emps(state CHAR) RETURN emp_array_type AS
    emps emp_array_type := emp_array_type();
    i NUMBER := 1;  --counter
  BEGIN
    FOR rec IN emp_cur2(state) LOOP
      emps.EXTEND;
      emps(i) := rec;
      i := i + 1;
    END LOOP;
    RETURN emps;
  END get_emps;

  FUNCTION get_emps(city VARCHAR2) RETURN emp_array_type AS
    emps emp_array_type := emp_array_type();
    i NUMBER := 1;  --counter
  BEGIN
    FOR rec IN emp_cur3(city) LOOP
      emps.EXTEND;
      emps(i) := rec;
      i := i + 1;
    END LOOP;
    RETURN emps;
  END get_emps;

  FUNCTION get_manager(store_num NUMBER) RETURN NUMBER AS
    mid store.manager_id%TYPE;
  BEGIN
    SELECT id
      INTO mid 
      FROM employee 
     WHERE id = (SELECT manager_id 
                   FROM store
                  WHERE store_number = store_num); 
    RETURN mid;
  END get_manager;

  /* The next function is private, since it's not declared in
     the spec. */
  FUNCTION get_store_state(st_num NUMBER) RETURN VARCHAR2 AS
    st store.state%TYPE; 
  BEGIN
    SELECT state
      INTO st 
      FROM store
     WHERE store_number = st_num;
    RETURN st;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE invalid_store;
  END get_store_state;

  /*
  The BEGIN block is an optional block where you can initialize
  variables. It must appear after all other definitions.
  */
BEGIN
    min_wages('CO') := 5.15;
    min_wages('CA') := 6.75;
    min_wages('WA') := 7.35;
    min_wages('UT') := 5.15;
    min_wages('NV') := 5.15;
END hr2;
/
