Ноу Інти, лекція, вираження в oracle sql

Конструкції (оператори) CASE для побудови виразів

В якості альтернативи функції DECODE (відсутнє у стандарті рішення Oracle, оформлене у вигляді функції) та інших функцій умовної підстановки значень NVL, NVL2, NANVL і COALESCE починаючи з версії 8.1.6 можна користуватися "пошуковим" CASE -виражена, а з версії 9 - "простим" CASE -виражена (обидва входять в стандарт SQL-92). Формально конструкцію CASE можна вважати оператором (з більш складною синтаксичною структурою, ніж в разі, між іншим, арифметичних операторів), призначеним для побудови виразів з простіших. Для вживання істотно, що результат CASE не «є остаточним"; він являє собою вираз, яке не забороняється використовувати для побудови чергового більш складного. У цьому конструкція CASE не відрізняється від інших операторів.

Синтаксис "пошукового" оператора CASE:

Перевірки відбуваються зверху вниз, поки перше по порядку умовне-вираженіеI не стане TRUE. Тоді перевірки припиняться, і результатом CASE буде значення виразу-результатаI.

Синтаксис "простого" оператора CASE:

Перевірки відбуваються зверху вниз, поки значення першого по порядку вираженіяI не стане рівним значенню вираженія0. Тоді перевірки припиняться, і результатом CASE буде значення виразу-результатаI.

Синтаксис умовного-вирази в CASE відповідає синтаксису подібного в частині WHERE пропозицій SELECT, UPDATE і DELETE. описуваних далі, і допускає досить складні конструкції, як показує приклад нижче:

Зауважте, що за нашими даними в результаті службовець KING буде позначений як "високооплачуваний". Якщо в операторі CASE перевірку зарплати і місцезнаходження відділу поміняти місцями, KING виявиться позначений як "працює в Нью-Йорку".

Вправа. Перевірте останнє твердження.

Тим самим конструкція CASE вносить елемент процедурності в описову в цілому побудова запиту, прийняте в SQL.

Відсутність конструкції ELSE може призводити до відсутності значення в результаті (до NULL), але ж не до помилки:

Існує думка, що обов'язкове зазначення ELSE покращує розуміння тексту програмістом (а значить, знижує ризик людських помилок). Відповідно до цієї точки зору такий вираз не є побудованим вдало:

Замість цього краще написати:

"Пошукова" різновид CASE носить більш загальний характер, ніж "проста", так як допускає умовні вирази, які отримані операторами порівняння, відмінними від = (рівності).

Через те, що конструкція CASE оформлена у вигляді оператора мови, а не функції, як DECODE, NVL, NVL2, NANVL і COALESCE. вона стає не тільки їх більш загальним замінником, але до того ж і швидше їх обчислюваною, хоча б і не набагато в кожному окремому випадку. Це створює стимул до застосування в програмуванні саме її, а не перерахованих функцій умовної підстановки значень. У той же час, в тексті запиту вона зазвичай займає більше місця.

скалярний запит

Ще одна конструкція для формування виразів існує з версії Oracle 9. Якщо запит одностолбцовий і повертає не більше одного рядка, його можна вказати в круглих дужках в складі вираження на правах значення.

При цьому множинний результат сприймається як помилка, а порожній результат - як відсутність значення, NULL:

Додавання нуля в вираженні вище зроблено, щоб переконати Новомосковсктеля у відсутності значення у наведеного скалярного вираження. Інакше підійшло б використання функції NVL.

Вправа. Перепишіть останній запит з використанням функції NVL для з'ясування реакції СУБД на відсутність рядків в скалярному запиті.

Одностолбцовость скалярного запиту Oracle в змозі контролювати синтаксично, а ось однорядковими - немає. Для підвищення надійності тексту деякі пропонують в якості штучної заходи включати в умовний вираз у фразі WHERE запиту додаткову умову ROWNUM <= 1. например:

Скалярний підзапит СКАЛЯРИЯ в тому ж сенсі, що і згадувані скалярні функції, тобто результат його не може бути масивом (наприклад, стовпцем значень). У той же час єдине, що повертається їм значення цілком може бути об'єктом (в сенсі об'єктних можливостей Oracle) і мати зрозумілу СУБД структуру.

умовні вирази

Окремі зауваження з приводу відсутності значення в виразах

Вираз з операндом, значення якого відсутній (позначено як NULL), призведе до відсутнього же значенням (NULL) в разі:

числових і тимчасових виразів, побудованих арифметичними операціями;
порівняння виразів всіх видів.

Зрозуміти обробку NULL іноді допомагає наступне правило: SQL сприймає в виразах NULL як невідоме значення.

При роботі з відсутніми значеннями в БД часто використовують функцію NVL. Порівняйте відповіді:

На жаль, формального обгрунтування застосування функції NVL в подібних випадках не існує. Варто її вжити чи ні, вирішується змістом, який проектувальник БД закладає в допущення пропуску значення в стовпці. У нашому випадку, якщо сенс - "комісійні невідомі" (unknown, "значення відсутній, тому що невідомо базі даних, не надійшла до БД"), то слід застосувати запит (1). Якщо ж сенс "комісійних немає" ( "співробітник не отримав комісійних"), то запит (2). Сенс пропущеного значення в таблиці SQL ніяк не відзначений в БД; він існує поза БД. проте ж повинен враховуватися в програмі, що працює з БД. Це одна з давно відомих неприємностей SQL.

Частково вирішити саме цю проблему можна було б використанням замість одного "безликого" ознаки відсутності значення NULL хоча б двох з різним змістом (пропонувалося "не застосовується" - missing but inapplicable - і "невідомо" - missing but applicable). Однак в цьому випадку виникли б інші проблеми, пов'язані зі складністю вживання чотиризначною логіки, і з цієї причини в SQL від цього відмовилися. Розробники SQL радять використовувати пропущені значення в стовпцях тільки в сенсі unknown = missing but applicable. У Oracle ця рада має відносну цінність, так як деякі запити (приклади зустрінуться далі) здатні породжувати пропущені значення саме в сенсі missing but inapplicable.

Повним же рішенням могла стати відмова від відсутніх значень взагалі. Оскільки в SQL цього не зроблено, деякі радять добровільно уникати вживання відсутніх значень у міру можливості і моделювати відсутність значень (в силу різних причин) без використання NULL. Зворотним боком такого самообмеження виявиться захаращення схеми даних і ускладнення запитів до БД.