Комментарии к статье "Реализация календаря средствами СУБД Oracle"

Здесь приводятся комментарии к статье, а также альтернативные варианты реализации календаря. Уверенно могу сказать, что вместе с этими комментариями тема календарей в Oracle покрыта достаточно плотно и даже если Вас не устраивает ни один из трех предложенных вариантов, то приведенной информации должно быть достаточно для построения Вашего собственного решения.

Высказанные замечания и предложения достаточно серьёзны и окончательное решение можно было бы переделать. Но я оставил его без изменений, т.к. главной задачей статьи считаю не реализацию календаря. Мне хотелось показать процесс разработки изнутри, показать решение часто встречающихся задач, а календарь лишь очень удачно попался под руку.

Комментарии от Vitaliy Lyanchevskiy

Номер недели для целей запроса можно получить гораздо проще:

  trunc(((TRUNC(p_calendar.get_date,'MM')+ROWNUM-1)-to_date('03.01.0001','dd.mm.yyyy'))/7) AS week_num

где '03.01.0001' - это любой понедельник.

Комментарии от Vladimir Begun.

0. Календари вещь сложная и трудоёмкая.

1. Постановка. Вижу, что постановка задачи неполна или не до конца
описана. Прав ли я что иллюстрируется только программное решение,
конкретного набора лет скажем наших веков (20-21 н.э.) -- т.е. в
статье определён свой стандарт для вывода календаря? Как на счёт
общепринятых, по крайней мере западных (разумеется CIS включаем)
календарей? -- мне кажется что нельзя показывать календарь в
отрыве NLS установок (c поддержкой номера дня в недели) или
нужно оговорить для каких NLS календарь работает.

2. Реализация.

а) Как я уже говорил в частной переписке, PL/SQL код очень уж
часто вызывается, для месяца в котором 31 день у меня получилось
достаточно много, около 94 (если не ошибаюсь) переключения
контекста.

б) Мне кажется что использование all_objects в данном случае
не очень правильно. Начнём хотя бы с того что это достаточно
сложное view, с зависимостями и проверкой прав пользователя
на объекты, вывод прост -- внутри это работает сложнее, чем
таблица с 31 строчкой, из которой данные просто выбираются
без всяких проверок.

в) Вложенные DECODE для расчёта week_num

DECODE (
   TO_CHAR(p_calendar.get_date, 'MM'),
   '12', DECODE (
          TO_CHAR(TRUNC(p_calendar.get_date,'MM')+ROWNUM-1,'IW'),
          '01', '54',
          TO_CHAR(TRUNC(p_calendar.get_date,'MM')+ROWNUM-1,'IW')
         ),
   '01', DECODE (
          TO_CHAR(TRUNC(p_calendar.get_date,'MM')+ROWNUM-1,'IW'),
          '52', '00',
          '53', '00',
          TO_CHAR(TRUNC(p_calendar.get_date,'MM')+ROWNUM-1,'IW')
         ),
   TO_CHAR(TRUNC(p_calendar.get_date,'MM')+ROWNUM-1, 'IW')
) AS week_num

наверное можно переписать так (я не проверял, но вроде бы всё
правильно)

DECODE(TO_CHAR(p_calendar.get_date, 'MM')
     || TO_CHAR(TRUNC(p_calendar.get_date,'MM')+ROWNUM-1,'IW')
      , '1201', '54'
      , '0152', '00'
      , '0153', '00'
              , TO_CHAR(TRUNC(p_calendar.get_date,'MM')+ROWNUM-1, 'IW')
) AS week_num

г) Расчёт месяца года, сложная вещь, это очень сильно сочетается
с пунктами 0 и 1.

Положим я хочу посмотреть календарь за октябрь 1582 года,
года Папа Георгий (как известно из истории календарей) сделал
реформу -- код работать не будет. Он также работать не будет
для ноября, декабря 1582 года, поскольку считает недели года,
а не недели месяца, т.е. с точки зрения реализации это пример
error propagation.

