ORACLE 计算节假日

create or replace function get_workday(starTime in date,endTime in date) return number
is
 Weekends number;
 Holidays number;
 Workdays number;
begin
--统计开始到结束有几天周末
 select count(*) into Weekends from(
        SELECT TO_CHAR (TRUNC (starTime, 'DD') - 1 + ROWNUM, 'D') week_no
        FROM all_objects
        WHERE ROWNUM <=  -- 计算从开始时间到结束时间有多少天
                  (select  ceil(endTime-starTime) from dual)
 ) where week_no in(7,1);
--从节假日表中获取开始到结束有几天节日
 select nvl(sum(decode(isholiday,0,1,1,-1)),0) into Holidays from to_yx_ykbz_holiday t--
        where hdate <= trunc(endTime,'dd') and hdate >= trunc(starTime,'dd');
        
 select  ceil(endTime-starTime) into Workdays from dual;
 
 Workdays := Workdays - Weekends - Holidays;
return(Workdays);
end;

1.获取周末需要使用一个数据all_objects表
2.获取节日需要自己建表维护，统计出天数

转载于:https://www.cnblogs.com/zealotwill/p/3411258.html