Tema Laborator 7 SGBD

tema_lab7.sql

-- 1. Să se creeze o procedură stocată care măreşte salariile angajaţilor care nu au comision şi au
-- media mai mică decât cea a departamentului în care lucrează cu o valoare transmisă ca
-- parametru.
create or replace procedure inc_salary (val number) as
  cursor v_cursor is 
    select department_id, avg(salary) avg_sal
    from employees
    group by department_id
    order by 2;

begin
  for i in v_cursor loop
    update emp_pnu
    set salary = salary + val
    where commission_pct is null
      and salary < i.avg_sal
      and department_id = i.department_id;
  end loop;
end inc_salary;

-- 2. Să se creeze o funcţie stocată care determină numărul de salariaţi care au fost angajaţi după
-- toţi salariaţii ai unui manager al cărui cod este dat ca parametru. Să se apeleze această funcţie
-- într-un bloc PL/SQL.
create or replace function find_employees_num(man_id emp_pnu.manager_id%type) return number as
  type t_date is table of date;
  v_dates t_date;
  rez emp_pnu.employee_id%type;
begin
  select distinct hire_date bulk collect into v_dates
  from emp_pnu
  where manager_id = man_id
  order by 1 desc;
  
  select count(employee_id) into rez
  from emp_pnu
  where hire_date >= v_dates(1);
  
  return rez;
end find_employees_num;

-- 3. Să se declare o procedură locală prin care să se introducă în tabelul locations o nouă
-- înregistrare.
declare
v_number number(3);

procedure add_location(loc_id loc_pnu.location_id%type,
                      str_addr loc_pnu.street_address%type,
                      post_code loc_pnu.postal_code%type,
                      city loc_pnu.city%type,
                      st_province loc_pnu.state_province%type,
                      country loc_pnu.country_id%type) as
  begin
    insert into loc_pnu values(loc_id, str_addr, post_code, city, st_province,
                               country);
  end add_location;

begin
  select employee_id into v_number 
  from employees
  where rownum=1;
end;

-- 4. Să se declare o procedură locală care are următorii parametri:
-- - p_rezultat (parametru de ieşire) de tip NUMBER;
-- - p_job_id (parametru de intrare) de tip job_id din jobs2, iniţializat cu NULL;
-- - p_titlu (parametru de intrare) de tip job_title din jobs2, iniţializat cu NULL.
-- Dacă job_id nu este NULL atunci în rezultat se va memora numărul de angajaţi având codul
-- job-ului specificat ca parametru. În caz contrar, în rezultat se va memora numărul de angajaţi care
-- au titlul job-ului dat de al treilea parametru din procedura. Trataţi excepţiile care pot apărea
declare
v_number number(3);

procedure emp_count(p_rezultat out number,
                    p_job_id in jobs2.job_id%type default null,
                    p_titlu in jobs2.job_title%type default null) as
  begin
    if p_job_id is not null then
      select count(employee_id)
      into p_rezultat
      from emp_pnu
      where job_id = p_job_id;
    else
      select count(employee_id)
      into p_rezultat
      from emp_pnu join jobs2 using(job_id)
      where job_title = p_titlu;
    end if;
  end emp_count;

begin
  select employee_id into v_number 
  from employees
  where rownum=1;
end;
-- 6. Să se creeze trei funcţii locale cu acelaşi nume care să calculeze numărul de salariaţi astfel:
-- - prima funcţie va avea ca argument codul departamentului, adică funcţia calculează numărul
-- de salariaţi din departamentul specificat;
-- - a doua funcţie va avea două argumente, unul reprezentând codul departamentului, iar
-- celălalt reprezentând anul angajării, adică funcţia va calcula numărul de salariaţi din
-- departament şi care au fost angajaţi într-un anumit an;
-- - a treia funcţie va avea trei argumente, unul reprezentând codul departamentului, unul
-- reprezentând anul angajării, iar celălalt grila de salarizare, adică funcţia va calcula numărul
-- de salariaţi din departament, care au fost angajaţi într-un anumit an şi au salariul într-o
-- anumită grilă de salarizare.
declare
val number(8);

function get_num_sal(dep_id employees.department_id%type) return number as
  v_number number(4);
  begin
    select count(employee_id)
    into v_number
    from employees
    where department_id = dep_id;
      
    return v_number;
  end get_num_sal;

function get_num_sal(dep_id employees.department_id%type, yr number) return number as
  v_number number(4);
  begin
    select count(employee_id)
    into v_number
    from employees
    where department_id = dep_id and to_char(hire_date, 'yyyy') = yr;
    
    return v_number;
  end get_num_sal;   
-- Nu stiu exact ce se cere la a treia functie. Ce e aia grila salariala?
-- Ce tip are? Nu e un fel de interval?
begin
  select employee_id into val
  from employees
  where rownum=1;
end;

-- 7. Să se creeze o funcţie pentru calculul recursiv al combinărilor.
create or replace function factorial(n number) return number as
  begin  
    if n = 1 then
      return n;
    else
      return n * factorial(n-1);
    end if;
  end factorial;

create or replace function comb(n number, k number) return number as
  begin
    return factorial(n) / (factorial(k) * factorial(n-k));
  end comb;

-- 8. Să se modifice salariul unui angajat al cărui cod este introdus ca parametru astfel încât să
-- devină media salariilor angajaţilor care câştigă comision dintr-un departament dat ca parametru.
declare
procedure modif_sal(emp_id emp_pnu.employee_id%type,
                    dep_id emp_pnu.department_id%type) as
  v_mean emp_pnu.salary%type;
  
  begin
    select avg(salary)
    into v_mean
    from employees
    where department_id = dep_id;
    
    update emp_pnu
    set salary = v_mean
    where employee_id = emp_id;
  end modif_sal;
begin
  modif_sal(999, 999);
end;

-- 9. Să se scrie o funcţie care să întoarcă, pentru un angajat al cărui cod este specificat ca
-- parametru, vechimea exprimată în luni. Să se utilizeze funcţia într-o instrucţiune SELECT care să
-- întoarcă numele angajaţilor, salariul şi numărul de luni lucrate.
create or replace function months_employed(emp_id emp_pnu.employee_id%type) return number as
  v_months number(4);
  begin
    select months_between(SYSDATE, hire_date)
    into v_months
    from emp_pnu
    where employee_id = emp_id;
    
    return v_months;
  end months_employed;

declare
type t_detail is record
(
  f_name emp_pnu.first_name%type,
  l_name emp_pnu.last_name%type,
  sal emp_pnu.salary%type,
  num_months number(4)
);
type t_details is table of t_detail;
v_names t_details;

begin
  select first_name, last_name, salary, months_employed(employee_id)
  bulk collect into v_names
  from emp_pnu;
end;