Oefening2Package

CREATE OR REPLACE
PACKAGE department_info AS
  TYPE t_dep_loc IS RECORD(
    region_name   regions.region_name%TYPE,
    country_name  countries.country_name%TYPE,
    state         locations.state_province%TYPE,
    city          locations.city%TYPE
  );
  TYPE t_dep_mgr IS RECORD(
    first_name    employees.first_name%TYPE,
    last_name     employees.last_name%TYPE,
    email         employees.email%TYPE,
    phone_number  employees.phone_number%TYPE
  );
  /* Function returns a t_dep_loc record for a given department_id */
  FUNCTION location_department(p_department_id departments.department_id%TYPE) RETURN t_dep_loc;
  /* Fucntion returns a t_dep_mgr record for a given department_id */
  FUNCTION manager_department(p_department_id departments.department_id%TYPE) RETURN t_dep_mgr;
END department_info;
/
CREATE OR REPLACE
PACKAGE BODY department_info IS
  FUNCTION location_department(p_department_id departments.department_id%TYPE) RETURN t_dep_loc AS
    dep t_dep_loc;
  BEGIN
    SELECT r.region_name, co.country_name, l.state_province, l.city INTO
    dep.region_name, dep.country_name, dep.state, dep.city
    FROM departments d
    INNER JOIN locations l ON l.location_id = d.location_id
    INNER JOIN countries co ON co.country_id = l.country_id
    INNER JOIN regions r ON r.region_id = co.region_id
    WHERE p_department_id = department_id;
    RETURN dep;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.put_line('Error: no departments found for the given id');
        RAISE;
  END;
  FUNCTION manager_department(p_department_id departments.department_id%TYPE) RETURN t_dep_mgr AS
    mgr t_dep_mgr;
  BEGIN
    SELECT emp.first_name, emp.last_name, emp.email, emp.phone_number INTO mgr
    FROM departments d
    INNER JOIN employees emp ON d.manager_id=emp.employee_id
    WHERE d.department_id = p_department_id;
    RETURN mgr;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.put_line('Error: no departments found for the given id');
        RAISE;
  END;
END;
/*----TestApplicatie --------*/
DECLARE
v_department_id departments.department_id%TYPE;
locatie department_info.t_dep_loc;
manager department_info.t_dep_mgr;
BEGIN
-- De onderstaande regel zorgt ervoor dat je een dialoogvenster krijgt
-- waarin je een department_id kan opgeven
-- Om te debuggen maak je best gebruik van v_department_id := 50;
v_department_id := '&Geef_de_departementid';

locatie := department_info.location_department(v_department_id);
manager := department_info.manager_department(v_department_id);

DBMS_OUTPUT.put_line('Report of department with id: ' || v_department_id);
DBMS_OUTPUT.put_line('-------------------------------------------------');
DBMS_OUTPUT.put_line('Location: ');
DBMS_OUTPUT.put_line('Regionaam: ' || locatie.region_name);
DBMS_OUTPUT.put_line('Staat: ' || locatie.state);
DBMS_OUTPUT.put_line('Stad: ' || locatie.city);
DBMS_OUTPUT.put_line('');
DBMS_OUTPUT.put_line('Manager: ');
DBMS_OUTPUT.put_line('Voornaam: ' || manager.first_name);
DBMS_OUTPUT.put_line('Achternaam: ' || manager.last_name);
DBMS_OUTPUT.put_line('Email: ' || manager.email);
DBMS_OUTPUT.put_line('Telefoon: ' || manager.phone_number);
DBMS_OUTPUT.put_line('');
DBMS_OUTPUT.put_line('-------------------------------------------------');
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('Foutje...');
END;