-- 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 CLASSICAL FUNCTION IN WHERE CLAUSE

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

-- 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    (
  p_manager_id in number,
  p_manager_hire_date in date )
  return number

is
  report_hire_date date;
  before integer:=0;
  after integer:=0;
begin
  for report in (
    select hire_date, employee_id from employees where manager_id = p_manager_id )
  loop

    case report.hire_date <= p_manager_hire_date
      when true  then before := before + 1;
      when false then after  := after + 1;
    end case;
  end loop;
   
  case ( before > after ) and ( before+after > 0 )
    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 employee_id manager_employee_id
    from employees managers
    where Most_Reports_Before_Manager ( employee_id, hire_date ) = 1;