Oracle function 求两个日期之间的工作日

上周帮同事写了一个求两个日期之间的工作日的函数，此处留个笔记。
-------------------------------

限制条件：

1.需要一张节假日登记表

2.传入参数第一个要比第二个日期小

CREATE OR REPLACE FUNCTION GET_WORK_DAYS(STARTDAY IN VARCHAR2,
                                         ENDDAY IN VARCHAR2)
  RETURN INTEGER AS SQL_QUERY VARCHAR2(4000);
TMP_QUERY VARCHAR2(4000);
DAY_QUERY VARCHAR2(4000);

OUT_NUM INTEGER;
Z_NUM INTEGER; --总天数
TMP_NUM INTEGER;
DAY_NUM INTEGER; --判断是周几

I INTEGER;

BEGIN
  --总共天数
  DAY_QUERY := ' SELECT abs(to_date(''' || ENDDAY ||
               ''',''yyyy-mm-dd'') - to_date(''' || STARTDAY ||
               ''',''yyyy-mm-dd'')) FROM dual ';
  EXECUTE IMMEDIATE DAY_QUERY
    INTO Z_NUM;
  --输出
  --DBMS_OUTPUT.PUT_LINE(Z_NUM);
  OUT_NUM := Z_NUM+1;
  I       := 0;
  LOOP

    /*
            先判断是周几
            如果是工作日去除休息日
            如果是休息日去除工作日
    */
    IF I > Z_NUM THEN
      EXIT;
    END IF;

    SQL_QUERY := 'SELECT  to_char(to_date(''' || STARTDAY ||
                 ''', ''yyyy-mm-dd'') + ' || I ||
                 ',''d'') FROM dual';
    EXECUTE IMMEDIATE SQL_QUERY
      INTO DAY_NUM;
   -- DBMS_OUTPUT.PUT_LINE(DAY_NUM);

    TMP_QUERY := 'SELECT COUNT(1) FROM SYSTEM_FESTIVAL sf WHERE sf.sfe_type =''休息日'' AND to_char(sf.sfe_date,''yyyy-mm-dd'') = (SELECT to_char(to_date(''' ||
                 STARTDAY || ''', ''yyyy-mm-dd'') + ' || I ||
                 ',''yyyy-mm-dd'') FROM dual)';
    DAY_QUERY := 'SELECT COUNT(1) FROM SYSTEM_FESTIVAL sf WHERE sf.sfe_type =''工作日'' AND to_char(sf.sfe_date,''yyyy-mm-dd'') = (SELECT to_char(to_date(''' ||
                 STARTDAY || ''', ''yyyy-mm-dd'') + ' || I ||
                 ',''yyyy-mm-dd'') FROM dual)';
    --周一
    IF DAY_NUM = 2 THEN
      EXECUTE IMMEDIATE TMP_QUERY
        INTO TMP_NUM;
      IF TMP_NUM > 0 THEN
        OUT_NUM := OUT_NUM - 1;
      END IF;
    END IF;
    --周二
    IF DAY_NUM = 3 THEN
      EXECUTE IMMEDIATE TMP_QUERY
        INTO TMP_NUM;
      IF TMP_NUM > 0 THEN
        OUT_NUM := OUT_NUM - 1;
      END IF;
    END IF;
    --周三
    IF DAY_NUM = 4 THEN
      EXECUTE IMMEDIATE TMP_QUERY
        INTO TMP_NUM;
      IF TMP_NUM > 0 THEN
        OUT_NUM := OUT_NUM - 1;
      END IF;
    END IF;
    --周四
    IF DAY_NUM = 5 THEN
      EXECUTE IMMEDIATE TMP_QUERY
        INTO TMP_NUM;
      IF TMP_NUM > 0 THEN
        OUT_NUM := OUT_NUM - 1;
      END IF;
    END IF;
    --周五
    IF DAY_NUM = 6 THEN
      EXECUTE IMMEDIATE TMP_QUERY
        INTO TMP_NUM;
      IF TMP_NUM > 0 THEN
        OUT_NUM := OUT_NUM - 1;
      END IF;
    END IF;

    --周六
    IF DAY_NUM = 7 THEN

      EXECUTE IMMEDIATE DAY_QUERY
        INTO DAY_NUM;
      IF DAY_NUM = 0 THEN
        OUT_NUM := OUT_NUM - 1;
      END IF;
    END IF;
    --周日
    IF DAY_NUM = 1 THEN
      EXECUTE IMMEDIATE DAY_QUERY
        INTO DAY_NUM;
      IF DAY_NUM = 0 THEN
        OUT_NUM := OUT_NUM - 1;
      END IF;
    END IF;
    I         := I + 1;
  END LOOP;
 -- DBMS_OUTPUT.PUT_LINE(OUT_NUM);
  RETURN OUT_NUM;
END GET_WORK_DAYS;

如果有什么需要改进的地方大家可以留言指正，谢谢！