functions.sql

create or replace function acte_possible
  (code in varchar2, day in date, creneau_min in number)
  return number
is
  nb_actes number;
  cursor services_ouverts is
    select s.NumService
    from Service s
      left join EstOuvert eo on s.NumService = eo.NumService
      left join EstCompetent ec on s.NumService = ec.NumService
    where Jour = to_number(to_char(sysdate, 'D'))
      and ec.CodeCCAM = code;
begin
  for numserv in services_ouverts loop
    select count(*) into nb_actes;
    from Effectue e
      left join Acte a on e.NumActe = a.Numacte
      left join Ordonnance o on a.NumOrdo = o.NumOrdo
    where trunc(o.DateInter) = trunc(day)
      and e.NumService = numserv
      and e.NumCreneau >= creneau_min;

    if nb_actes <= 6 then
      return numserv
    end if;
  end loop;
  return null;
end acte_possible;
/