Oracle 自定义函数

http://www.oracle.com.cn/archiver/?tid-17996.html

 

1.在一段时间内的工作日（天数）

create or replace FUNCTION WORKDAYS
( startd IN DATE
, endd IN DATE
) RETURN NUMBER AS
days number;
BEGIN
  select (trunc(endd-startd)-((case WHEN (8-to_number(to_char(startd,'D')))
 > trunc(endd -startd)+1 THEN 0 ELSE trunc((trunc(endd-startd)-  
 (8-to_number(to_char(startd,'D'))))/7)+1 END) +(case WHEN  mod(8-to_char(startd,'D'),7) 
 > trunc(endd - startd)-1  THEN  0  ELSE  trunc((trunc(endd-startd)-  
(mod(8-to_char(to_date('2010-3-1','yyyy-MM-dd'),'D'),7)+1))/7) + 1 END))) as workingdays into days from dual;
  RETURN days;
END WORKDAYS;

 

2.

create or replace
FUNCTION GETWORKDAY2
( sdate IN DATE
, edate IN DATE
) RETURN NUMBER AS
 holidays number;
 st Date;
 en Date;
BEGIN
holidays:=0;
 for h in (select holiday_id hid from T_ATTE_HOLIDAYS) loop
    select DATE_START into st from T_ATTE_HOLIDAYS where holiday_id=h.hid;
    select DATE_END into en from T_ATTE_HOLIDAYS where holiday_id=h.hid;
     if(st>sdate and en>edate and edate>=st) then
      begin
      --holidays:=edate-st+1+holidays;
        holidays:=workdays(st,edate+1)+holidays;
       dbms_output.put_line('left');
      end;
      Elsif(st<=sdate and en>edate) then
        begin
       -- holidays:=edate-sdate+1+holidays;
          holidays:=workdays(sdate,edate+1)+holidays;
         dbms_output.put_line('midder');
        end;
      Elsif(st<sdate and en<edate and sdate<en) then
        begin
       -- holidays:=en-sdate+1+holidays;
          holidays:=workdays(sdate,en+1)+holidays;
         dbms_output.put_line('right');
        end;
      Elsif(st>=sdate and en<=edate) then
        begin
       -- holidays:=en-st+1+holidays;
          holidays:=workdays(st,en+1)+holidays;
         dbms_output.put_line('outer');
        end;
      else
        begin
        holidays:=0+holidays;
         dbms_output.put_line('else');
        end;
      end if;
  end loop;
  RETURN holidays;
END GETWORKDAY2;