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

Благодарности

После первичного опубликования статьи в relcom.comp.dbms.oracle я получил множество отзывов. Некоторые из них были настолько интересными и содержательными что для них была выделена отдельная страничка.
Настоятельно рекомендую посетить её, т.к. кроме рекомендаций по улучшению (а улучшать как оказалось, есть что) она содержит еще и два альтернативных решения.

Выражаю искреннюю признательность Vladimir Begun, Serge Furs, IgorM, Nicholay Logazyak, Vitaly Lyanchevskiy, Max Shahotsky, Johnny Smith, Stax, Andrey Vdovin, Alexander Yanushkevich, Vladimir Zakharychev, Evgeny Zybarev, Алексей Босый, Виктор Выдрин, Николай Малахов
за представленные отзывы, советы, рекомендации и просто поддержку.

Вместо предисловия или Постановка задачи
С чего начать
Определение дня недели
О матричных отчетах
Лучше меньше, да лучше или Добавление группировки
Передача параметров в представление или Печать календаря за любой месяц
О пользе тестирования
CASE против DECODE
Решение

Вместо предисловия или Постановка задачи

Сейчас очень модно делать различные веб-приложения, вот и мне довелось разрабатывать что-то подобное, связанное с календарем. Открывает пользователь страничку, где сбоку такой маленький месячный календарик и каждый день в виде ссылочки. Нажимаешь на нужный день месяца, а там тебе новости за этот день или список мероприятий, разнообразных, выводится.

В качестве базы данных для реализации приложения была выбрана, разумеется, Oracle, а в качестве средств разработки - впрочем, это не важно, т.к. мне сразу показалось, что должно существовать простое SQL решение, соответственно, никак не зависящее от средств разработки.

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

Итак, задача сводится к написанию запроса, который выводит, скажем, для текущего (Июнь, 2002г.) месяца такой результат:

 MON  TUE  WED  THU  FRI  SAT  SUN
---- ---- ---- ---- ---- ---- ----
                            1    2
   3    4    5    6    7    8    9
  10   11   12   13   14   15   16
  17   18   19   20   21   22   23
  24   25   26   27   28   29   30

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

В любом случае, даже если Вам все это:

давно известно
совершенно не интересно
лень читать

можете при необходимости просто воспользоваться готовым решением.

С чего начать

Философский вопрос. Очень важно сразу выбрать правильный путь, в противном случае все придется начинать сначала.

Коль скоро требуется написать SELECT, то в первую очередь нужно определиться с тем что выбирать (список столбцов) и откуда выбирать (фраза FROM).
Чисто интуитивно понятно, что выбирать нужно даты текущего месяца, но вот откуда? Завести таблицу со столбцом типа DATE и последовательно ее заполнить? Вероятно, несколько лет назад я так бы и сделал. Но сейчас, находясь под большим влиянием прочитанного у Томаса Кайта я решил попробовать по-другому.

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

Функция LAST_DAY возвращает последний день месяца для заданной даты, TO_CHAR с форматной маской 'DD' в паре с TO_NUMBER даст этот последний день в виде числа и что более важно это и есть то количество записей, которое нужно выбрать и которое никогда не превысит 31 (Мюнхгаузен не в счет!).
Ну а выбирать будем из любимого Томасом Кайтом представления ALL_OBJECTS:

SELECT TRUNC(SYSDATE, 'MM') + ROWNUM - 1
  FROM all_objects
 WHERE ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE), 'DD'))

TRUNC(SYS
---------
01-JUN-02
02-JUN-02
...
30-JUN-02

Начало положено!
Мы имеем запрос, который возвращает все дни текущего месяца.
Только не говорите мне, что не правильно полагаться на то, что в представлении ALL_OBJECTS всегда будет 31 запись. Да, я полагаюсь на это и считаю это допущение вполне оправданным!

Хочу обратить внимание на то, что запрос выводит дни только текущего месяца (в нем жестко зашита функция SYSDATE), хотя правильнее было бы как-то предусмотреть возможность вывода дней для разных месяцев. Этим самым я хочу разделить задачу на части. Сначала добиться результата с текущим месяцем, а уже потом заниматься параметризацией. И это правильный путь! Не нужно пытаться объять необъятное, нужно разделять и решать по отдельности (так и хочется сказать "властвовать", но это уже чей-то "копирайт" :-) ).

