Подяки
Зараз дуже модно робити різні веб-додатки, ось і мені довелося розробляти щось подібне, пов'язане з календарем. Відкриває користувач сторінку. де збоку такий маленький місячний календарик і кожен день у вигляді ссилочку. Натискаєш на потрібний день місяця, а там тобі новини за цей день або список заходів, різноманітних, виводиться.
За базу даних для реалізації програми була обрана, зрозуміло, Oracle, а в якості засобів розробки - втім, це не важливо, тому що мені відразу здалося, що повинно існувати просте SQL рішення, відповідно, ніяк не залежить від засобів розробки.
Звичайно, завдання цю можна вирішити різними способами, але все-таки, мені здається, що чисте SQL-рішення повинно бути найбільш переважно, адже воно може бути використано в будь-якому середовищі розробки, де підтримується команда SELECT (покажіть мені де вона не підтримується).
По ходу вирішення мені довелося зіткнутися з різними цікавими ситуаціями, які власне кажучи і підштовхнули до написання цієї статті і, як я сподіваюся, будуть цікаві не тільки мені, але і всім читає, в першу чергу розробникам ПО.
Філософське питання. Дуже важливо відразу вибрати правильний шлях, в іншому випадку все доведеться починати спочатку.
За великим рахунком для друку місячного календаря потрібна таблиця, в якій гарантовано є 31 запис. І зовсім неважливо, що в цій таблиці зберігається. Все що нам потрібно можна отримати за допомогою псевдостолбца ROWNUM і функцій для роботи з датами.
Функція LAST_DAY повертає останній день місяця для заданої дати, TO_CHAR з форматної маскою 'DD' в парі з TO_NUMBER дасть цей останній день у вигляді числа і що більш важливо це і є те кількість записів, яке потрібно вибрати і яке ніколи не перевищить 31 (Мюнхгаузен не зараховується!).
Ну а вибирати будемо з улюбленого Томасом кайт уявлення ALL_OBJECTS:
Початок покладено!
Ми маємо запит, який повертає всі дні поточного місяця.
Тільки не кажіть мені, що неправильно покладатися на те, що в поданні ALL_OBJECTS завжди буде 31 запис. Так, я покладаюся на це і вважаю це припущення цілком виправданим!
Хочу звернути увагу на те, що запит виводить дні тільки поточного місяця (в ньому жорстко зашита функція SYSDATE), хоча правильніше було б якось передбачити можливість виведення днів для різних місяців. Цим самим я хочу розділити задачу на частини. Спочатку домогтися результату з поточним місяцем, а вже потім займатися параметризацією. І це правильний шлях! Не потрібно намагатися осягнути неосяжне, потрібно розділяти і вирішувати окремо (так і хочеться сказати "панувати", але це вже чийсь "копірайт" :-)).
Ну що ж, тепер саме час перейти до днях тижня. Хочу зупинитися на цій темі докладніше, тому що змушений визнати, що довгий час був в невіданні як програмно визначити день тижня для заданої дати :-(. Довгий час мені це і не потрібно було, а ось пару років назад зіткнувся і правильне рішення знайшов далеко не відразу. Дуже не хотілося б, щоб хтось ще витрачав на це час.
З одного боку здається, що все просто, беремо функцію і отримуємо, відповідно, короткий (трибуквенне) або повна назва дня тижня. Та ось невдача, виявляється, день тижня буде повернутий на тій мові, який встановлений параметрами NLS користувальницької сесії. Тобто якщо у користувача заданий англійська, то для понеділка буде видано MON (MONDAY), якщо російська, то ПНД (понеділок).
Ви впевнені, що всі користувачі будуть користуватися вашим додатком з однаковими настройками NLS? Особисто я майже впевнений в зворотному і такий метод визначення мене не влаштовує.
Можна спробувати скористатися TO_CHAR інший форматної маскою. яка замість назви повертає номер дня тижня. Ну, вже номер-то від мови не залежить! Від мови номер дійсно не залежить, а ось від параметра NLS визначає територію залежить :-( Якщо в Росії четвер - перший день тижня, то в США це вже другий.
Що робити в цій ситуації? Перевіряти на MON і ПНД одночасно? Несерйозно.
Перевіряти установки користувача при старті сесії і довантажувати для кожної сесії свої мовні константи? Громіздко.
Написати свою функцію, що повертає день тижня незалежно від мовних установок, благо алгоритм нескладний? Ймовірно, так би і поступив, але ж має ж бути нормальне рішення!
Напевно, в житті кожної розробника бувають такі моменти, коли після декількох років постійного використання чого-небудь ти раптом дізнаєшся щось нове, якого тобі завжди не вистачало і що найприкріше знаходилося практично на поверхні. Я бачив (читав) реакцію Стівена Ферстейна на команду @@ в sqlplus і це після того як він написав початкову версію PL / Vision. У моєму випадку бурю емоцій викликали можливості функції TO_CHAR.
Ключ до вирішення виявився настільки простий, наскільки спочатку дана проблема здавалася нерозв'язною. Виявляється у функції TO_CHAR є третій параметр, який дозволяє задати мову, в якому дата повинна повертатися! Завжди поверне MON для понеділка незалежно від NLS установок користувачів.
До речі, а знаєте, що повинно повернути логічний вираз: за умови, що my_date - понедельник?
Правильно, FALSE!
Чому? Тому що функція TO_CHAR в даному випадку поверне c хвостовими пробілами до найдовшого (з точки зору написання) дня тижня WEDNESDAY. Відповідно, якщо ставити NLS_DATE_LANGUAGE в RUSSIAN, то все повні дні тижня будуть вирівнюватися до 11 символів (до понеділка).
Для коректного використання повного дня тижня потрібно до форматної масці DAY додати модифікатор fm:
На завершення розмови про дні тижня хочеться сказати, що існує чимало програм, які хоч і розроблені в Росії (або адаптовані для Росії), але використовують англійську як мову для відображення дати. Ймовірно, що неправильне визначення днів тижня це одна з причин.
Часто в різних телеконференціях можна почути запитання про те, як побудувати матричний звіт. Причому бажано однією командою SELECT.
Розглянемо цю проблему на улюбленій (принаймні мною) таблиці EMP. Отримати загальну суму зарплат (sal) в розрізі посад (job) досить легко:
Але що, якщо ми хочемо подивитися цю інформацію ще й в розрізі відділів? Виходить так, що стовпець SUM_SAL потрібно:- розбити на кілька стовпців (за кількістю відділів)
- розподілити його дані між цими стовпцями
- Поліпшення читання запиту.
Довга в написанні функція TO_CHAR отримала алиас (day_of_week), який чітко описує значення, що повертається. Без аліаса не кожен відразу визначить, що це функція повертає. Це приклад так званої самодокументіруемості коду.
До того ж тепер ми можемо сім раз продублювати короткий day_of_week в основному запиті, замість довгої TO_CHAR. Спробуйте перенести TO_CHAR з підзапиту наверх в DECODE і спробуйте розібратися в вийшла мішанині. - Поліпшення продуктивності.
Функція TO_CHAR виконується тільки один раз для кожного повертається дня місяця. Якби ми користувалися одним запитом, то вона виконувалася б по сім разів на кожен день. Так, можливо, на цьому прикладі зміна продуктивності взагалі не буде помітно, але якби замість TO_CHAR була б призначена для користувача функція і повертаються записів було б далеко за 30, то ефект був би сильний.
Здорово я з матричними звітами "відстрілявся"! Раз і в кущі.
А що ж робити, якщо потрібно отримати справжній матричний звіт, коли число стовпців заздалегідь не відомо?
Відразу хочу засмутити тих, хто хоче отримати це одним запитом. Я не знаю такого способу. Якщо хтось знає - повідомте мені. Рішення, яке я пропоную, засноване на тому, що раз заздалегідь невідомо необхідну для запиту кількість стовпців, то значить насамперед (запитом) його треба визначити, а потім динамічно сформувати і виконати потрібну команду SELECT.
Конкретна реалізація буде залежати від середовища виконання звіту. Я покажу два способи, як це робиться в SQL * Plus, тому що крім нього і Oracle Reports (який підтримує побудову матричних звітів) з іншими засобами розробки звітів не знайомий.
Отже, повернемося до календаря. Ось висновок від попереднього запиту.
Перше враження двояке. З одного боку, дійсно, вийшло сім стовпців і дати по днях тижня розподілені правильно, а з іншого боку - це не те, що нам потрібно. Нам потрібно щоб всі дати одного тижня розташовувалися на одній сходинці, а тут.
А чого я хотів, адже додавання кількох стовпців в запит ніяк не впливає на кількість повертаються записів. Як було 30, так і залишилося. Потрібно придумати якийсь спосіб, в результаті якого замість 30 записів залишиться 5 жаданих.
Перше що приходить в голову це додати в запит угруповання (GROUP BY). Але для того щоб її додати потрібно визначити:- Вираз для угруповання (що запишемо в GROUP BY)
- Як будемо перетворювати кілька записів в одну (яку агрегатную функцію використовувати)
Очевидно, що групувати потрібно потижнево. Але як сформувати вираз, що повертає цей тиждень? Тут нам знову допоможе TO_CHAR. У цій функції є ще одна форматна маска IW, яка повертає порядковий номер тижня в році. Це саме те, що нам потрібно!
Яку агрегатную функцію застосувати?
Якщо подивитися на ті записи, які будуть підлягати угрупованню, то легко побачити, що в кожній групі записів (тижня), для кожного стовпця (дня тижня) буде заповнена тільки один запис (з числом), а в інших буде порожньо (NULL). Нам потрібно, щоб групова функція ось це єдине значення і повернула. Для цього можна використовувати різні функції, я вибрав MAX.
Принципи структурного програмування зобов'язують уникати повторного використання коду. Тому потрібно терміново створити уявлення (VIEW) з текстом цього запиту, поки розробники не встигли "понавтикать" його в свої програми. Так, але як же тоді отримувати календарі для інших місяців? Поки використовується окремий запит, можна просто замінити SYSDATE на іншу дату. А як це зробити з поданням?
Тут ми повертаємося до теми порушеної ще на самому початку з приводу поділу завдання на частини. Я вже тоді передбачав, що підсумковий запит потрібно буде помістити в уявлення. Ось зараз прийшов час визначитися з тим, як це уявлення параметризованих.
Оскільки текст подання (на відміну від тексту команди SELECT) ми не можемо (точніше не повинні) змінювати від запиту до запиту, то виходить, що потрібно це робити як то побічно.
Стандартним прийомом є використання в тексті подання користувальницької функції. Функція ця, повертає значення, яке ми можемо задавати за допомогою виклику іншої процедури. Робота з таким параметризованим поданням виглядає так. Поставили потрібне значення процедурою, потім виконали запит до подання. Поставили інше значення, виконали запит, і те ж саме уявлення повертає вже інші дані.
Головне, щоб заданий процедурою значення зберігалося протягом користувальницької сесії. Для реалізації цього ідеально підходить PL / SQL пакет. У тілі пакету створюємо пакетну змінну, яка зберігає своє значення протягом всієї сесії користувача. У специфікації визначаємо функцію, яка повертає значення пакетної змінної і процедуру, яка це значення їй задає.
Тепер можна виводити календарі за будь-який заданий місяць, причому якщо ми ще нічого не задали, то за замовчуванням буде використаний поточний місяць, що здається абсолютно логічним: Як Постскриптум до цього розділу хочу сказати, що якщо ви працюєте з Oracle Portal, то не зможете використовувати пакетні змінні для зберігання значень протягом сесії. Oracle Portal використовує свої сесії, ні як не пов'язані з тим, що ми звикли розуміти під користувальницької сесією. В якості альтернативи потрібно використовувати API пропоноване об'єктним типом WWSTO_API_SESSION, яке знаходиться в схемі власника Portal-а. Ви можете дізнатися подробиці про роботу з цим типом в його специфікації.
Чи любите ви тестувати свої програми?
Посміхаєтеся? Або скривилися? Я теж не люблю, і ніколи не бачив тих хто любить. Деякі розробники як відмовки кажуть, що сам розробник не зможе правильно оттестировать свою програму, що це повинні робити інші люди.
Тут все потрібно розставити по своїх місцях. Тест - тесту ворожнечу. Є так званий тест окремого модуля (unit test), який зобов'язаний виконувати сам розробник. Це тест окремих процедур, звітів, екранних форм. А є системний тест, в якому потрібно протестувати взаємодія в системі окремих модулів. Ось системний тест дійсно належить робити не розробниками, а сторонніми людьми (ідеально групою експлуатації).
З'явився у версії 8.1.6 оператор CASE дуже добре підходить для опису такого роду умов:
Ну, ось тепер, здається, все. Запрацювало!
Ні не все. Все - це для тих, у кого Oracle версії 8.1.6 і старше, а якщо ні?
Звичайно, можна написати для користувача функцію, але не варто однак викидати з рахунків і DECODE. Ця стара добра функція може послужити і в цьому випадку.
Спеціально для шанувальників DECODE і тих, у кого версія Oracle менше ніж 8.1.6 я покажу як повинен виглядати стовпець WEEK_NUM з використанням DECODE: Виглядає громіздко, думаю, що CASE в цьому місці виглядає переважніше. Однак це не означає, що від DECODE потрібно повсюдно відмовлятися на користь CASE. У цьому поданні є ще одна функція DECODE: яка, на мій погляд, виглядає дуже лаконічно і зрозуміло.
Переписавши цю функцію DECODE на CASE, мені здається, що ми тільки втратимо.
Більш того, в остаточне рішення включено саме варіант з DECODE (для більшого охоплення версій Oracle). Так що якщо ви віддаєте перевагу CASE, то використовуйте текст подання V_CALENDAR з попереднього розділу.
Перш ніж використовувати нижчевикладене рішення, ще раз настійно рекомендую прочитати відгуки на статтю, тому що вони містять дуже важливі і корисні зауваження, а також два альтернативних рішення.