oracle 节假日天数,强大的PLSQL - 计算两个日期之间的工作日天数-除去(周末和公共假日...

用php写了一个函数,实现的功能是 计算两个日期之间的工作日天数-除去(周末和公共假日)写了 300多行的代码,  实现公共假日从文件中读取, 或者从数据库提取, 然后传入两个日期,就能返回想要的结果, 无奈之下想到了PLSQL, 同事几分钟后给我一段简短的代码. 着实让我吃了一惊..

下面是代码:

CREATE OR REPLACE FUNCTION calcDates(START_DATE IN DATE, END_DATE IN DATE)

RETURN number IS

No_of_DAYS number;

BEGIN

IF START_DATE < END_DATE THEN

SELECT count(1) days

INTO NO_OF_DAYS

FROM (SELECT DISTINCT trunc(START_DATE) + level - 1 dayList

FROM dual

connect BY trunc(START_DATE) + level - 1 < = trunc(END_DATE)) A

where not exists (select 1

from dp_tbl_public_holiday b

where to_date(b.ph_date, 'YYYYMMDD') = A.dayList)

and to_char(dayList, 'D') not in (1, 7);

ELSE

SELECT 0 - count(1) days

INTO NO_OF_DAYS

FROM (SELECT DISTINCT trunc(END_DATE) + level - 1 dayList

FROM dual

conn