-- 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

-- USING A TABLE FUNCTION

-- Connect hr/hr@9i
set serveroutput on

drop package My_Types;
drop function Young_Managers_Fn;


create or replace package My_Types is
  type employee_ids_tab is table of employees.employee_id%type;
end My_Types;
/
Show Errors
   
create or replace function Young_Managers_Fn

  return My_Types.employee_ids_tab
  pipelined
is
  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_date_t  is table of employees.hire_date%type
                               index by binary_integer;

      report_hire_dates        report_hire_date_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
      pipe row ( manager_employee_id );

    end if;
  end loop;
  close managers;
  return;
end Young_Managers_Fn;
/
Show Errors
   
create or replace view young_managers as
  select column_value manager_employee_id from table (Young_Managers_Fn());