Oefening1Package

CREATE OR REPLACE
PACKAGE Oefening1Package AS
  name_type VARCHAR2(50);
  TYPE name_tabel IS TABLE OF name_type%TYPE;

  TYPE job_info IS RECORD (
    job_id jobs.job_id%TYPE,
    job_name jobs.job_title%TYPE,
    names name_tabel
  );
  FUNCTION geefWerknemers(p_job_id VARCHAR2) RETURN job_info;
END Oefening1Package;
/
CREATE OR REPLACE
PACKAGE BODY Oefening1Package AS
  FUNCTION geefWerknemers(p_job_id VARCHAR2) RETURN job_info AS
    job_i job_info;
    counter INTEGER := 0;
    CURSOR c1 IS
      SELECT * FROM employees
      WHERE job_id = p_job_id;
  BEGIN
    /*Fetch job_id, job_name via simple select into */
    SELECT job_id, job_title INTO job_i.job_id, job_i.job_name FROM jobs
    WHERE job_id=p_job_id;
    /* Get all employees and add them to job_info */
    job_i.names := name_tabel();
    FOR emp_rec IN c1
    LOOP
      counter:=counter+1;
      job_i.names.extend;
      job_i.names(counter) := emp_rec.first_name || ' ' || emp_rec.last_name;
      EXIT WHEN c1%notfound;
    END LOOP;
    RETURN job_i;
    END;
END Oefening1Package;
/
/*------------Test program -------------------*/
DECLARE
   job_i HR.oefening1package.job_info;

BEGIN
   -- Call the function
   job_i := oefening1package.geefWerknemers('AD_PRES');
   DBMS_OUTPUT.put_line('Number of employees with this job: ' || job_i.names.COUNT);
   DBMS_OUTPUT.put_line(job_i.job_id);
   DBMS_OUTPUT.put_line(job_i.job_name);
   DBMS_OUTPUT.put_line('[');
   FOR i IN 1..job_i.names.COUNT LOOP
    DBMS_OUTPUT.put_line(job_i.names(i) || ', ' );
   END LOOP;
   DBMS_OUTPUT.put_line(']');

END;