/*
Pentru angaja?ii dintr-un departament, al c?rui ID se introduce de la tastatur?, 
care au salariul peste media salariilor pentru functia pe care o detin, 
s? se genereze o excep?ie definit? altfel sa se afiseze angajatul.
*/

set serveroutput on;

declare
  angajat integer;
  type nestedEmpTable is table of emp%rowtype;
  angajatiDepartament nestedEmpTable;
  
  medieSal emp.sal%type;
  
  idDept emp.deptno%type;

  salPesteMedie exception;
begin
  idDept := &deptId;
  
  select *
  bulk collect into angajatiDepartament
  from emp
  where deptno = idDept;

  dbms_output.put_line(rpad('Nume',15,' ')||
    rpad('Job',20,' ')||lpad('Salariu',20,' '));
  dbms_output.put_line(rpad('=',15,'=')||
    rpad('=',20,'=')||lpad('=',20,'='));

  for angajat in angajatiDepartament.first..angajatiDepartament.LAST
  loop
  begin

    select avg(sal)
    into medieSal
    from emp
    where job = angajatiDepartament(angajat).job;
    
    if (angajatiDepartament(angajat).sal > medieSal) then
      raise salPesteMedie;
    else
      dbms_output.put_line(rpad(angajatiDepartament(angajat).ename,15,' ')||rpad(angajatiDepartament(angajat).job,20,' ')||lpad(angajatiDepartament(angajat).sal,20,' '));
    end if;
  exception  
    when salPesteMedie then
        dbms_output.put_line(rpad(angajatiDepartament(angajat).ename,15,' ')||
          rpad(angajatiDepartament(angajat).job,20,' ')||lpad('salariu peste medie',20,' '));
  end;
  end loop;
  exception
      when no_data_found then null;
end;