Считаем даты с учетом производственного календаря на PL/SQL

Формулировки вида “в срок до 10 рабочих дней” подразумевают вычисление даты с учетом государственных праздников и перенесенных рабочих дней. В этой статье рассмотрим способ вычисления даты с учетом производственного календаря.

Что должно быть в результате?

Результатом будет:

• функция “получить n-ый рабочий день” которая принимает стартовую дату и количество рабочих дней и возвращает ближайший рабочий день
• и таблица с государственными праздниками и перенесенными рабочими днями, из которой функция будет “черпать” информацию

Пример вызова функции:

SELECT get_nth_workday(systdate, 10) FROM dual;

Информацию о праздниках функция будет брать из таблицы. Для корректной работы функции актуализируйте информацию в таблице.

Подготовка

Нам понадобиться:

• таблица, в которой будут официальные выходные и рабочие дни
• вспомогательные функция определяющие является ли день выходным/рабочим
• и собственно функция вычисляющая n-ый рабочий день

Таблица

Создадим таблицу, содержащую выходные и рабочие дни.

CREATE TABLE production_calendar (
    day DATE PRIMARY KEY,
    holiday NUMBER(1) CHECK (holiday IN (0, 1)) NOT NULL
    );

/* Добавим комментарий к столбцу holiday */
COMMENT ON COLUMN temp.PRODUCTION_CALENDAR.holiday IS '1 - выходной день, 0 - рабочий';

В таблице два столбца:

• day - дата
• holiday - является ли дата выходным или нет (1 - является, 0 - нет)

Таблица будет содержать только те выходные и рабочие дни, которые не совпадают с обычными выходными и рабочими днями по дню недели. То есть суббота и воскресенье являются выходными если их нет в таблице production_calendar и наоборот понедельник-пятница являются рабочими если их нет в production_calendar

Примечание: в Oracle SQL нет булевого типа данных, поэтому будем использовать NUMBER со значениями 0 и 1. В свою очередь в PL/SQL есть булевый тип, но для параметров функций мы все равно будем использовать 0 и 1 для возможности вызывать функции из SQL.

Заполнение таблицы

Заполним таблицу данным за 2020 год, импортируем заранее подготовленный файл production_calendar.csv. Содержимое файла production_calendar.csv:

"day","holiday"
"2020-01-01",1
"2020-01-02",1
"2020-01-03",1
"2020-01-04",1
"2020-01-05",1
"2020-01-06",1
"2020-01-07",1
"2020-01-08",1
"2020-02-23",1
"2020-02-24",1
"2020-03-08",1
"2020-03-09",1
"2020-04-30",0
"2020-05-01",1
"2020-05-04",1
"2020-05-05",1
"2020-05-08",0
"2020-05-09",1
"2020-05-11",1
"2020-06-11",0
"2020-06-12",1
"2020-11-03",0
"2020-11-04",1
"2020-12-31",0

Я использую SQLDeveloper потому, что он обладает приятным современным интерфейсом и потребляем минимум памяти бесплатный и все действия буду выполнять в нем.

Чтобы импортировать файл в таблицу:

• в панели Connections кликаем правой кнопкой мыши на нужной таблице и выбираем импорт из файла
• в открывшемся диалоговом окне жмем “Далее” пока окно от нас не отстанет не закроется

Если формат дат или названия столбцов в файле и таблице отличаются, в диалоговом окне вместо кликанья по кнопке Далее придется выбрать соответствующие опции сопоставления столбцов и формата дат.

Убеждаемся, что все импортировалось:

SELECT * FROM production_calendar;

Если все импортировалось, IDE выведет даты праздников загруженные из нашего файла

Реализация

Теперь перейдем к функциям.

Общий вид функции в PL/SQL выглядит так:

