tema_lab6.sql

-- 1.
set serveroutput on;
declare
cursor c_dept is
  select department_name, avg(salary) sal
  from departments join employees using(department_id)
  group by department_name;

begin
  for i in c_dept loop
    dbms_output.put_line('In departamentul "' || i.department_name || '" media salariilor este ' || i.sal || '.');
  end loop;
end;

-- 2.
set serveroutput on;
declare
cursor c_ang is
  select last_name, first_name
  from employees
  order by last_name;
type nume is record
(
  v_nume employees.last_name%type,
  v_prenume employees.first_name%type
);
v_ang nume;
begin
  open c_ang;
  for i in 1..5 loop
    fetch c_ang into v_ang;
    if c_ang%FOUND then
      dbms_output.put_line(i || ' ' || v_ang.v_nume || ' ' || v_ang.v_prenume);
    end if;
  end loop;
  close c_ang;
end;

-- 3.
set serveroutput on;
declare
type tip_sal is table of employees.salary%type;
v_salarii tip_sal;
v_idx number(3) := &salariul;

type t_result is record
(
  ln employees.last_name%type,
  fn employees.first_name%type,
  sal employees.salary%type
);
type t_results is table of t_result;

v_rez t_results;
begin
  select distinct salary bulk collect into v_salarii
  from employees
  order by 1 desc;
  
  select last_name, first_name, salary bulk collect into v_rez
  from employees
  where salary >= v_salarii(v_idx)
  order by 3 desc;
  
  for i in 1..v_rez.last loop
    dbms_output.put_line(v_rez(i).fn || ' ' || v_rez(i).ln || ' ' || v_rez(i).sal);
  end loop;
end;