Определение дня недели

Ну что же, теперь самое время перейти к дням недели. Хочу остановиться на этой теме подробнее, т.к. вынужден признать, что долгое время был в неведении как программно определить день недели для заданной даты :-( . Долгое время мне это и не нужно было, а вот пару лет назад столкнулся и правильное решение нашел далеко не сразу. Очень не хотелось бы, чтобы кто-то еще тратил на это время.

С одной стороны кажется, что все просто, берем функцию

   TO_CHAR(my_date, 'DY') или TO_CHAR(my_date, 'DAY')

и получаем, соответственно, краткое (трехбуквенное) или полное название дня недели. Да вот незадача, оказывается, день недели будет возвращен на том языке, который установлен параметрами NLS пользовательской сессии. Т.е. если у пользователя задан английский, то для понедельника будет выдано MON (MONDAY), если русский, то ПНД (ПОНЕДЕЛЬНИК).

Вы уверены, что все пользователи будут пользоваться вашим приложением с одинаковыми настройками NLS? Лично я почти уверен в обратном и такой метод определения меня не устраивает.

Можно попробовать воспользоваться TO_CHAR другой форматной маской :

   TO_CHAR(my_date, 'D')

которая вместо названия возвращает номер дня недели. Ну, уж номер-то от языка не зависит! От языка номер действительно не зависит, а вот от параметра NLS определяющего территорию зависит :-( Если в России понедельник - первый день недели, то в США это уже второй.

Что делать в этой ситуации? Проверять на MON и ПНД одновременно?

   TO_CHAR(my_date, 'DY') IN ('MON', 'ПНД')

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

Наверное, в жизни каждого разработчика бывают такие моменты, когда после нескольких лет постоянного использования чего-либо ты вдруг узнаешь что-то новое, которого тебе всегда не хватало и что самое обидное находилось практически на поверхности. Я видел (читал) реакцию Стивена Ферстейна на команду @@ в sqlplus и это после того как он написал начальную версию PL/Vision. В моем случае бурю эмоций вызвали возможности функции TO_CHAR.

Ключ к решению оказался настолько прост, насколько поначалу данная проблема казалась неразрешимой. Оказывается у функции TO_CHAR есть третий параметр, который позволяет задать язык, в котором дата должна возвращаться!

   TO_CHAR(my_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN')

Всегда вернет MON для понедельника вне зависимости от NLS установок пользователей.

Кстати, а знаете, что должно вернуть логическое выражение:

   TO_CHAR(my_date, 'DAY', 'NLS_DATE_LANGUAGE=AMERICAN') = 'MONDAY'

при условии, что my_date - понедельник?

Правильно, FALSE!
Почему? Потому что функция TO_CHAR в данном случае вернет

'MONDAY   '

c хвостовыми пробелами до самого длинного (с точки зрения написания) дня недели WEDNESDAY. Соответственно, если ставить NLS_DATE_LANGUAGE в RUSSIAN, то все полные дни недели будут выравниваться до 11 символов (до ПОНЕДЕЛЬНИКа).
Для корректного использования полного дня недели нужно к форматной маске DAY добавить модификатор fm:

   TO_CHAR(my_date, 'fmDAY', 'NLS_DATE_LANGUAGE=AMERICAN') = 'MONDAY'

В завершении разговора о днях недели хочется сказать, что существует немало приложений, которые хоть и разработаны в России (или адаптированы для России), но используют английский в качестве языка для отображения даты. Вероятно, что неправильное определение дней недели это одна из причин.

О матричных отчетах

Часто в различных телеконференциях можно услышать вопрос о том, как построить матричный отчет. Причем желательно одной командой SELECT.

Рассмотрим эту проблему на любимой (по крайней мере мной) таблице EMP. Получить общую сумму зарплат(sal) в разрезе должностей(job) достаточно легко:

SELECT job, SUM(sal) AS sum_sal
  FROM emp
 GROUP BY job

JOB       SUM_SAL
--------- -------
ANALYST     6,000
CLERK       4,150
MANAGER     8,275
PRESIDENT   5,000
SALESMAN    5,600

Но что, если мы хотим посмотреть эту информацию еще и в разрезе отделов? Получается так, что столбец SUM_SAL нужно:

разбить на несколько столбцов (по количеству отделов)
распределить его данные между этими столбцами

Второе замечательно решается при помощи функции DECODE. Например, чтобы получить сумму зарплаты для отдела с номером(deptno) 10 можно использовать:

   SUM (DECODE (deptno, 10,sal, 0)) AS sum_for_10_dept

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

SELECT job,
       SUM(DECODE(deptno, 10, sal, 0)) AS ACCOUNTING,
       SUM(DECODE(deptno, 20, sal, 0)) AS RESEARCH,
       SUM(DECODE(deptno, 30, sal, 0)) AS SALES,
       SUM(DECODE(deptno, 40, sal, 0)) AS OPERATIONS
  FROM emp
 GROUP BY job

JOB       ACCOUNTING RESEARCH  SALES OPERATIONS
--------- ---------- -------- ------ -----------
ANALYST            0    6,000      0          0
CLERK          1,300    1,900    950          0
MANAGER        2,450    2,975  2,850          0
PRESIDENT      5,000        0      0          0
SALESMAN           0        0  5,600          0

Так вот оказывается, что ситуация когда мы заранее знаем сколько у нас должно быть столбцов - не такая уж и редкая. И приведенного способа вполне достаточно для построения так называемого псевдоматричного отчета. Особенно часто это встречается в финансовых отчетах, где нужно получать данные в разрезе кварталов, месяцев. Мы заранее знаем, что кварталов - 4, месяцев - 12. Даже названия их знаем :-)

Вот и в нашем случае, возвращаясь к календарю, мы заранее знаем, что нам нужно разбить наш пока единственный столбец на 7, по количеству дней недели. Как определить день недели мы тоже уже выяснили в предыдущем разделе. Так что теперь ничего не мешает сделать очередной шаг к поставленной цели:

SELECT
       DECODE (day_of_week, 'MON', dd, NULL) Mon,
       DECODE (day_of_week, 'TUE', dd, NULL) Tue,
       DECODE (day_of_week, 'WED', dd, NULL) Wed,
       DECODE (day_of_week, 'THU', dd, NULL) Thu,
       DECODE (day_of_week, 'FRI', dd, NULL) Fri,
       DECODE (day_of_week, 'SAT', dd, NULL) Sat,
       DECODE (day_of_week, 'SUN', dd, NULL) Sun
  FROM (
       SELECT ROWNUM AS dd,
              TO_CHAR (TRUNC(SYSDATE,'MM') + ROWNUM - 1,
		      'DY', 'NLS_DATE_LANGUAGE=AMERICAN') AS day_of_week
         FROM all_objects
        WHERE ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE), 'DD'))
  );

