-- Sample To Illustrate Table Functions And Cursor Expressions

-- “YOUNG MANAGERS” SCENARIO

-- Find those managers in the employees table, the majority of whose direct reports were hired
-- before the manager

-- CLASSICAL PROCEDURAL APPROACH

-- This scenario needs the hr sample schema that comes along with Oracle9i database

-- Connect hr/hr@9i

drop table young_managers_classical;


create table young_managers_classical as
  select employee_id from employees where 1=2;

declare
  cursor managers is
    select
      employee_id, hire_date,

      cursor (
              select hire_date
                from employees reports
                where reports.manager_id = managers.employee_id
             )
      from employees managers;
  manager_employee_id employees.employee_id%type;
  manager_hire_date employees.hire_date%type;

  reports sys_refcursor;
  type report_hire_dates_t is table of employees.hire_date%type
                              index by binary_integer;
  report_hire_dates report_hire_dates_t;
  before integer; after integer;
begin
  open managers;
  loop
    before:=0; after:=0;

    fetch managers into manager_employee_id, manager_hire_date, reports;
    exit when managers%notfound;
    fetch reports bulk collect into report_hire_dates;
    if report_hire_dates.count > 0
    then
      for j in report_hire_dates.first..report_hire_dates.last
      loop
        case report_hire_dates(j) <= manager_hire_date
          when true  then before := before + 1;
          when false then after  := after + 1;

        end case;
      end loop;
    end if;
    if before > after then
      insert into young_managers_classical values ( manager_employee_id ); end if;
  end loop;
  close managers;
end;
/