-- add_employee.sql

DECLARE
  bad_supervisor_exception EXCEPTION;

  PROCEDURE add_emp(emp_id employee.id%TYPE, 
                    st_num employee.store_number%TYPE,
                    pay_code employee.pay_type_code%TYPE,
                    pay_amount employee.pay_amount%TYPE,
                    new_title employee.title%TYPE,
                    sup_id employee.supervisor_id%TYPE)
  AS
    sup_st_num employee.store_number%TYPE;
  BEGIN
    IF sup_id IS NOT NULL THEN
      SELECT store_number
        INTO sup_st_num
        FROM employee
       WHERE id = sup_id;
      IF sup_st_num != st_num THEN
        RAISE bad_supervisor_exception;
      END IF;
    END IF;

    INSERT INTO employee
      VALUES (emp_id, st_num, pay_code, pay_amount, new_title, sup_id);

  END add_emp;

BEGIN
  add_emp(9989, 7, 'H', 10.50, 'Clerk', 7881);
EXCEPTION
  WHEN bad_supervisor_exception THEN
    add_emp(9989, 7, 'H', 10.50, 'Clerk', NULL);
END;
/
