create or replace package pck_example
as
    procedure exemplu_procedura(idDept in departments.department_id%type, 
        numeDept out departments.department_name%type, angajati out sys_refcursor);
    function exemplu_functie(jobId in jobs.job_id%type, 
        salLow out jobs.min_salary%type, salHigh out jobs.max_salary%type) return number;
end pck_example;
/

create or replace package body pck_example
as
    function exemplu_functie(jobId in jobs.job_id%type, 
        salLow out jobs.min_salary%type, salHigh out jobs.max_salary%type) return number
    is
        avgVenit number;
    begin
        select distinct min_salary, max_salary into salLow, salHigh from jobs where job_id = jobId;
        select round(avg(salary * (1 + nvl(commission_pct, 0))), 2) into avgVenit 
            from employees where job_id = jobId;
        return avgVenit;
    end exemplu_functie;

    procedure exemplu_procedura(idDept in departments.department_id%type, 
        numeDept out departments.department_name%type, angajati out sys_refcursor)
    is
    begin
        select department_name into numeDept from departments where department_id = idDept;
        open angajati for 
            select e.first_name || ' ' || e.last_name, e.hire_date, j.job_title, e.salary * (1 + nvl(e.commission_pct, 0)) as venit 
            from employees e inner join jobs j on e.job_id = j.job_id where e.department_id = idDept;
    end exemplu_procedura;
end pck_example;
/