dokuwiki

This is an old revision of the document!

---

FUNCTION get_emp_per_dep
  ( p_departement_name in varchar2)
  RETURN emp_list pipelined
  is
  t_emp_list emp_list := emp_list();
  c boolean := false;
  e_no_emp_found exception;

  pragma EXCEPTION_INIT
     (e_no_emp_found, -20001);
  
BEGIN
  for emp in (select first_name||' '||last_name name
          from employees e, departments d where
          e.department_id = d.department_id and
          department_name = p_departement_name) loop
  c := true;
--  t_emp_list.extend;
--  t_emp_list(t_emp_list.last) := emp.name;
    pipe row(emp.name);
  end loop;
  
  if c = false then
      raise_application_error(-20001, 'NO emp found in dep ' || p_departement_name);
  end if;
  return;
END;