-- 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 FUNCTION WITH REF CURSOR PARAMETER IN WHERE CLAUSE

-- Note: Following code sample depend on the employees and departments tables. These are
-- in the hr sample schema. Scripts to create and populate this are provided with Oracle9i,
-- and it is included in the standard pre-built database. The sample is complete.

-- Connect hr/hr@9i

set serveroutput on
   
drop function Most_Reports_Before_Manager ;   
drop view young_managers ;

create or replace function Most_Reports_Before_Manager    (
  report_hire_dates_cur in sys_refcursor,

  manager_hire_date in date )
  return number
is
  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:=0; after integer:=0;
begin

  fetch report_hire_dates_cur 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;
  case before > after
    when true  then return 1;
    when false then return 0;
  end case;
end Most_Reports_Before_Manager;
/
Show Errors
   

create or replace view young_managers as
  select managers.employee_id manager_employee_id
  from employees managers
  where Most_Reports_Before_Manager
    (
      cursor ( select reports.hire_date from employees reports
                 where reports.manager_id = managers.employee_id
             ),
      managers.hire_date
    ) = 1;