FUNCTION [схема.]имя[( параметр[, параметр...] ) ] RETURN возвращаемый_тип
    [AUTHID DEFINER | CURRENT_USER] [DETERMINISTIC]
    [PARALLEL_ENABLE ...]
    [PIPELINED]
    [RESULT_CACHE ...]
    [ACCESSIBLE BY (program_unit_list) [AGGREGATE ...]
    [EXTERNAL ...]
IS
    [объявления]
BEGIN
    исполняемые команды
[EXCEPTION
обработчики исключений]
END [имя];

В квадратных скобках необязательные элементы, оставим только то что будем использовать и упростим вид функции:

FUNCTION имя_функции( параметры ) RETURN возвращаемый_тип
IS
    тут объявляем все переменные, функции, курсоры и т.д.
BEGIN
    исполняемые команды
EXCEPTION
    обрабатываем возникшие исключения
END имя_функции;

Теперь можно перейти к конкретным функциям.

Функция проверки выходного дня

Сначала код, потом пояснения:

CREATE OR REPLACE FUNCTION is_holiday(day_in IN DATE)
    RETURN NUMBER
IS
    CURSOR prod_cal(day_in IN VARCHAR2) IS
        SELECT
            "PC"."DAY"       "DAY",
            "PC"."HOLIDAY"   "HOLIDAY"
        FROM
            "TEMP"."PRODUCTION_CALENDAR" "PC"
        where "PC".day = day_in;
    day_row     prod_cal%ROWTYPE;
    retval      NUMBER(1);
BEGIN
    OPEN prod_cal(day_in);
    FETCH prod_cal INTO day_row;

    IF prod_cal%FOUND
    THEN
        IF day_row.holiday = 1 THEN
            retval := 1;
        ELSE
            retval := 0;
        END IF;
    ELSE
        IF TO_CHAR(day_in, 'D') > 5 THEN
            retval := 1;
        ELSE
            retval := 0;
        END IF;
    END IF;

    CLOSE prod_cal;
    RETURN retval;
EXCEPTION
    WHEN OTHERS THEN
        IF prod_cal%ISOPEN THEN
            CLOSE prod_cal;
        END IF;
END;

В заголовке функции (строки 1-2) у нас на вход подается параметр day_in c типом данных DATE и описывается возвращаемый тип NUMBER. В нашем случае это альтернатива использования типа BOOLEAN. В блоке IS (строки 3-12) объявляем:

• курсор (строки 4-10). Если не сталкивались с курсором ранее, думайте о нем как о выборке с сильно ограниченным набором действий и свойств, которые надо открывать перед чтением и закрывать после чтения
• переменная для строки из курсора (строка 11)
• переменная для возвращаемого значения (строка 12) Далее в теле функции (строки 13-33):
• открываем курсор и записываем данные из него в day_row (строки 14-15)
• проверяем был ли наш день в курсоре (строка 17)
  • если был, проверяем является ли он выходным и записываем соответствующее значение в переменную retval (строки 19-23)
  • иначе проверяем является ли день “обычным” выходным (субботой или воскресеньем) и записываем значение в retval (строки 25-29)
• далее закрываем курсор (строка 32)
• и возвращаем retval (строка 33) Блок EXEPTION (строки 34-38) в нашем случае гарантирует закрытие курсора если в процессе выполнения функции возникнет ошибка и выполнение не дойдет до 32 строки

Важно Для вычисления выходных (субботы и воскресения) в строке 25 используется выражение TO_CHAR(day_in, 'D') > 5. Здесь подразумевается русская локализация, поэтому 5-ый день недели - пятница, в некоторых локализациях неделя начинается с воскресения и результат будет отличаться.

Теперь определим функцию is_workday

CREATE OR REPLACE FUNCTION is_workday(day_in IN DATE)
    RETURN NUMBER
IS
BEGIN
    IF is_holiday(day_in) = 1 THEN
        RETURN 0;
    END IF;
    RETURN 1;
END;

тут мы проверяем является ли день выходным используя уже написанную функцию is_holiday и возвращаем противоположный результат

Функция вычисляющая n-ый рабочий день

Теперь, когда у нас есть необходимые вспомогательные функции, напишем основную:

CREATE OR REPLACE FUNCTION get_nth_workday(start_date IN DATE, n_days IN INT)
    RETURN DATE
IS
    counter        INT := n_days;
    next_day    DATE := start_date;
    step            INT;
    FUNCTION get_step(n_days IN INT)
        RETURN INT
    IS
    BEGIN
        IF n_days > 0 THEN
            RETURN 1;
        END IF;
        RETURN -1;
    END;
BEGIN
    step := get_step(n_days);
    LOOP
        EXIT WHEN counter = 0;
        next_day := next_day + step;
        IF is_workday(next_day) = 1 THEN
            counter := counter - step;
        END IF;
    END LOOP;
    return next_day;
END;

Тут в блоке объявления:

• в строке 4 объявляем переменную counter, которую будем использовать для подсчета рабочих дней
• в строке 5 объявляем следующий день next_day (это станет нашей целевой датой) и приравниваем его к параметру start_date
• далее в строках 6-8 мы объявили переменную step и функцию get_step, они нужны для определения знака в какую строну двигаться (если нам нужно получить дату большую стартовой, то +1, иначе -1) В теле функции
• вычисляем шаг (строка 17)
• далее в цикле (строки 18-24), пока counter не обнулиться станет равным 0 получаем следующий next_day и, если next_day является рабочим днем - уменьшаем количество counter на единицу.

Функция готова, проверяем:

SELECT get_nth_workday(TO_DATE('3.01.2020', 'DD.MM.YYYY'), 0);
SELECT get_nth_workday(TO_DATE('3.01.2020', 'DD.MM.YYYY'), 1);
SELECT get_nth_workday(TO_DATE('3.01.2020', 'DD.MM.YYYY'), 10);
SELECT get_nth_workday(TO_DATE('26.03.2020', 'DD.MM.YYYY'), -4);

Рабочая суббота

Написанные нами функции считают что в неделе пять рабочих дней, чтобы функции считали субботу рабочей нужно заменить 5 на 6 в функции is_holiday, а если нужно использовать и пятидневную и шестидневную неделю (например количество рабочих дней в организации может отличаться в зависимости от отдела), нужно ввести дополнительный параметр во все три функции:

CREATE OR REPLACE FUNCTION is_holiday(day_in IN DATE, sixdays IN NUMBER := 0)
    RETURN NUMBER
IS
    CURSOR prod_cal(day_in IN VARCHAR2) IS
        SELECT
            "PC"."DAY"       "DAY",
            "PC"."HOLIDAY"   "HOLIDAY"
        FROM
            "TEMP"."PRODUCTION_CALENDAR" "PC"
        where "PC".day = day_in;
    day_row         prod_cal%ROWTYPE;
    retval          NUMBER(1);
    workdays        NUMBER := 5;
BEGIN
    OPEN prod_cal(day_in);
    FETCH prod_cal INTO day_row;

    IF prod_cal%FOUND
    THEN
        IF day_row.holiday = 1 THEN
            retval := 1;
        ELSE
            retval := 0;
        END IF;
    ELSE
        IF sixdays != 0 THEN
            workdays := 6;
        END IF;

        IF TO_CHAR(day_in, 'D') > workdays THEN
            retval := 1;
        ELSE
            retval := 0;
        END IF;
    END IF;

    CLOSE prod_cal;
    RETURN retval;
EXCEPTION
    WHEN OTHERS THEN
        IF prod_cal%ISOPEN THEN
            CLOSE prod_cal;
        END IF;
END;

Что изменилось:

• в первой строке добавили параметр sixdays с типом NUMBER и значением по умолчанию. Тип NUMBER у нас опять имитирует BOOLEAN.
• в строке 13 объявили переменную workdays и поставили ей значение 5, это наш порог выходных дней
• в строках 26-28 если параметр sixdays отличен от нуля (для нас это эквивалент True), меняем значение workdays на 6
• и в строке 30 сравниваем день недели с workdays

Важно Поскольку для параметра sixdays мы используем тип NUMBER при проверке (строка 26) за ложь мы принимаем только 0. Любые другие значения будут означать что рабочая неделя состоит из 6 дней.

Изменения в остальных функциях минимальны, мы просто добавляем входной параметр sixdays и передаем его в вызываемую в исполняемом блоке функцию is_holiday

CREATE OR REPLACE FUNCTION is_workday(day_in IN DATE, sixdays IN NUMBER := 0)
    RETURN NUMBER
IS
BEGIN
    IF is_holiday(day_in, sixdays) = 1 THEN
        RETURN 0;
    END IF;
    RETURN 1;
END;

CREATE OR REPLACE FUNCTION get_nth_workday(start_date IN DATE, n_days IN INT, sixdays IN NUMBER := 0)
    RETURN DATE
IS
    workdays        INT := n_days;
    next_workday    DATE := start_date;
    step            INT;
    FUNCTION get_step(n_days IN INT)
        RETURN INT
    IS
    BEGIN
        IF n_days > 0 THEN
            RETURN 1;
        END IF;
        RETURN -1;
    END;
BEGIN
    step := get_step(n_days);
    LOOP
        EXIT WHEN workdays = 0;
        next_workday := next_workday + step;
        IF is_workday(next_workday, sixdays) = 1 THEN
            workdays := workdays - step;
        END IF;
    END LOOP;
    return next_workday;
END;

Упаковываем в пакет

У нас получилось несколько функций для работы с датами с учетом производственного календаря, две из которых зависят от других. Для упрощения поиска, модификации, и хранения функций в одном месте запакуем их в пакет.

Пакеты состоят из двух частей:

1. Спецификация. В спецификации описываются все объекты которые будут дотупны при обращении к пакету из вне.
2. Тело. В теле располагаем реализацию описанных в спецификации объектов, если они того требуют и всю необходимую для этого внутреннюю логику.

Аналогично функциям рассмотрим упрощенный общий вид пакета.

Спецификация пакета:

PACKAGE package_name
IS
    объявления переменных, констант, функций, курсоров и т.д. доступних из вне
END package_name;

Хорошо написанная спецификация, позволяет понять какие функции, процедуры, курсоры и т.д. есть в этом пакете, какие парматры они принимаю и какой тип данных возвращают без необходимости смотреть реализацию.

Тело пакета:

PACKAGE BODY package_name
IS
    обявление реализации объектов описынных в спецификации и приватных объектов
[BEGIN
    инициализационный раздел]
[EXCEPTION
    обработчики исключений]
END package_name;

В спецификацию внесем заголовки наших функций, а в тело пакета в блок объявлений копируем полный код функций:

CREATE OR REPLACE PACKAGE workdays
IS
    FUNCTION is_holiday (day_in IN DATE, sixdays IN NUMBER := 0) RETURN NUMBER;
    FUNCTION is_workday (day_in IN DATE, sixdays IN NUMBER := 0) RETURN NUMBER;
    FUNCTION get_nth_workday(start_date IN DATE, n_days IN INT, sixdays IN NUMBER := 0) RETURN DATE;
END workdays;


CREATE OR REPLACE PACKAGE BODY workdays
IS
    FUNCTION is_holiday(day_in IN DATE, sixdays IN NUMBER := 0)
        RETURN NUMBER
    IS
        CURSOR prod_cal(day_in IN VARCHAR2) IS
            SELECT
                "PC"."DAY"       "DAY",
                "PC"."HOLIDAY"   "HOLIDAY"
            FROM
                "TEMP"."PRODUCTION_CALENDAR" "PC"
            where "PC".day = day_in;
        day_row         prod_cal%ROWTYPE;
        retval          NUMBER(1);
        workdays        NUMBER := 5;
    BEGIN
        OPEN prod_cal(day_in);
        FETCH prod_cal INTO day_row;

        IF prod_cal%FOUND
        THEN
            IF day_row.holiday = 1 THEN
                retval := 1;
            ELSE
                retval := 0;
            END IF;
        ELSE
            IF sixdays != 0 THEN
                workdays := 6;
            END IF;

            IF TO_CHAR(day_in, 'D') > workdays THEN
                retval := 1;
            ELSE
                retval := 0;
            END IF;
        END IF;

        CLOSE prod_cal;
        RETURN retval;
    EXCEPTION
        WHEN OTHERS THEN
            IF prod_cal%ISOPEN THEN
                CLOSE prod_cal;
            END IF;
    END;

    FUNCTION is_workday(day_in IN DATE, sixdays IN NUMBER := 0)
        RETURN NUMBER
    IS
    BEGIN
        IF is_holiday(day_in, sixdays) = 1 THEN
            RETURN 0;
        END IF;
        RETURN 1;
    END;

    FUNCTION get_nth_workday(start_date IN DATE, n_days IN INT, sixdays IN NUMBER := 0)
        RETURN DATE
    IS
        workdays        INT := n_days;
        next_workday    DATE := start_date;
        step            INT;
        FUNCTION get_step(n_days IN INT)
            RETURN INT
        IS
        BEGIN
            IF n_days > 0 THEN
                RETURN 1;
            END IF;
            RETURN -1;
        END;
    BEGIN
        step := get_step(n_days);
        LOOP
            EXIT WHEN workdays = 0;
            next_workday := next_workday + step;
            IF is_workday(next_workday, sixdays) = 1 THEN
                workdays := workdays - step;
            END IF;
        END LOOP;
        return next_workday;
    END;

END workdays;

Готово. Теперь глядя на спецификацию мы видим все возможности пакета, а реализация скрыта в теле.

Поскольку функции теперь упакованы в пакет, доступ к ним осуществляется через точку (имя_пакета.имя_функции):

SELECT workdays.is_holiday(TO_DATE('3.01.2020', 'DD.MM.YYYY'), 0);
SELECT workdays.is_workday(TO_DATE('3.01.2020', 'DD.MM.YYYY'), 1, 1);
SELECT workdays.get_nth_workday(TO_DATE('3.01.2020', 'DD.MM.YYYY'), 10);

Источники:

• книга Oracle PL/SQL для профессионалов, Фейерштейн С. и Прибыл Б. (ISBN 978-5-496-01152-5)
  • Глава 15. Выборка данных (Про курсоры)
  • Глава 17. Процедуры, функции и параметры
  • Глава 18. Пакеты
• Про BOOLEAN в Oracle