На результат данного запроса вы посмотрите в следующем разделе, а пока хочу обратить внимание на то, что я сделал основной запрос вложенным (перенес его во фразу FROM - inline view). На то есть несколько причин:

Улучшение читаемости запроса.
Длинная в написании функция TO_CHAR получила алиас (day_of_week), который четко описывает возвращаемое значение. Без алиаса не каждый сразу определит, что это функция возвращает. Это пример так называемой самодокументируемости кода.
К тому же теперь мы можем семь раз продублировать короткое day_of_week в основном запросе, вместо длинной TO_CHAR. Попробуйте перенести TO_CHAR из подзапроса наверх в DECODE и попытайтесь разобраться в получившейся мешанине.
Улучшение производительности.
Функция TO_CHAR выполняется только один раз для каждого возвращаемого дня месяца. Если бы мы пользовались одним запросом, то она исполнялась бы по семь раз на каждый день. Да, возможно, на этом примере изменение производительности вообще не будет заметно, но если бы вместо TO_CHAR была бы пользовательская функция и возвращаемых записей было бы далеко за 30, то эффект был бы сильный.

Здорово я с матричными отчетами "отстрелялся"! Раз и в кусты.
А что же делать, если нужно получить настоящий матричный отчет, когда число столбцов заранее не известно?