http://www.coins.ru/numizm/glossary/showarticle.phtml?word=cal_grig
http://www.astronet.ru:8100/db/msg/1162236/
http://calends.webzone.ru/3000.html
http://www.ernie.cummings.net/calendar.htm
http://webexhibits.org/calendars/timeline.html
http://astro.nmsu.edu/~lhuber/leaphist.html
http://www.calendarzone.com/

Скажу честно, я и не подозревал, что так мало знаю о календарях, пока не прочитал сообщение Владимира.

Nicholay Logazyak предложил альтернативный вариант, который после замечаний Vladimir M. Zakharychev и Vladimir Begun приобрел такой вид:

SELECT
TO_CHAR(DAY,'mm_yy') MM_YY,
MAX(DECODE (day_of_week, 'MON', dd, NULL)) Mon,
MAX(DECODE (day_of_week, 'TUE', dd, NULL)) Tue,
MAX(DECODE (day_of_week, 'WED', dd, NULL)) Wed,
MAX(DECODE (day_of_week, 'THU', dd, NULL)) Thu,
MAX(DECODE (day_of_week, 'FRI', dd, NULL)) Fri,
MAX(DECODE (day_of_week, 'SAT', dd, NULL)) Sat,
MAX(DECODE (day_of_week, 'SUN', dd, NULL)) Sun
FROM (
SELECT dd.N+1 dd,
DAY,
TO_CHAR (MM_DAYS.DAY + dd.N,
'DY', 'NLS_DATE_LANGUAGE=AMERICAN')  day_of_week,
DECODE ( TO_CHAR( MM_DAYS.DAY, 'MM')||TO_CHAR( MM_DAYS.DAY+dd.N,'IW'),
'1201', '54',
'0152', '00',
'0153', '00',
TO_CHAR(MM_DAYS.DAY+dd.N, 'IW')
) WEEK_NUM
FROM
    ( SELECT rownum-1 N from all_objects WHERE rownum <= 31 ) dd,
    ( SELECT to_date( '01.'||MM||'.'||YY, 'dd.mm.yy' ) DAY
      FROM ( SELECT rownum MM from all_objects WHERE rownum <= 12 ),
		   ( SELECT rownum YY from all_objects WHERE rownum <= 50 ))
MM_DAYS
WHERE dd.N < TO_NUMBER(TO_CHAR(LAST_DAY( MM_DAYS.DAY ), 'DD'))
)
GROUP BY
TO_CHAR(DAY,'mm_yy'), week_num

Запрос к представлению на январь 2001 года теперь выглядит так :

select * from v_calendar where MM_YY = '01_01' -- MM_YY = 'MM_YY'

Данный вариант интересен тем, что не требует использования PL/SQL пакета. Календарь выводится только при помощи одной команды SELECT.

Помимо комментариев к статье Vladimir Begun предложил еще и свой вариант реализации календаря:

0.  Код работает только для Грегорианского Календаря
    Oracle9i Database Globalization Support Guide Release 2 (9.2)
    1 Overview of Globalization Support
    Globalization Support Features
    Calendars Feature

    и поддерживает "западные" NLS.

1.  В данном варианте по-прежнему нет поддержки 10.1582, возможно, её
    следует добавить, в этом случае, возможно, не следует рассматривать
    пункты 2.1 и 3.

2.  Используется IOT, хотя это не обязательно. Замечено что для 9i
    при использовании IOT делается лишний вызов get_date. Вполне вероятно
    от неё следует отказаться использовать обычную таблицу с CACHE опцией.

2.1 Это происходит в том случае если вместо ограничения по ROWNUM
    использует ограничение по значению дня.

3.  Отметку заданного дня "[ ]", можно сделать по-другому, не используя
    '[' || LPAD(TO_CHAR(dd, 'FM99'), 2) || ']'... т.е. не тратя ресурсы
    на разбор, просто сохранив в таблице форматированные даты -- всё
    равно мы тратим 1 блок данных, лучше набить его "под завязку" всем
    статическим.

4.  Вроде бы это всё "тараканы", если кто обнаружит что-то ещё, сообщайте.

REM *********************************************************************
REM Name:       cal
REM Purpose:    Mimics cal(1) Unix command
REM Author:     Copyright(c) 2000,2002 Vladimir Begun, All Rights Reserved
REM             No warranty, the code is distributed "as is". This header
REM             must not be removed in case of using the code or the
REM             algorithm.
REM $Id$
REM TODO:       XXX

