Выражения в Oracle SQL

Конструкции (операторы) CASE для построения выражений

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

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

CASE
  WHEN условное-выражение1 THEN выражение-результат1
  WHEN условное-выражение2 THEN выражение-результат2
  …
  WHEN условное-выражениеN THEN выражение-результатN
  [ ELSE выражение-результат ]
END

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

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

CASE выражение0
  WHEN выражение1 THEN выражение-результат1
  WHEN выражение2 THEN выражение-результат2
  …
  WHEN выражениеN THEN выражение-результатN
  [ ELSE выражение-результат ]
END

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

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

SELECT 
  ename
, sal
, deptno
, CASE 
  WHEN sal > 4000
    THEN 'Highly paid'
  WHEN deptno IN ( SELECT deptno FROM dept WHERE loc = 'NEW YORK' )
    THEN 'Works in New York'
  ELSE 'Nothing interesting'
  END || ' !'
  attention
FROM emp
;

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

Упражнение. Проверьте последнее утверждение.

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

Отсутствие конструкции ELSE может приводить к отсутствию значения в результате (к NULL), однако же не к ошибке:

SQL> SELECT NVL ( CASE 1 WHEN 2 THEN 3 END, -1 ) FROM dual;
NVL(CASE1WHEN2THEN2END,-1)
--------------------------
                        -1

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

CASE loc 
WHEN 'NEW YORK' THEN 'NEW YORK CITY' 
WHEN 'BOSTON'   THEN 'BOSTON AREA'
END

Вместо этого лучше написать:

CASE loc 
WHEN 'NEW YORK' THEN 'NEW YORK CITY' 
WHEN 'BOSTON'   THEN 'BOSTON AREA'
ELSE NULL
END

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

Из-за того, что конструкция CASE оформлена в виде оператора языка, а не функции, как DECODE, NVL, NVL2, NANVL и COALESCE, она становится не только их более общим заменителем, но к тому же и быстрее их вычислимой, хотя бы и ненамного в каждом отдельном случае. Это создает стимул к применению в программировании именно ее, а не перечисленных функций условной подстановки значений. В то же время, в тексте запроса она обычно занимает больше места.

Скалярный запрос

Еще одна конструкция для формирования выражений существует с версии Oracle 9. Если запрос одностолбцовый и возвращает не более одной строки, его можно указать в круглых скобках в составе выражения на правах значения.

Пример:

SELECT
  ename
, '-> ' || ( SELECT dname FROM dept WHERE dept.deptno = emp.deptno )
FROM emp
WHERE 
   TRUNC ( hiredate, 'year' ) >
   TRUNC
   ( ( SELECT hiredate FROM emp WHERE job = 'PRESIDENT' ), 'year' )
;

При этом множественный результат воспринимается как ошибка, а пустой результат — как отсутствие значения, NULL:

SELECT
   ename
 , ( SELECT deptno FROM dept WHERE 1 = 2 ) + 0
FROM emp
;

Добавление нуля в выражении выше сделано, чтобы убедить читателя в отсутствии значения у приведенного скалярного выражения. Иначе подошло бы использование функции NVL.

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

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

( SELECT hiredate FROM emp WHERE job = 'PRESIDENT' AND ROWNUM <= 1 )

Не исключено, что такая мера более важна как способ привлечения внимания программиста к содержательно правильному построению запроса, и такое дополнительное условие служит своего рода "активным комментарием" к тексту программы. Обратите внимание, что добавление AND ROWNUM <= 1 несколько изменяет смысл запроса.

Скалярный подзапрос скалярен в том же смысле, что и упоминавшиеся скалярные функции, то есть результат его не может быть массивом (например, столбцом значений). В то же время единственное возвращаемое им значение вполне может быть объектом (в смысле объектных возможностей Oracle) и иметь понятную СУБД структуру.

Условные выражения

Условные выражения в Oracle существуют, но в отличие от числовых, строковых и временных не могут использоваться для придания значений полям строк таблиц БД, так как в Oracle отсутствует тип BOOLEAN (хотя он есть в стандарте SQL:1999). Не будучи в той же степени равными, они активно используются для проверки условия в операторе CASE (см. выше), а также в части START WITH фразы CONNECT BY и во фразах WHERE и HAVING предложений SELECT, UPDATE, DELETE (см. ниже).

Отдельные замечания по поводу отсутствия значения в выражениях

Выражение с операндом, значение которого отсутствует (обозначено как NULL), приведет к отсутствующему же значению (NULL) в случае:

• числовых и временных выражений, построенных арифметическими операциями;
• сравнения выражений всех видов.

Понять обработку NULL иногда помогает следующее правило: SQL воспринимает в выражениях NULL как неизвестное значение.

При работе с отсутствующими значениями в БД часто используют функцию NVL. Сравните ответы:

( 1 ) SELECT ename, sal, comm, sal + comm FROM emp;

и:

( 2 ) SELECT ename, sal, comm, sal + NVL ( comm, 0 ) FROM emp;

В случае (1) получим:

ENAME             SAL       COMM   SAL+COMM
---------- ---------- ---------- ----------
SMITH             800
ALLEN            1600        300       1900
WARD             1250        500       1750
JONES            2975
MARTIN           1250       1400       2650
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500          0       1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300

В случае (2) получим:

ENAME             SAL       COMM SAL+NVL(COMM,0)
---------- ---------- ---------- ---------------
SMITH             800                        800
ALLEN            1600        300            1900
WARD             1250        500            1750
JONES            2975                       2975
MARTIN           1250       1400            2650
BLAKE            2850                       2850
CLARK            2450                       2450
SCOTT            3000                       3000
KING             5000                       5000
TURNER           1500          0            1500
ADAMS            1100                       1100
JAMES             950                        950
FORD             3000                       3000
MILLER           1300                       1300

К сожалению, формального обоснования применения функции 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. Оборотной стороной такого самоограничения окажется загромождение схемы данных и усложнение запросов к БД.