Сразу хочу огорчить тех, кто хочет получить это одним запросом. Я не знаю такого способа. Если кто-то знает - сообщите мне. Решение, которое я предлагаю, основано на том, что раз заранее неизвестно требуемое для запроса количество столбцов, то значит первым делом (запросом) его надо определить, а затем динамически сформировать и выполнить нужную команду SELECT.

Конкретная реализация будет зависеть от среды выполнения отчета. Я покажу два способа, как это делается в SQL*Plus, т.к. кроме него и Oracle Reports (который поддерживает построение матричных отчетов) с другими средствами разработки отчетов не знаком.


PROMPT
PROMPT Вариант 1. Универсальный. Должен работать и на 7-ой версии Oracle
PROMPT
SET FEEDBACK OFF VERIFY OFF
VARIABLE sql VARCHAR2(4000)
BEGIN
   :sql := 'job';
   FOR rec IN (SELECT deptno, dname FROM dept)
   LOOP
      :sql := :sql ||',SUM(DECODE(deptno,'''||rec.deptno||''',sal,0)) AS "'||
                     rec.dname||'"';
   END LOOP;
   :sql := :sql || ' FROM emp GROUP BY job';
END;
/
COLUMN sql NEW_VALUE rest_of_select NOPRINT
SELECT :sql AS sql FROM dual;

SELECT &rest_of_select
/
SET FEEDBACK ON VERIFY ON

PROMPT
PROMPT Вариант 2. Будет работать только для 8i и старше,
PROMPT т.к. используется NDS (Native Dynamic SQL)
PROMPT
VARIABLE rc REFCURSOR
DECLARE
   sql_v LONG;
BEGIN
   sql_v := 'SELECT job';
   FOR rec IN (SELECT deptno, dname FROM dept)
   LOOP
      sql_v := sql_v ||',SUM(DECODE(deptno,'''||rec.deptno||''',sal,0)) AS "'||
                       rec.dname||'"';
   END LOOP;
   sql_v := sql_v || ' FROM emp GROUP BY job';
   OPEN :rc FOR sql_v;
END;
/
PRINT rc

Лучше меньше, да лучше или Добавление группировки

Итак, вернемся к календарю. Вот вывод от предыдущего запроса.

 MON  TUE  WED  THU  FRI  SAT  SUN
---- ---- ---- ---- ---- ---- ----
                            1
                                 2
   3
        4
             5
                  6
                       7
                            8
                                 9
...
                           29
                                30

Первое впечатление двоякое. С одной стороны, действительно, получилось семь столбцов и даты по дням недели распределены правильно, а с другой стороны - это не то, что нам нужно. Нам нужно чтобы все даты одной недели располагались на одной строчке, а тут...

А чего я хотел, ведь добавление нескольких столбцов в запрос никак не влияет на количество возвращаемых записей. Как было 30, так и осталось. Нужно придумать какой-нибудь способ, в результате которого вместо 30 записей останется 5 желанных.

Первое что приходит в голову это добавить в запрос группировку (GROUP BY). Но для того чтобы ее добавить нужно определить:

Выражение для группировки(что запишем в GROUP BY)
Как будем преобразовывать несколько записей в одну (какую агрегатную функцию использовать)

Очевидно, что группировать нужно понедельно. Но как сформировать выражение, возвращающее эту неделю? Здесь нам опять поможет TO_CHAR. У этой функции есть еще одна форматная маска IW, которая возвращает порядковый номер недели в году. Это именно то, что нам нужно!

Какую агрегатную функцию применить?
Если посмотреть на те записи, которые будут подлежать группировке, то легко увидеть, что в каждой группе записей (неделе), для каждого столбца(дня недели) будет заполнена только одна запись (с числом), а в остальных будет пусто (NULL). Нам нужно, чтобы групповая функция вот это единственное значение и вернула. Для этого можно использовать разные функции, я выбрал MAX.

SELECT
       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 ROWNUM AS dd,
              TO_CHAR (TRUNC(SYSDATE,'MM') + ROWNUM - 1,
                       'DY', 'NLS_DATE_LANGUAGE=AMERICAN') AS day_of_week,
              TO_CHAR (TRUNC(SYSDATE,'MM')+ROWNUM-1, 'IW') AS week_num
         FROM all_objects
        WHERE ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE), 'DD'))
  )
GROUP BY
      week_num

 MON  TUE  WED  THU  FRI  SAT  SUN
---- ---- ---- ---- ---- ---- ----
                            1    2
   3    4    5    6    7    8    9
  10   11   12   13   14   15   16
  17   18   19   20   21   22   23
  24   25   26   27   28   29   30

Отлично! Это самый настоящий календарь на Июнь 2002 года.
И получен он довольно не сложным запросом! Да вы не стесняйтесь, прямо берите его cut&paste в ваш любимый sqlplus/toad/plsqldev/etc и выполняйте.

Передача параметров в представление или Печать календаря за любой месяц

Принципы структурного программирования обязывают избегать повторного использования кода. Поэтому нужно срочно создать представление(VIEW) с текстом этого запроса, пока разработчики не успели "понавтыкать" его в свои программы. Да, но как же тогда получать календари для других месяцев? Пока используется отдельный запрос, можно просто заменить SYSDATE на другую дату. А как это сделать с представлением?

Тут мы возвращаемся к теме затронутой еще в самом начале по поводу разделения задачи на части. Я уже тогда предвидел, что итоговый запрос нужно будет поместить в представление. Вот сейчас пришло время определиться с тем, как это представление параметризировать.

Поскольку текст представления (в отличие от текста команды SELECT) мы не можем(точнее не должны) изменять от запроса к запросу, то получается, что нужно это делать как то косвенно.
Стандартным приемом является использование в тексте представления пользовательской функции. Функция эта, возвращает значение, которое мы можем задавать при помощи вызова другой процедуры. Работа с таким параметризованным представлением выглядит так. Задали нужное значение процедурой, затем выполнили запрос к представлению. Задали другое значение, выполнили запрос, и то же самое представление возвращает уже другие данные.

Главное, чтобы заданное процедурой значение сохранялось на протяжении пользовательской сессии. Для реализации этого идеально подходит PL/SQL пакет. В теле пакета создаем пакетную переменную, которая сохраняет свое значение на протяжении всей сессии пользователя. В спецификации определяем функцию, которая возвращает значение пакетной переменной и процедуру, которая это значение ей задает.

CREATE OR REPLACE PACKAGE p_calendar
IS
   FUNCTION get_date RETURN DATE;
   PROCEDURE set_date (date_in IN DATE);
END p_calendar;
/
CREATE OR REPLACE PACKAGE BODY p_calendar
IS
   date_pv DATE := SYSDATE;

   FUNCTION get_date RETURN DATE IS BEGIN RETURN (date_pv); END get_date;

   PROCEDURE set_date (date_in IN DATE)
   IS 
   BEGIN 
      date_pv := NVL (date_in, SYSDATE);
   END set_date;

END p_calendar;
/

CREATE OR REPLACE VIEW v_calendar
AS
SELECT
       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 ROWNUM AS dd,
              TO_CHAR (TRUNC(p_calendar.get_date,'MM') + ROWNUM - 1,
                       'DY', 'NLS_DATE_LANGUAGE=AMERICAN') AS day_of_week,
	      TO_CHAR(TRUNC(p_calendar.get_date,'MM')+ROWNUM-1, 'IW') AS week_num
         FROM all_objects
	 WHERE ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(p_calendar.get_date), 'DD'))
  )
 GROUP BY
       week_num;

Теперь можно выводить календари за любой заданный месяц, причем если мы еще ничего не задали, то по умолчанию будет использован текущий месяц, что кажется совершенно логичным:

scott@orcl> select * from v_calendar;

 MON  TUE  WED  THU  FRI  SAT  SUN
---- ---- ---- ---- ---- ---- ----
                            1    2
   3    4    5    6    7    8    9
  10   11   12   13   14   15   16
  17   18   19   20   21   22   23
  24   25   26   27   28   29   30

scott@orcl> exec p_calendar.set_date(add_months(sysdate, 1));

PL/SQL procedure successfully completed.

scott@orcl> select * from v_calendar;

 MON  TUE  WED  THU  FRI  SAT  SUN
---- ---- ---- ---- ---- ---- ----
   1    2    3    4    5    6    7
   8    9   10   11   12   13   14
  15   16   17   18   19   20   21
  22   23   24   25   26   27   28
  29   30   31

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

О пользе тестирования

Любите ли вы тестировать свои программы?
Улыбаетесь? Или скривились? Я тоже не люблю, и никогда не видел тех кто любит. Некоторые разработчики в качестве отговорки говорят, что сам разработчик не сможет правильно оттестировать свою программу, что это должны делать другие люди.

Здесь все нужно расставить по своим местам. Тест - тесту рознь. Есть так называемый тест отдельного модуля (unit test), который обязан выполнять сам разработчик. Это тест отдельных процедур, отчетов, экранных форм. А есть системный тест, в котором нужно протестировать взаимодействие в системе отдельных модулей. Вот системный тест действительно должен делаться не разработчиками, а посторонними людьми (идеально группой эксплуатации).

Одним словом, хотел я этого или нет(второе правильно), а на глаза мне попался календарик за 2002 год и я понял, что сейчас придется методично месяц за месяцем проверять правильность работы представления V_CALENDAR. Где-то на июле-августе хотел бросить, ну что ерундой заниматься и так все правильно. Усилием воли заставил себя дойти до декабря (только потому, что знаю, большинство ошибок всплывает на граничных условиях) и что вы думаете?

Тестирование декабря 2002 года:

scott@orcl> exec p_calendar.set_date(TO_DATE('01.12.2002','DD.MM.YYYY'));

PL/SQL procedure successfully completed.

scott@orcl> select * from v_calendar;

 MON  TUE  WED  THU  FRI  SAT  SUN
---- ---- ---- ---- ---- ---- ----
  30   31
                                 1
   2    3    4    5    6    7    8
   9   10   11   12   13   14   15
  16   17   18   19   20   21   22
  23   24   25   26   27   28   29

Вот это номер! Я уже собрался кофе пить, а тут последняя неделя декабря наверх вылезла. Что-то с сортировкой не то получилось. Фраза ORDER BY не используется, сортировка обеспечивается неявно выражением GROUP BY. Группируем по номеру недели, значит у этих двух последних дней декабря номер недели меньше чем у предыдущих?

Все дело в том, что количество дней в году не кратно семи (неделе), ведь 52 недели на 7 дней это только 364, а не 365(366). TO_CHAR с форматной маской IW возвращает '52'('53') для 31 декабря в том случае, если на этой неделе больше дней старого года и возвращает '01' если больше дней нового года. Вот и получается, что на той неделе, где расположено 31.12.2002 январских дней больше, поэтому для '30' и '31' декабря возвращается '01'.

Логично предположить, что должны быть года, где 1-е января приходится на последнюю неделю предыдущего года, там тоже V_CALENDAR даст не правильную сортировку:

scott@orcl> exec p_calendar.set_date(TO_DATE('01.01.2005','DD.MM.YYYY'));

PL/SQL procedure successfully completed.

scott@orcl> /

 MON  TUE  WED  THU  FRI  SAT  SUN
---- ---- ---- ---- ---- ---- ----
   3    4    5    6    7    8    9
  10   11   12   13   14   15   16
  17   18   19   20   21   22   23
  24   25   26   27   28   29   30
  31
                            1    2

Получается, что для правильной сортировки нельзя использовать TO_CHAR с IW в чистом виде. Нужно специально обрабатывать те дни декабря, где номер недели '01' и те дни января где номер недели '52' или '53'. Что значит специально обрабатывать? Просто для таких декабрьских дней нужно возвращать вместо '01' заведомо наибольшее число, например '54', а для январских дней заведомо наименьшее - '00'

Появившийся в версии 8.1.6 оператор CASE очень хорошо подходит для описания такого рода условий:

CREATE OR REPLACE VIEW v_calendar
AS
SELECT
       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 ROWNUM AS dd,
              TO_CHAR (TRUNC(p_calendar.get_date,'MM') + ROWNUM - 1,
                       'DY', 'NLS_DATE_LANGUAGE=AMERICAN') AS day_of_week,
	      CASE WHEN TO_CHAR(p_calendar.get_date, 'MM') = '12'
	            AND TO_CHAR(TRUNC(p_calendar.get_date,'MM') + ROWNUM - 1,
	                       'IW') = '01'
		   THEN
		      '54'
	           WHEN TO_CHAR(p_calendar.get_date, 'MM') = '01'
	            AND TO_CHAR(TRUNC(p_calendar.get_date,'MM') + ROWNUM - 1,
		                'IW') IN ('52', '53')
		   THEN
		      '00'
		   ELSE
	              TO_CHAR(TRUNC(p_calendar.get_date,'MM')+ROWNUM-1, 'IW')
	      END AS week_num 
         FROM all_objects
        WHERE ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(p_calendar.get_date), 'DD'))
  )
 GROUP BY
       week_num;


scott@orcl> select p_calendar.get_date from dual;

GET_DATE
---------
01-JAN-05

scott@orcl> select * from v_calendar;

 MON  TUE  WED  THU  FRI  SAT  SUN
---- ---- ---- ---- ---- ---- ----
                            1    2
   3    4    5    6    7    8    9
  10   11   12   13   14   15   16
  17   18   19   20   21   22   23
  24   25   26   27   28   29   30
  31

scott@orcl> exec p_calendar.set_date(TO_DATE('01.12.2002', 'DD.MM.YYYY'));

PL/SQL procedure successfully completed.

scott@orcl> select * from v_calendar;

 MON  TUE  WED  THU  FRI  SAT  SUN
---- ---- ---- ---- ---- ---- ----
                                 1
   2    3    4    5    6    7    8
   9   10   11   12   13   14   15
  16   17   18   19   20   21   22
  23   24   25   26   27   28   29
  30   31

Ну, вот теперь, кажется, все. Заработало!

CASE против DECODE

Нет не все. Все - это для тех, у кого Oracle версии 8.1.6 и старше, а если нет?
Конечно, можно написать пользовательскую функцию, но не стоит однако выбрасывать со счетов и DECODE. Эта старая добрая функция может послужить и в этом случае.

Специально для поклонников DECODE и тех, у кого версия Oracle меньше чем 8.1.6 я покажу как должен выглядеть столбец WEEK_NUM с использованием DECODE:

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

Выглядит громоздко, думаю, что CASE в этом месте смотрится предпочтительнее. Однако это не значит, что от DECODE нужно повсеместно отказываться в пользу CASE. В этом представлении есть еще одна функция DECODE:

   DECODE (day_of_week, 'SUN', dd, NULL)

которая, на мой взгляд, смотрится очень лаконично и понятно.
Переписав эту функцию DECODE на CASE, мне кажется, что мы только потеряем.

Более того, в окончательное решение включен именно вариант с DECODE (для большего охвата версий Oracle). Так что если вы предпочитаете CASE, то используйте текст представления V_CALENDAR из предыдущего раздела.

Решение

Прежде чем использовать нижеприведенное решение, ещё раз настоятельно рекомендую прочитать отзывы на статью, т.к. они содержат очень важные и полезные замечания, а также два альтернативных решения.

CREATE OR REPLACE PACKAGE p_calendar
IS
   FUNCTION get_date RETURN DATE;
   PROCEDURE set_date (date_in IN DATE);
END p_calendar;
/
CREATE OR REPLACE PACKAGE BODY p_calendar
IS
   date_pv DATE := SYSDATE;

   FUNCTION get_date RETURN DATE IS BEGIN RETURN (date_pv); END get_date;

   PROCEDURE set_date (date_in IN DATE)
   IS 
   BEGIN 
      date_pv := NVL (date_in, SYSDATE);
   END set_date;

END p_calendar;
/

CREATE OR REPLACE VIEW v_calendar
AS
SELECT
       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 ROWNUM AS dd,
              TO_CHAR (TRUNC(p_calendar.get_date,'MM') + ROWNUM - 1,
                       'DY', 'NLS_DATE_LANGUAGE=AMERICAN') AS day_of_week,
              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
         FROM all_objects
        WHERE ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(p_calendar.get_date), 'DD'))
  )
 GROUP BY
       week_num;

23 июля 2002г.

Hosted by www.Geocities.ws