DROP TABLE calendar_day
/
CREATE TABLE calendar_day (
  dd                               NUMBER(2)
, rn                               NUMBER(1)
, CONSTRAINT pk$calendar_day$dd PRIMARY KEY(dd)
, CONSTRAINT ck$calendar_day$dd CHECK(dd >= 1 AND dd <= 31)
, CONSTRAINT nn$calendar_day$rn CHECK(rn IS NOT NULL)
, CONSTRAINT ck$calendar_day$rn CHECK(rn >= 1 AND rn <= 7)
)
ORGANIZATION INDEX
STORAGE (INITIAL 16K NEXT 16K PCTINCREASE 0 MAXEXTENTS 1)
/
INSERT INTO calendar_day
  SELECT ROWNUM
       , DECODE(MOD(ROWNUM, 7)
               , 0, 7
                  , MOD(ROWNUM, 7)
         )
    FROM all_objects
   WHERE ROWNUM <= 31
/
/*
CREATE TABLE calendar_day (
  rn                               NUMBER(1)
, CONSTRAINT nn$calendar_day$rn CHECK(rn IS NOT NULL)
, CONSTRAINT ck$calendar_day$rn CHECK(rn >= 1 AND rn <= 7)
)
STORAGE (INITIAL 16K NEXT 16K PCTINCREASE 0 MAXEXTENTS 1) CACHE
/
INSERT INTO calendar_day
  SELECT DECODE(MOD(ROWNUM, 7)
               , 0, 7
                  , MOD(ROWNUM, 7)
         )
    FROM all_objects
   WHERE ROWNUM < 32
/
*/
COMMIT
/
CREATE OR REPLACE PACKAGE pkg$cal
AS
--  gn_call_count                    PLS_IINTEGER;

  FUNCTION get_date
  RETURN DATE;

  PROCEDURE set_date (
    ad_date_i                      DATE DEFAULT SYSDATE
  );

END pkg$cal;
/
CREATE OR REPLACE PACKAGE BODY pkg$cal
AS

  gd_date                          DATE NOT NULL := SYSDATE;

  FUNCTION get_date
  RETURN DATE
  IS
  BEGIN
--    gn_call_count := gn_call_count + 1;
    RETURN gd_date;
  END get_date;

  PROCEDURE set_date (
    ad_date_i                      DATE DEFAULT SYSDATE
  )
  IS 
  BEGIN 
--    gn_call_count := 0;
    IF (ad_date_i IS NULL)
    THEN
      gd_date := SYSDATE;
    ELSE
      gd_date := ad_date_i;
    END IF;
  END set_date;

END pkg$cal;
/
CREATE OR REPLACE VIEW viw$cal
AS
  SELECT
