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

Функции для работы со временем

Позволяют пополнить "временную арифметику" необходимыми или практичными операциями. Примеры функций:

ADD_MONTHS
LAST_DAY
MONTHS_BETWEEN
NEXT_DAY
ROUND
TRUNC
EXTRACT(9-)

(9-) начиная с версии 9

Допустим, что в поле BDATE типа DATE текущей строки находится значение "5 сентября 1999 г., 13 часов 30 минут 05 секунд". Справедливы следующие оценки выражений:

EXTRACT ( DAY FROM SYSTIMESTAMP )
EXTRACT ( HOUR FROM SYSTIMESTAMP )

EXTRACT ( MINUTE FROM INTERVAL '04:03' HOUR TO MINUTE )

[сегодняшний день](*) (типа NUMBER)
[время суток](*) (типа NUMBER)
и так далее
3 "минуты" (типа NUMBER)

TRUNC ( BDATE )
TRUNC ( BDATE, 'year' )

5 сентября 1999 года, 00:00:00
1 января 1999 года, 00:00:00
и так далее

(*) время компьютера с СУБД

Можно заметить, что Oracle, как и стандарт SQL, непоследователен в своем синтаксисе. Сравните указание компоненты момента времени в виде строки текста ('year' в функции TRUNC, а также ROUND) и с помощью ключевого слова (DAY, HOUR в функции EXTRACT).

Примеры использования:

SELECT EXTRACT ( YEAR FROM SYSTIMESTAMP ) FROM dual;
SELECT MONTHS_BETWEEN ( DATE '2009-03-01', DATE '2009-02-28' ) 
FROM   dual
;

Заметьте, что функции "месячной арифметики" ADD_MONTHS и MONTHS_BETWEEN вовсе не так очевидны.

Упражнение. Проверьте значения следующих выражений:

ADD_MONTHS ( DATE '2009-01-28', 1 )
ADD_MONTHS ( DATE '2009-01-29', 1 )
ADD_MONTHS ( DATE '2008-01-29', 1 )
ADD_MONTHS ( DATE '2009-01-30', 1 )

Функции условной подстановки значений

Дают возможность выполнить "преобразование" аргументов, а по сути — условную замену конкретных величин. Часть таких функций связана с желательной для программиста переработкой отсутствующих значений (в отдельном случае Not a Number), а функция DECODE — нет:

(IEEE 754) для типов BINARY_FLOAT/BINARY_DOUBLE

[9-) начиная с версии 9

Примеры:

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

В отличие от NVL и NVL2, COALESCE не вычисляет выражения-аргументы без надобности:

SELECT NVL ( 123, 1 / 0 ) FROM dual;
-- Ошибка !
SELECT COALESCE ( 123, 1 / 0 ) FROM dual;
-- OK 

Пример DECODE:

SELECT 
  deptno
, loc
, DECODE ( loc, 'NEW YORK', 'NEW YORK CITY', 'BOSTON', 'BOSTON AREA' )
FROM dept
;

Фактически DECODE позволяет сформулировать в тексте запроса таблицу подстановки значений. В нашем случае, если потребуется выдать исходное значение LOC, когда там не значения 'NEW YORK' и 'BOSTON', нужно будет добавить замыкающий четный аргумент:

DECODE 
( loc, 'NEW YORK', 'NEW YORK CITY', 'BOSTON', 'BOSTON AREA', loc )

Это не самое хорошее решение, так как иногда приходится повторять сложное выражение вторично, что чревато ошибками и лишними вычислениями. Кроме того, методически оправданно держать правила преобразования в БД, а не в тексте запроса (если только эти правила имеют прикладное значение). На практике же нахождение таблицы преобразования в БД резко замедлит вычисление.

Нескалярные функции для анализа данных

Таковых имеется две категории: "агрегатные", то есть обобщающие, и "аналитические".

Агрегатные функции иначе называют "стандартными агрегатными" функциями и "агрерирующими" функциями. До версии 8.1.6 они (сокращенным количеством) назывались "статистическими". Они дают скалярный результат, но аргументом им служит столбец значений, чем они конструктивно отличаются от большинства других встроенных функций. Это же сообщает им обобщающий характер.

Некоторые из них:

STDDEV,
STDDEV_POP,
STDDEV_SAM

[10-) с версии 10

Некоторые другие примеры: CORR, COVAR_POP, COVAR_SAMP, CUME_DIST, PERCENTILE_COUNT и так далее.

Начиная с версии 10 Oracle позволяет производить более "серьезные" статистические обобщения данных столбца таблицы, но уже не средствами SQL, а программно, с помощью процедур из встроенного пакета DBMS_STAT_FUNCS. Они позволяют определить соответствие указанного значения тому или иному виду статистического распределения, а также обобщать данные столбца всеми способами стандартных агрегатных функций, но вдобавок со значительным количеством дополнительной обобщающей информации.

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

Агрегатные и аналитические функции отличаются от скалярных по формальному употреблению в тексте запроса и требуют в силу этого отдельного рассмотрения, которое последует в соответствующих разделах описания предложения SELECT ниже.