colocviu.sql

set serveroutput on;



create or replace package colocviu as
  function venit(ang in emp%rowtype) return number;
  function grad(ang in emp%rowtype) return number;
  function sef(ang in emp%rowtype) return emp%rowtype;
  function este_sef(ang in emp%rowtype) return number;
  function bonus(ang in emp%rowtype) return number;
  function venit_mediu_dep(ang in emp%rowtype) return number;
  function venit_mediu_functie(ang in emp%rowtype) return number;
  procedure tabel;
end colocviu;
/
create or replace package body colocviu as
  function venit(ang in emp%rowtype) return number is
  begin
    return ang.sal + nvl(ang.comm, 0);
  end;

  function grad(ang in emp%rowtype) return number is
    grad number;
    v number;
  begin
    v := venit(ang);
    begin
      select grade into grad from salgrade where v >= losal and v <= hisal;
      return grad;
    exception
      when no_data_found then return null;
    end;
  end;
  
  function sef(ang in emp%rowtype) return emp%rowtype is
    s emp%rowtype;
  begin
    begin
      select * into s from emp where empno = ang.mgr;
      return s;
    exception
      when no_data_found then return null;
    end;
  end;
  
  function este_sef(ang in emp%rowtype) return number is
    subalt number;
  begin
    select count(*) into subalt from emp where mgr = ang.empno;
    if subalt > 0 then
      return 1;
    end if;
    return 0;
  end;
  
  function bonus(ang in emp%rowtype) return number is
  begin
    if este_sef(ang) = 1 then
      return grad(sef(ang)) / 10 * venit_mediu_dep(ang);
    end if;
    return grad(ang) / 10 * venit_mediu_functie(ang);
  end;
  
  function venit_mediu_dep(ang in emp%rowtype) return number is
    mediu number;
  begin
    select avg(sal + nvl(comm, 0)) into mediu from emp where deptno = ang.deptno;
    return mediu;
  end;
  
  function venit_mediu_functie(ang in emp%rowtype) return number is
    mediu number;
  begin
    select avg(sal + nvl(comm, 0)) into mediu from emp e where e.job = ang.job;
    return mediu;
  end;

  procedure tabel is
    cursor c_ang is select * from emp order by ename;
    ang c_ang%rowtype;
  begin
    dbms_output.put_line(rpad('NUME', 10)||rpad('GRAD', 7)||rpad('SEF', 10)||rpad('GRAD SEF', 10)||rpad('BONUS', 7)||'   '||rpad('ESTE SEF', 10));
    open c_ang;
    loop
      fetch c_ang into ang;
      exit when c_ang%notfound;
      dbms_output.put_line(rpad(ang.ename, 10)||rpad(grad(ang), 7)||rpad(sef(ang).ename, 10)||rpad(grad(sef(ang)), 10)||rpad(bonus(ang), 7)||'   '||rpad(este_sef(ang), 10));
    end loop;
    close c_ang;
  end tabel;
end colocviu;
/
begin
  colocviu.tabel;
end;
/