Операторы ветвления в команде SELECT
Вложенные функции мы затронули чуть ранее, сейчас рассмотрим их чуть детальнее. Также рассмотрим функции работы со значением NULL и функции, помогающие реализовать операцию ветвления в запросе.
Вложенные функции
Вложенные функции использую возвращаемые значение одной функции как входной параметр для другой функции. Функции всегда возвращают только одно значение. Поэтому вы можете рассматривать результат вызова функции как значение-литерал, когда используете его в качестве параметра вызова другой функции. Строчные функции могут быть вложены до любого уровня вложенности. Вызов один функции выглядит так
Function1(parameter1, parameter2, …) = result
Замена параметра функции на вызов другой функции может привести к появлению выражений вида
F1( param1.1, F2( param2.1, param2.2, F3( param3.1)), param1.3)
Вначале вычисляются вложенные функции перед тем как их результаты используются как входные значения для других функций. Функции вычисляются от самого глубокого уровня вложенности к самому верхнему слева направо. Предыдущее выражение выполняется следующим образом
- Вычисляется функция F3(param1) и возвращаемое значение используется как третий параметр для функции 2, назовём его param2.3
- Затем вычисляется функция F2(param1, param2.2, param2.3) и возвращаемое значение используется как второй параметр функции F1 – param1.2
- И наконец вычисляется функция F1(param1, param2, param1.3) и результат возвращается в вызывающую программу.
Таким образом функция F3 находится на третьем уровне вложенности.
Рассмотрим запрос
select next_day(last_day(sysdate)-7, ‘tue’) from dual;
- В этом запросе три функции, от нижнего уровня к верхнему – SYSDATE, LAST_DAY, NEXT_DAY. Запрос выполняется следующим образом
- Выполняется самая вложенная функция SYSDATE. Она возвращает текущее системное время. Предположим, что текущая дата 28 октября 2009 года
- Далее вычисляется результат функция второго уровня LAST_DAY. LAST_DATE(’28-OCT-2009’) возвращает последний день окбября 2009 года, то есть значение 31 октябрая 2009.
- Затем происходит вычитания из этой даты семи дней – получается 24 октября.
- И наконец вычисляется функция NEXT_DAY(’24-OCT-2009’, ‘tue’), и запрос возвращает последний вторник октября – что в нашем примере 27-OCT-2009.
Tip
Достаточно сложно разбираться и строить сложные выражения используя много вложенных вызовов функций, но это приходит со временем и практикой. Можно разбивать такие выражения на части и тестировать отдельно. Таблица DUAL очень полезно для тестирования з.апросов и результатов вызова функций. Можно тестировать и отлаживать небольшие компоненты, которые затем совмещать в одно большое нужное выражение.
Функции ветвления
Функции ветвления, также известные как ЕСЛИ-ТО-ИНАЧЕ, используется для определения пути выполнения в зависимости от каких-либо обстоятельств. Функции ветвления возвращают разные результат основываясь не результате вычисления условия. В группе таких функций выделяют функции работы со значением NULL: NVL, NVL2, NULLIF и COALESCE. И также общие функции, представленные функцией DECODE и выражением CASE. Функция DECODE является Oracle функцией, тогда как CASE выражение присутствует в стандарте ANSI SQL.
Функция NVL
Функция NVL проверяет значение столбца или выражения любого типа данных на значение NULL. Если значение NULL – она возвращает альтернативное не-NULL значение по умолчанию, иначе возвращается исходное значение.
У функции NVL два обязательных параметра и синтаксис NVL(original, ifnull) где original это исходное значение для проверки и ifnull результат возвращаемый функцией если original значение равно NULL. Тип данных параметров ifnull и original должен быть совместим. То есть либо тип данных должен быть одинаковым или должна быть возможность неявной конвертации значений из одного типа в другой. Функция NVL возвращает значение такого же типа данных как тип данных параметра original. Рассмотрим три запроса
Query 1: select nvl(1234) from dual;
Query 2: select nvl(null, 1234) from dual;
Query 3: select nvl(substr(‘abc’, 4), ‘No substring exists’) from dual;
Так как функции NVL необходимо два параметра, запрос 1 вернёт ошибку ORA-00909: invalid number of arguments. Запрос 2 вернёт 1234 так как проверяется значение NULL и оно равно NULL. Запрос три используется вложенную SUBSTR функцию которая пытается выделить четвёртый символ из строки длиной в три символа, возвращает значение NULL, а функция NVL возвращает строку ‘No sbustring exists’.
Tip
Функция NVL очень полезна при работе с числами. Она используется для конвертации NULL значений в 0, чтобы арифметические операции над числами не возвращали NULL
Функция NVL2
Функция NVL2 предоставляет больше функционала чем NVL, но служит также для обработки значения NULL. Она проверяет значение столбца или выражения любого типа на значение NULL. Если значение не равно NULL, то вовзращается второй параметр, иначе возвращается третий параметр, в отличии от функции NVL, которая в этом случае возвращает исходное значение.
У функции NVL2 три обязательных параметра и синтаксис NVL2(original, ifnotnull, ifnull), где original – это проверяемое значение, ifnotnull значение возвращаемое в случае если original не равно NULL и ifnull значение возвращаемое в случаем если original равно NULL. Типы данных параметров ifnotnull и ifnull должы быть совместимы, и они не могут быть типа LONG. Тип данных возвращаемых функцией NVL2 равен типу данных параметра ifnotnull. Рассмотрим несколько примеров
Query 1: select nvl2(1234, 1, ‘a string’) from dual;
Query 2: select nvl2(null, 1234, 5678) from dual;
Query 3: select nvl2(substr(‘abc’, 2), ‘Not bc’, ‘No substring’) from dual;
Параметра ifnotnull в запросе 1 это число, а параметр ifnull – это строка. Так как типы данных несовместимы, возвращается ошибка “ORA-01722: invalid number”. Запрос два возвращает ifnull параметр, так как original равно NULL и результатом будет 5678. Запрос три использует функция SUBSTR которая возвращает ‘bc’ и происходит вызов NVL2(‘bc’,’Not bc’,’No substring’) – который возвращает ifnotnull параметр – ‘Not bc’.
Функция NULLIF
Функция NULLIF проверяет два значения на идентичность. Если они одинаковы – возвращается NULL иначе возвращается первый параметр. У функции NULLIF два обязательных параметра и синтаксис NULLIF(ifunequal, comparison_item). Функция сравнивает два параметра и если они идентичны – возвращается NULL, иначе параметр ifunequal. Рассмотрим запросы
Query 1: select nullif(1234, 1234) from dual;
Query 2: select nullif(’24-JUL-2009′, ’24-JUL-09′) from dual;
Запрос один возвращает NULL так как параметры идентичны. Строки в запросе 2 не конвертируются в дату, а сравниваются как строки. Так как строки разной длины – возвращается параметра ifunequal 24-JUL-2009.
На рисунке 10-4 функция NULLIF вложена в функцию NVL2. В функции NULLIF в свою очередь используются функции SUBSTR и UPPER как части выражения в параметре ifunequal. Столбец EMAIL сравнивается с этим выражением, возвращающем первую букву имени, объединённую с фамилией для сотрудников у которых имя длиной в 4 символа. Когда эти значения равны, NULLIF вернёт NULL, иначение вернёт значение параметра ifunequal. Эти значения используюся как параметр для функции NVL2. NVL2 в свою очередь возвращает описание совпадали ли сравниваемые элементы или нет.
Рисунок 10-4 – Использование функции NULLIF
Функция COALESCE
Функция COALESCE возвращает первое значение не равное NULL из списка параметров. Если все параметры равны NULL, то возвращается NULL. У функции COALESCE два обязательных параметра и сколько угодно необязательных параметров и синтаксис COALESCE(expr1, expr2, …, exprn) где результатом будет expr1 если значение expr 1не NULL, иначе результатом будет expr2 если оно не NULL и т.д. COALESCE равно по смыслу вложенным функциям NVL
COALESCE(expr1, expr2) = NVL (expr1, expr2)
COALESCE(expr1, expr2, expr3) = NVL(expr1,NVL(expr2, expr3))
Тип данных возвращаемого значение если найдено не NULL значение равен типу данных первого не NULL значения. Для того чтобы избежать ошибки ‘ORA-00932: inconsistent data types’ все не NULL параметры должны быть совместимы с первым не NULL параметром. Рассмотрим три примера
Query 1: select coalesce(null, null, null, ‘a string’) from dual;
Query 2: select coalesce(null, null, null) from dual;
Query 3: select coalesce(substr(‘abc’, 4), ‘Not bc’, ‘No substring’) from dual;
Запрос 1 возвращает четвёртый параметр: строку, так как это первый не NULL параметр. Запрос два возвращает NULL так как все параметры равны NULL. Запрос 3 вычисляет первый параметр, получает значение NULL и возвращает второй параметр, так как он первый не NULL параметр.
Exam tip
Параметры функции NVL2 могут запутать если вы уже знакомы с функцие NVL. NVL(original, ifnull) возвращает original если значение не NULL, иначе ifnull. NVL2(original, ifnotnull, ifnull) возвращает ifnotnull если значение original не равно NULL иначе ifnull. Путаница происходит из-за того, что второй параметра функции NVL ifnull, тогда как у NVL2 это ifnotnull. Так что не надейтесь на позицию параметра в функции.
Функция DECODE
Функция DECODE реализует if-then-else логику проверяя первые два параметра на равенство и возвращая третье значение в случае их равенства или другое значение в случае неравенства. У функции DECODE три обязательных параметра и синтаксис DECODE(expr1, comp1, iftrue1, [comp2, iftrue2 … [compN, iftrueN], [iffalse]). Эти параметры используются как показано в слеующем примере псевдокода
IF expr1=comp1 then return iftrue1
Else if expr1=comp2 then return iftrue2
…
…
Else if exprN=compN then return iftrueN
Else return NULL|iffalse;
Вначале expr1 сравнивается с comp1. Если они равны возвращается значение iftrue1. Если expr1 не равно comp1, то что происходит дальше зависит от того заданы ли параметры comp2 и iftrue2. Если заданы, тов значение expr1 сравнивается с comp2. Если значения равны, то возвращается iftrue2. Если нет, то если есть пары параметров compN, iftrueN происходит сравнение expr1 и compN и в случае равнества возвращается iftrueN. Если не было найдено совпадение ни в одном наборе параметров, то возвращается или iffalse если этот параметр был задан, или NULL.
Все параметры в функции DECODE могут быть выражениями. Тип возвращаемого значения равен типу первого проверяющего элемента – параметра comp1. Выражение expr1 неявно преобразуется к типу данных параметра comp1. Все остальные доступные параметры comp1 … compN также неявно преобразуются к типу comp1. DECODE рассматривает значение NULL как равное другому значению NULL, т.е. если expr1 is NULL и comp3 is NULL, а comp2 не NULL, то возвращается значение iftrue3. Рассмотрим несколько примеров
Query 1: select decode(1234, 123, ‘123 is a match’) from dual;
Query 2: select decode(1234, 123, ‘123 is a match’, ‘No match’) from dual;
Query 3: select decode(‘search’, ‘comp1’, ‘true1’, ‘comp2’, ‘true2’, ‘search’, ‘true3’, substr(‘2search’, 2, 6), ‘true4’, ‘false’) from dual;
Запрос один сравнивает значение 1234 и 123. Так как они не равны то iftrue1 игнорируется и так как не определено значение iffalse то возвращается NULL. Запрос два идентичен запросу 1 за тем исключением что значение iffalse определено. Так как 1234 не равно 123 то возвращается iffalse – ‘No match’. Запрос три проверяет значения параметров на совпадения значению search. Параметры comp1 и comp2 не равны ‘search’ поэтому результаты iftrue1 и iftrue2 пропускаются. Совпадение найдено в третьей операции сравнения элемента comp3 (позиция параметра 6) и возвращается значение iftrue3 (параметр 7) которое равно ‘true3’. Так как совпадение найдено больше вычисления не производятся. То есть несмотря на то что значение comp4 (параметр 8) также совпадает с expr1 – это выражение никогда не рассчитывается так как совпадение было найдено в предыдущем сравнении.
Выражение CASE
Все языки программирования третьего и четвертого поколения реализуют конструкцию case. Как и функция DECODE, выражение CASE позволяет реализовывать if-then-else логику. Доступны два варианта использования выражения CASE. Простое CASE выражение устанавливает исходный элемент для сравнения единожды, а затем перечисляет все необходимые условия проверки. Сложный (searched) CASE вычисляет оба оператора для каждого условия.
У выражения CASE три обязательных параметра. Синтаксис выражения зависит от типа. Для простого CASE выражения он выглядит так
CASE search_expr
WHEN comparison_expr1 THEN iftrue1
[WHEN comparison _expr2 THEN iftrue2
…
WHEN comparison _exprN Then iftrueN
ELSE iffalse]
END
Выражение заключается в CASE … END блок и должно иметь хотя бы один WHEN … THEN элемент. В своей простейшей форме с одинм WHEN … THEN элементом параметр search_expr сравнивается с comparison_expr1 и, если они равны, возвращается iftrue1. Если нет, то возвращается значение NULL если не указан элемент ELSE. Если ELSE присутствует в выражении, то возвращается значение iffalse. Если в выражении больше чем один WHEN … THEN, то происходит сравнение значения search_expr пока не будет найдено совпадение.
Параметрами search_expr, comparison и iftrue могут быть значения столбца, выражения или литералы, но тип данных должен быть одинаковым. Рассмотрим следующий запрос
select
case substr(1234, 1, 3)
when ‘134’ then
‘1234 is a match’
when ‘1235’ then
‘1235 is a match’
when concat(‘1′, ’23’) then
concat(‘1′, ’23’)||’ is a match’
else
‘no match’
end
from dual;
Параметр search_expression рассчитываеся в функции SUBSTR и получается значение ‘123’. Первый WHEN … THEN сравнивает 134 и 123. Так как они не равны, проверяется следующий WHEN…THEN и сравнивается 123 с 12345 и значения снова не одинаковы. Третий WHEN … THEN сравнивает значение ‘123’ с выражением CONCAT(‘1’,’23’), которое даёт результат ‘123’. Сравниваемые значения равны, и возвращается результат третьего выражения ‘123 is a match’.
На рисунке 10-5 выполняется запрос, который выбирает столбцы LAST_NAME и HIRE_DATE из таблицы EMPLOYEES где значение DEPARTMENT_ID равны 10 или 60, а также два выражения и выражение CASE.
Рисунок 10-5 – сложное CASE выражение
Преположим что текущая дата 22 апреля 2016 года. Выражение с псевдонимом YEARS возвращает округленное в нижнюю сторону значения равного количеству месяцев с момента приёма на работу до текущей даты, разделённое на 12. Пять категорий лоялности сотрудника определены в зависимости от значения количества месяцев, проработанных в компании, делённых на число 60. Это значение формирует параметр condition1 для выражения CASE. Ни одна из строкв таблице не удовлетворяет первому условию, но удовлетворяет некоторым другим. Такие выражения называются сложными (searched) и синтаксис такого выражения
CASE
WHEN condition1 THEN iftrue1
[WHEN condition2 THEN iftrue2
…
WHEN conditionN THEN iftrueN
ELSE iffalse]
END
Сложное выражение заключается в конструкцию CASE … END и состоит минимум из одного WHEN…THEN блока. В простейшей форме с одним блоком WHEN…THEN вычисляется condition1 и если значение возвращает логическое ДА, то возвращается iftrue, иначе возвращается NULL если не указан ELSE iffalse, при наличии которого вместо NULL возвращается iffalse. Если в CASE выражении больше чем один блок WHEN…THEN то вычисление происходит пока не будет найдено совпадение. Запрос для получения результата, похожего на представленный на рисунке 10-5
select last_name, hire_date,
trunc(months_between(sysdate, hire_date)/12) years,
trunc(months_between(sysdate, hire_date)/60) «Years divided by 5»,
case
when trunc(months_between(sysdate, hire_date)/60) < 1 then ‘Intern’
when trunc(months_between(sysdate, hire_date)/60) < 2 then ‘Junior’
when trunc(months_between(sysdate, hire_date)/60) < 3 then ‘Intermediate’
when trunc(months_between(sysdate, hire_date)/60) < 4 then ‘Senior’
else
‘Furniture’
end Loyalty
from employees
where department_id in (60,10);
Чтобы убедиться что CASE вычисляет значения только до первого совпадения можно выполнить запрос
select case ‘A’
when ‘A’ then
‘X’
when ‘A’ then
‘X1’
else
‘X3’
end as f1
from dual;