在oracle sql中创建日期差异的自定义函数，排除周末和节假日

我需要计算两个日期之间的天数decimal, 不包括周末和节假日 by 使用自定义函数在 Oracle SQL 中。 网站上也有类似的问题；然而，正如我所看到的，它们都没有要求使用自定义函数将输出作为十进制。我需要小数的原因是为了之后能够使用/提取时间分量。如果已经有这样的问题，请分享链接。

尝试借助我在互联网上找到的附加内容编写如下函数感谢作者 https://sqljana.wordpress.com/2017/03/16/oracle-calculating-business-days-between-two-dates-in-oracle/。内部子查询单独工作正常，但它不能作为一个整体功能工作。

简而言之，这个想法是：

（计算开始日期和结束日期之间的日差）->（排除开始日期和结束日期之间的周末天数）->（排除开始日期和结束日期之间的周末天数）

当我尝试保存该函数时，出现错误PLS-00103: Encountered the symbol "end-of-file"。由于我已经是函数新手，可能缺少一些基本的东西。

最后，如果您对如何提高代码效率有任何建议，也请告诉我。

提前致谢！

CREATE OR REPLACE FUNCTION  NET_WORKING_DAYS (startdate IN DATE, enddate IN DATE)
RETURN NUMBER IS 
  WORKINGDAYS_BETWEEN NUMBER;
BEGIN

SELECT

    -- number of days between startdate and enddate
      (
        SELECT (TO_DATE('20160831150000','YYYYMMDDHH24MISS')  - TO_DATE('20160801000000','YYYYMMDDHH24MISS') ) DAYS_BETWEEN 
        FROM DUAL
      )  
    -
    -- number of weekend days (after a given date)
      (
        SELECT COUNT(1)  WEEKEND_DAYS_BETWEEN
        FROM
        (
          SELECT
            TO_DATE('20160701000000','YYYYMMDDHH24MISS') + SEQ as day_date,     --2016/07/01 is a constant/given date for this formula
            TO_CHAR(TO_DATE('20160701000000','YYYYMMDDHH24MISS') + SEQ , 'D') day_of_week
          FROM
            (
                SELECT ROWNUM-1 SEQ
                FROM   ( SELECT 1 FROM  DUAL CONNECT BY LEVEL<= 365 * 5) --5 years
            )
          ORDER BY 1
        )
        WHERE day_of_week IN (6,7) 
          AND day_date > TO_DATE('20160801000000','YYYYMMDDHH24MISS')     --this should be replaced with startdate parameter
          AND day_date < TO_DATE('20160831000000','YYYYMMDDHH24MISS')      --this should be replaced with enddate parameter
      )  
    -
    -- number of holidays (after a given date)
      (
        SELECT COUNT(1)
        FROM HOLIDAYS
        WHERE HOLIDAY_DATE > TO_DATE('20160801000000','YYYYMMDDHH24MISS')     --this should be replaced with startdate parameter
          AND HOLIDAY_DATE < TO_DATE('20160831000000','YYYYMMDDHH24MISS')      --this should be replaced with enddate parameter
      )
INTO WORKINGDAYS_BETWEEN
FROM DUAL;

RETURN WORKINGDAYS_BETWEEN;
END NET_WORKING_DAYS;

**EDIT-1：假期已在数据库中的 HOLIDAYS 表中定义，并且此日期范围从20160801000000 to 20160831000000 , 30.06.2016是假期日期。

您不需要使用行生成器枚举每天来获取工作日数 - 可以使用简单的计算来完成：

From 我的回答在这里 https://stackoverflow.com/a/43633234/1509264:

CREATE FUNCTION getWorkingDays (
  in_start_date IN  DATE,
  in_end_date   IN  DATE
) RETURN NUMBER DETERMINISTIC
IS
  p_start_date   DATE;
  p_end_date     DATE;
  p_working_days NUMBER;
  p_holiday_days NUMBER;
BEGIN
  IF in_start_date IS NULL OR in_end_date IS NULL THEN
    RETURN NUll;
  END IF;

  p_start_date := LEAST( in_start_date, in_end_date );
  p_end_date   := GREATEST( in_start_date, in_end_date );

  -- 5/7 * ( Number of days between monday of the week containing the start date
  --         and monday of the week containing the end date )
  -- + LEAST( day of week for end date, 5 )
  -- - LEAST( day of week for start date, 5 )
  p_working_days := ( TRUNC( p_end_date, 'IW' ) - TRUNC( p_start_date, 'IW' ) ) * 5 / 7
                    + LEAST( p_end_date - TRUNC( p_end_date, 'IW' ), 5 )
                    - LEAST( p_start_date - TRUNC( p_start_date, 'IW' ), 5 );

  SELECT COALESCE(
           SUM(
             LEAST( p_end_date, holiday_date + INTERVAL '1' DAY )
             - GREATEST( p_start_date, holiday_date )
           ),
           0
         )
  INTO   p_holiday_days
  FROM   Holidays
  WHERE  HOLIDAY_DATE BETWEEN TRUNC( p_start_date )
                      AND     TRUNC( p_end_date )
  AND    HOLIDAY_DATE - TRUNC( HOLIDAY_DATE, 'IW' ) < 5;

  RETURN GREATEST( p_working_days - p_holiday_days, 0 );
END;
/