/*
   Copyright(c) 2000,2002 Vladimir Begun, All Rights Reserved.
   No warranty, the code is distributed "as is". This header
   must not be removed in case of using the code or the algorithm.
*/
         d1
       , d2
       , d3
       , d4
       , d5
       , d6
       , d7
    FROM (
         SELECT 'DAYS' AS d0
              , MAX(DECODE(day_of_week, '1', name_of_day)) AS d1
              , MAX(DECODE(day_of_week, '2', name_of_day)) AS d2
              , MAX(DECODE(day_of_week, '3', name_of_day)) AS d3
              , MAX(DECODE(day_of_week, '4', name_of_day)) AS d4
              , MAX(DECODE(day_of_week, '5', name_of_day)) AS d5
              , MAX(DECODE(day_of_week, '6', name_of_day)) AS d6
              , MAX(DECODE(day_of_week, '7', name_of_day)) AS d7
           FROM (
                SELECT LPAD(TO_CHAR(dt.dt + dd, 'DY'), 3) AS name_of_day
                     , TO_CHAR(dt.dt + dd, 'D') AS day_of_week
                  FROM calendar_day
                     , (
                       SELECT pkg$cal.get_date AS dt
                         FROM sys.dual
                        GROUP BY pkg$cal.get_date
                       ) dt
                 WHERE dd <= 7
                )
         UNION ALL
         SELECT 'WEEKS' || week_of_month AS d0
              , MAX(DECODE(day_of_week, '1', day_of_month)) AS d1
              , MAX(DECODE(day_of_week, '2', day_of_month)) AS d2
              , MAX(DECODE(day_of_week, '3', day_of_month)) AS d3
              , MAX(DECODE(day_of_week, '4', day_of_month)) AS d4
              , MAX(DECODE(day_of_week, '5', day_of_month)) AS d5
              , MAX(DECODE(day_of_week, '6', day_of_month)) AS d6
              , MAX(DECODE(day_of_week, '7', day_of_month)) AS d7
           FROM (
                SELECT DECODE(dd
                             , def_dt, '[' || LPAD(TO_CHAR(dd, 'FM99'), 2) || ']'
                                     , LPAD(TO_CHAR(dd, 'FM99'), 3)
                       ) AS day_of_month
                     , SIGN(SIGN(rn - TO_NUMBER(TO_CHAR(dt.mo + dd - 1, 'D'))) - 1)
                     + TO_NUMBER(TO_CHAR(dt.mo + dd - 1, 'W')) AS week_of_month
                     , TO_CHAR(dt.mo + dd - 1, 'D') AS day_of_week
                  FROM (
                       SELECT TO_NUMBER(TO_CHAR(LAST_DAY(pkg$cal.get_date), 'DD')) AS days_in_month
                            , TO_NUMBER(TO_CHAR(pkg$cal.get_date, 'DD')) AS def_dt
                            , TRUNC(pkg$cal.get_date, 'MM') AS mo
                         FROM sys.dual
                        GROUP BY pkg$cal.get_date
                       ) dt
                     , calendar_day
                 WHERE dd <= days_in_month
                )
          GROUP BY
                week_of_month
         )
/
-- some tests
-- CLEAR SCREEN
VAR n NUMBER
SET FEEDBACK OFF TERMOUT ON HEADING OFF VERIFY OFF

ALTER SESSION SET NLS_TERRITORY=AMERICA
/
ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN
/
PROMPT
DEFINE dt = SYSDATE;
EXEC pkg$cal.set_date(&dt);
PROMPT
PROMPT &dt
SELECT * FROM viw$cal
/
ALTER SESSION SET NLS_TERRITORY=GERMANY
/
ALTER SESSION SET NLS_DATE_LANGUAGE=GERMAN
/
PROMPT
DEFINE dt = SYSDATE;
EXEC pkg$cal.set_date(&dt);
PROMPT
PROMPT &dt
SELECT * FROM viw$cal
/
DEFINE dt = "27.11.1976";
EXEC pkg$cal.set_date(TO_DATE('&dt', 'DD.MM.YYYY'));
PROMPT
PROMPT &dt
SELECT * FROM viw$cal
/
ALTER SESSION SET NLS_TERRITORY=AMERICA
/
ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN
/
PROMPT
PROMPT &dt
SELECT * FROM viw$cal
/
DEFINE dt = "18.04.2002";
EXEC pkg$cal.set_date(TO_DATE('&dt', 'DD.MM.YYYY'));
PROMPT
PROMPT &dt
SELECT * FROM viw$cal
/
ALTER SESSION SET NLS_TERRITORY=GERMANY
/
ALTER SESSION SET NLS_DATE_LANGUAGE=GERMAN
/
DEFINE dt = "01.01.2001";
EXEC pkg$cal.set_date(TO_DATE('&dt', 'DD.MM.YYYY'));
PROMPT
PROMPT &dt
SELECT * FROM viw$cal
/
ALTER SESSION SET NLS_TERRITORY=AMERICA
/
ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN
/
DEFINE dt = "31.12.2000";
EXEC pkg$cal.set_date(TO_DATE('&dt', 'DD.MM.YYYY'));
PROMPT
PROMPT &dt
SELECT * FROM viw$cal
/
-- EXEC :n := pkg$cal.gn_call_count;
-- PRINT N

23 Июля 2002 г.

Hosted by www.Geocities.ws