habr上的类似文章促使我写这篇文章：使用PostgreSQL和MS SQL的生产日历。我决定使用混合方法。一方面，仅存储日期例外，并动态生成日历，另一方面，您可以将此类日历保存到永久表中，并按日期或其他属性快速搜索。
为了进行开发，我们将使用Firebird 3.0，它与以前的版本相比已大大扩展了PSQL功能。使用日历的所有过程和功能都将封装在DATE_UTILS包中。
第一步是创建一个表以存储标准假日日期。
CREATE TABLE HOLIDAYS (
ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
AMONTH SMALLINT NOT NULL,
ADAY SMALLINT NOT NULL,
REMARK VARCHAR(255) NOT NULL,
CONSTRAINT PK_HOLIDAYS PRIMARY KEY (ID),
CONSTRAINT UNQ_HOLIDAYS UNIQUE (AMONTH, ADAY
);
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
VALUES (1, 1, 1, ' ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
VALUES (2, 1, 7, '');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
VALUES (3, 2, 23, ' ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
VALUES (4, 3, 8, ' ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
VALUES (5, 5, 1, ' ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
VALUES (6, 5, 9, ' ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
VALUES (7, 6, 12, ' ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
VALUES (8, 11, 4, ' ');
COMMIT;这样的表格将帮助我们自动完成日历的填写过程，以免在每个周末都增加假期。
现在让我们创建一个表来存储异常。它将存储指定为工作日的两个工作日（已成为周末），反之亦然。
此外，您可以在任何日期留下任意注释。
CREATE TABLE CALENDAR_NOTES (
BYDATE DATE NOT NULL,
DAY_TYPE SMALLINT NOT NULL,
REMARK VARCHAR(255),
CONSTRAINT PK_CALENDAR_NOTES PRIMARY KEY (BYDATE)
);字段DAY_TYPE指示日期类型：0-工作日。1天休息，2天假期。
要使用异常表，我们将创建2个存储过程，并将其放置在DATE_UTILS包中。
--
PROCEDURE SET_DATE_NOTE (
ADATE DATE,
ADAY_TYPE SMALLINT,
AREMARK VARCHAR(255))
AS
BEGIN
UPDATE OR INSERT INTO CALENDAR_NOTES (BYDATE, DAY_TYPE, REMARK)
VALUES (:ADATE, :ADAY_TYPE, :AREMARK);
END
--
PROCEDURE UNSET_DATE_NOTE (
ADATE DATE)
AS
BEGIN
DELETE FROM CALENDAR_NOTES
WHERE BYDATE = :ADATE;
END
与PostgreSQL不同，Firebird没有用于生成一系列值的特殊功能。可以使用递归CTE完成这种生成，但是在这种情况下，我们将受到递归深度的限制。我们将更轻松一些，编写一个自定义的选择性存储过程以生成日期序列并将其放置在DATE_UTILS包中。
--
-- 1
PROCEDURE GENERATE_SERIES (
MIN_DATE DATE,
MAX_DATE DATE)
RETURNS (
BYDATE DATE)
AS
BEGIN
IF (MIN_DATE > MAX_DATE) THEN
EXCEPTION E_MIN_DATE_EXCEEDS;
BYDATE = MIN_DATE;
WHILE (BYDATE <= MAX_DATE) DO
BEGIN
SUSPEND;
BYDATE = BYDATE + 1;
END
END
该过程提供了防止循环的保护；如果最小日期大于最大日期，则将引发异常E_MIN_DATE_EXCEEDS，其定义如下：
CREATE EXCEPTION E_MIN_DATE_EXCEEDS ' ';现在，让我们继续动态生成日历。如果异常表中包含日期，则将显示日期类型和异常表中的注释。如果例外表中没有日期，但带假期的表中存在该日期，则我们将显示假期表中的注释。周末取决于星期几，其余日期有效。所描述的算法是通过以下请求实现的
SELECT
D.BYDATE,
CASE
WHEN NOTES.DAY_TYPE IS NOT NULL THEN NOTES.DAY_TYPE
WHEN HOLIDAYS.ID IS NOT NULL THEN 2
WHEN EXTRACT(WEEKDAY FROM D.BYDATE) IN (0, 6) THEN 1
ELSE 0
END AS DATE_TYPE,
COALESCE(NOTES.REMARK, HOLIDAYS.REMARK) AS REMARK
FROM DATE_UTILS.GENERATE_SERIES(:MIN_DATE, :MAX_DATE) D
LEFT JOIN HOLIDAYS
ON HOLIDAYS.AMONTH = EXTRACT(MONTH FROM D.BYDATE) AND
HOLIDAYS.ADAY = EXTRACT(DAY FROM D.BYDATE)
LEFT JOIN CALENDAR_NOTES NOTES
ON NOTES.BYDATE = D.BYDATE让我们将此查询保存到选择性存储过程中，并添加其他一些列的输出
--
PROCEDURE GET_CALENDAR (
MIN_DATE DATE,
MAX_DATE DATE)
RETURNS (
BYDATE DATE,
YEAR_OF SMALLINT,
MONTH_OF SMALLINT,
DAY_OF SMALLINT,
WEEKDAY_OF SMALLINT,
DATE_TYPE SMALLINT,
REMARK VARCHAR(255))
AS
BEGIN
FOR
SELECT
D.BYDATE,
EXTRACT(YEAR FROM d.BYDATE) AS YEAR_OF,
EXTRACT(MONTH FROM d.BYDATE) AS MONTH_OF,
EXTRACT(DAY FROM d.BYDATE) AS DAY_OF,
EXTRACT(WEEKDAY FROM d.BYDATE) AS WEEKDAY_OF,
CASE
WHEN NOTES.DAY_TYPE IS NOT NULL THEN NOTES.DAY_TYPE
WHEN HOLIDAYS.ID IS NOT NULL THEN 2
WHEN EXTRACT(WEEKDAY FROM D.BYDATE) IN (0, 6) THEN 1
ELSE 0
END AS DATE_TYPE,
COALESCE(NOTES.REMARK, HOLIDAYS.REMARK) AS REMARK
FROM DATE_UTILS.GENERATE_SERIES(:MIN_DATE, :MAX_DATE) D
LEFT JOIN HOLIDAYS
ON HOLIDAYS.AMONTH = EXTRACT(MONTH FROM D.BYDATE) AND
HOLIDAYS.ADAY = EXTRACT(DAY FROM D.BYDATE)
LEFT JOIN CALENDAR_NOTES NOTES
ON NOTES.BYDATE = D.BYDATE
INTO BYDATE,
YEAR_OF,
MONTH_OF,
DAY_OF,
WEEKDAY_OF,
DATE_TYPE,
REMARK
DO
SUSPEND;
END添加一些功能以俄语显示星期几，月份名称和日期类型。
--
FUNCTION GET_WEEKDAY_NAME(AWEEKDAY SMALLINT) RETURNS CHAR(2)
AS
BEGIN
RETURN CASE AWEEKDAY
WHEN 1 THEN ''
WHEN 2 THEN ''
WHEN 3 THEN ''
WHEN 4 THEN ''
WHEN 5 THEN ''
WHEN 6 THEN ''
WHEN 0 THEN ''
END;
END
--
FUNCTION GET_MONTH_NAME(AMONTH SMALLINT) RETURNS VARCHAR(10)
AS
BEGIN
RETURN CASE AMONTH
WHEN 1 THEN ''
WHEN 2 THEN ''
WHEN 3 THEN ''
WHEN 4 THEN ''
WHEN 5 THEN ''
WHEN 6 THEN ''
WHEN 7 THEN ''
WHEN 8 THEN ''
WHEN 9 THEN ''
WHEN 10 THEN ''
WHEN 11 THEN ''
WHEN 12 THEN ''
END;
END
--
FUNCTION GET_DAY_TYPE_NAME(ADAY_TYPE SMALLINT) RETURNS VARCHAR(11)
AS
BEGIN
RETURN CASE ADAY_TYPE
WHEN 0 THEN ''
WHEN 1 THEN ''
WHEN 2 THEN ''
END;
END现在，我们可以使用以下查询显示日历：
SELECT
D.BYDATE AS BYDATE,
D.YEAR_OF,
DATE_UTILS.GET_MONTH_NAME(D.MONTH_OF) AS MONTH_NAME,
D.DAY_OF,
DATE_UTILS.GET_WEEKDAY_NAME(D.WEEKDAY_OF) AS WEEKDAY_NAME,
DATE_UTILS.GET_DAY_TYPE_NAME(D.DATE_TYPE) AS DATE_TYPE,
D.REMARK AS REMARK
FROM DATE_UTILS.GET_CALENDAR(DATE '01.05.2019', DATE '31.05.2019') D
BYDATE YEAR_OF MONTH_NAME DAY_OF WEEKDAY_NAME DATE_TYPE REMARK
=========== ======= ========== ======= ============ =========== ======================
2019-05-01 2019 1
2019-05-02 2019 2
2019-05-03 2019 3
2019-05-04 2019 4
2019-05-05 2019 5
2019-05-06 2019 6 <null>
2019-05-07 2019 7 <null>
2019-05-08 2019 8 <null>
2019-05-09 2019 9
2019-05-10 2019 10
2019-05-11 2019 11 <null>
2019-05-12 2019 12 <null>
2019-05-13 2019 13 <null>
2019-05-14 2019 14 <null>
2019-05-15 2019 15 <null>
2019-05-16 2019 16 <null>
2019-05-17 2019 17 <null>
2019-05-18 2019 18 <null>
2019-05-19 2019 19 <null>
2019-05-20 2019 20 <null>
BYDATE YEAR_OF MONTH_NAME DAY_OF WEEKDAY_NAME DATE_TYPE REMARK
=========== ======= ========== ======= ============ =========== ==================
2019-05-21 2019 21 <null>
2019-05-22 2019 22 <null>
2019-05-23 2019 23 <null>
2019-05-24 2019 24 <null>
2019-05-25 2019 25 <null>
2019-05-26 2019 26 <null>
2019-05-27 2019 27 <null>
2019-05-28 2019 28 <null>
2019-05-29 2019 29 <null>
2019-05-30 2019 30 <null>
2019-05-31 2019 31 <null>
如果需要将日期标记为周末或工作日，请使用以下查询：
EXECUTE PROCEDURE DATE_UTILS.SET_DATE_NOTE(date '05.05.2019', 1, ' ');
要从排除列表中删除日期，您需要运行查询
EXECUTE PROCEDURE DATE_UTILS.UNSET_DATE_NOTE(date '05.05.2019');
现在，让我们创建一个表来存储生产日历，并编写一个过程来填充它。
CREATE TABLE CALENDAR (
BYDATE DATE NOT NULL,
YEAR_OF SMALLINT NOT NULL,
MONTH_OF SMALLINT NOT NULL,
DAY_OF SMALLINT NOT NULL,
WEEKDAY_OF SMALLINT NOT NULL,
DATE_TYPE SMALLINT NOT NULL,
REMARK VARCHAR(255),
CONSTRAINT PK_CALENDAR PRIMARY KEY (BYDATE)
);
-- /
PROCEDURE FILL_CALENDAR (
MIN_DATE DATE,
MAX_DATE DATE)
AS
BEGIN
MERGE INTO CALENDAR
USING (
SELECT
BYDATE,
YEAR_OF,
MONTH_OF,
DAY_OF,
WEEKDAY_OF,
DATE_TYPE,
REMARK
FROM DATE_UTILS.GET_CALENDAR(:MIN_DATE, :MAX_DATE)
) S
ON CALENDAR.BYDATE = S.BYDATE
WHEN NOT MATCHED THEN
INSERT (
BYDATE,
YEAR_OF,
MONTH_OF,
DAY_OF,
WEEKDAY_OF,
DATE_TYPE,
REMARK
)
VALUES (
S.BYDATE,
S.YEAR_OF,
S.MONTH_OF,
S.DAY_OF,
S.WEEKDAY_OF,
S.DATE_TYPE,
S.REMARK
)
WHEN MATCHED AND
(CALENDAR.DATE_TYPE <> S.DATE_TYPE OR
CALENDAR.REMARK <> S.REMARK) THEN
UPDATE SET
DATE_TYPE = S.DATE_TYPE,
REMARK = S.REMARK;
END填充用于存储日历的表的过程的设计方式是，如果其中已存在日期，则仅当排除表中发生了更改或从排除列表中删除了日期时，日期和便笺类型才会更新。
为了使异常表中的更改立即反映在日历表中，我们将略微更改SET_DATE_NOTE和UNSET_DATE_NOTE过程。第一个变化是微不足道的，我们只是向过程中添加另一个请求以更新CALENDAR表中的便笺和日期类型。
--
PROCEDURE SET_DATE_NOTE (
ADATE DATE,
ADAY_TYPE SMALLINT,
AREMARK VARCHAR(255))
AS
BEGIN
UPDATE OR INSERT INTO CALENDAR_NOTES (BYDATE, DAY_TYPE, REMARK)
VALUES (:ADATE, :ADAY_TYPE, :AREMARK);
--
UPDATE CALENDAR
SET DATE_TYPE = :ADAY_TYPE,
REMARK = :AREMARK
WHERE BYDATE = :ADATE
AND (DATE_TYPE <> :ADAY_TYPE OR REMARK <> :AREMARK);
END删除日期的注释有些棘手，因为我们必须返回日期被排除之前的注释。为此，我们使用相同的逻辑来确定日期类型和GET_CALENDAR过程中已使用的注释。
--
PROCEDURE UNSET_DATE_NOTE (
ADATE DATE)
AS
BEGIN
DELETE FROM CALENDAR_NOTES
WHERE BYDATE = :ADATE;
--
MERGE INTO CALENDAR
USING (
SELECT
:ADATE AS BYDATE,
CASE
WHEN HOLIDAYS.ID IS NOT NULL THEN 2
WHEN EXTRACT(WEEKDAY FROM :ADATE) IN (0, 6) THEN 1
ELSE 0
END AS DATE_TYPE,
HOLIDAYS.REMARK AS REMARK
FROM RDB$DATABASE
LEFT JOIN HOLIDAYS ON
HOLIDAYS.AMONTH = EXTRACT(MONTH FROM :ADATE) AND
HOLIDAYS.ADAY = EXTRACT(DAY FROM :ADATE)
) S
ON CALENDAR.BYDATE = S.BYDATE
WHEN MATCHED THEN
UPDATE SET
DATE_TYPE = S.DATE_TYPE,
REMARK = S.REMARK;
END
您可以使用以下查询从表中显示日历：
SELECT
D.BYDATE AS BYDATE,
D.YEAR_OF,
DATE_UTILS.GET_MONTH_NAME(D.MONTH_OF) AS MONTH_NAME,
D.DAY_OF,
DATE_UTILS.GET_WEEKDAY_NAME(D.WEEKDAY_OF) AS WEEKDAY_NAME,
DATE_UTILS.GET_DAY_TYPE_NAME(D.DATE_TYPE) AS DATE_TYPE,
D.REMARK AS REMARK
FROM CALENDAR D
WHERE D.BYDATE BETWEEN DATE '01.05.2019' AND DATE '31.05.2019'BYDATE YEAR_OF MONTH_NAME DAY_OF WEEKDAY_NAME DATE_TYPE REMARK
=========== ======= ========== ======= ============ =========== ======================
2019-05-01 2019 1
2019-05-02 2019 2
2019-05-03 2019 3
2019-05-04 2019 4
2019-05-05 2019 5
2019-05-06 2019 6 <null>
2019-05-07 2019 7 <null>
2019-05-08 2019 8 <null>
2019-05-09 2019 9
2019-05-10 2019 10
2019-05-11 2019 11 <null>
2019-05-12 2019 12 <null>
2019-05-13 2019 13 <null>
2019-05-14 2019 14 <null>
2019-05-15 2019 15 <null>
2019-05-16 2019 16 <null>
2019-05-17 2019 17 <null>
2019-05-18 2019 18 <null>
2019-05-19 2019 19 <null>
2019-05-20 2019 20 <null>
BYDATE YEAR_OF MONTH_NAME DAY_OF WEEKDAY_NAME DATE_TYPE REMARK
=========== ======= ========== ======= ============ =========== ==================
2019-05-21 2019 21 <null>
2019-05-22 2019 22 <null>
2019-05-23 2019 23 <null>
2019-05-24 2019 24 <null>
2019-05-25 2019 25 <null>
2019-05-26 2019 26 <null>
2019-05-27 2019 27 <null>
2019-05-28 2019 28 <null>
2019-05-29 2019 29 <null>
2019-05-30 2019 30 <null>
2019-05-31 2019 31 <null>就这样。我们能够即时生成生产日历，管理日期异常，并将日历保存在表格中以便快速查找日期。可以在此处找到用于创建表和日历包的脚本。