问日期+7个工作日

EN

DECLARE
  l_date DATE := SYSDATE;

  FUNCTION IS_WEEKEND(P_DATE IN DATE)
    RETURN BOOLEAN
  IS
    l_daynum VARCHAR2(1) := to_char (P_DATE, 'D');
  BEGIN
    RETURN l_daynum = '6' OR l_daynum = '7';
  END;

  FUNCTION IS_HOLIDAY(P_DATE IN DATE)
    RETURN BOOLEAN
  IS
    CURSOR c_exists IS
      SELECT 1 FROM bank_holidays WHERE date = TRUNC(P_DATE)
    ;
    l_count NUMBER;
  BEGIN
    OPEN c_exists;
    l_count := c_exists%ROWCOUNT;
    CLOSE c_exists;
    RETURN l_count > 0;
  END;

  PROCEDURE ADD_WORKING_DAYS(P_DATE IN OUT DATE, P_DAYS IN NUMBER)
  IS
    l_workdays_added NUMBER := 0;
  BEGIN
    WHILE TRUE
    LOOP
      P_DATE := P_DATE + 1;
      IF NOT IS_WEEKEND(P_DATE) AND NOT IS_HOLIDAY(P_DATE) THEN
        l_workdays_added := l_workdays_added + 1;
      END IF;
      IF l_workdays_added = P_DAYS THEN
        RETURN;
      END IF;
    END LOOP;
  END;

BEGIN
  ADD_WORKING_DAYS(l_date, 12);
END;