
create or replace 
PROCEDURE 
 P_GET_EMP_BY_DEPARTMENT_NAME(
 p_department_name departments.department_name%type)
-- AUTHID CURRENT_USER 
AS 
  
/* uebergabe eines department_name
   ausgabe first_/last_name aller employees des
   departments
*/
cursor c_emp_by_departement_name(
  pc_department_name departments.department_name%type) 
  is
  select first_name,
         last_name
    from employees e join 
         departments d on
         e.department_id = d.department_id
    where department_name = pc_department_name;

i number := 0;    


e_department_empty exception;
pragma exception_init(e_department_empty, -20000);

e_no_department_found exception;
pragma exception_init(e_no_department_found, -20001);

BEGIN
  delete from employees_temp;
  select count(department_id) into i 
    from departments 
   where department_name = p_department_name;
   
  if i = 0 then  
    raise_application_error
       (-20001, 'no department found');
  end if;
  i := 0;
  
  for r_emp in c_emp_by_departement_name    
    (p_department_name) 
  loop    
    i := i + 1;
    insert into employees_temp values r_emp;
  end loop; 
  
  if i < 1 then 
    raise_application_error
       (-20000, 'no employee found');
    -- raise e_department_empty;
  end if;    
      
  dbms_output.put_line(
    to_char(sysdate, 'yyyy-mm-ddd hh24:mi:ss')
    );
  
  /* bulk collect geht nicht eine Tabelle
  open c_emp_by_departement_name(p_department_name);
  fetch c_emp_by_departement_name 
    bulk collect into employees_temp;
  */ 
END;


CREATE OR REPLACE PROCEDURE EMP_PER_DEP AS 
cursor c_dep is
    select department_id, department_name from departments;

i number;
BEGIN
  for r_dep in c_dep loop
    dbms_output.put_line(r_dep.department_name);
    i := 0;    
    for r_emp in 
      (select * from employees where 
       department_id = r_dep.department_id 
       order by salary desc) loop
       i := i + 1;
       exit when i > 3;
          dbms_output.put_line(i||'. '||r_emp.last_name
                                   ||' '||r_emp.salary);
     end loop;                                                
  end loop;  
END EMP_PER_DEP;