Функция NVL
Функция NVL, как правило, применяется чаще всего. Функция получает два параметра: NVL(expr1, expr2). Если первый параметр expr1 не равен NULL, то функция возвращает его значение. Если первый параметр NULL, то вместо него функция возвращает значение второго параметра expr2.
Рассмотрим практический пример. Поле COMM в таблице EMP может содержать значения NULL. При выполнении запроса вида:
SELECT EMPNO, ENAME, COMM, NVL(COMM, 0) NVL_COMM
FROM SCOTT.EMP
значение NULL будет заменено на ноль. Обратите внимание на то, что в случае формирования значения при помощи функции ему назначается псевдоним. Результаты запроса будут иметь вид:
| EMPNO | ENAME | COMM | NVL_COMM |
| 7369 | SMITH | 0 | |
| 7499 | ALLEN | 300 | 300 |
| 7521 | WARD | 500 | 500 |
| 7566 | JONES | 0 | |
| 7654 | MARTIN | 1400 | 1400 |
| 7698 | BLAKE | 0 | |
| 7782 | CLARK | 0 | |
| 7839 | KING | 0 | |
| 7844 | TURNER | 0 | 0 |
| 7900 | JAMES | 0 | |
| 7902 | FORD | 0 | |
| 7934 | MILLER | 0 |
Функция CEIL(n)
Функция CEIL возвращает наименьшее целое, большее или равное переданному в качестве параметра числу n. Например:
SELECT CEIL(100) X1, CEIL(-100) X2, CEIL(100.2) X3 , CEIL(-100.2) X4
FROM DUAL
Функция TRUNC(n [,m])
Функция TRUNC возвращает число n, усеченное до m знаков после десятичной точки. Параметр m может не указываться – в этом случае n усекается до целого.
SELECT TRUNC(100.25678) X1, TRUNC(-100.25678) X2, TRUNC(100.99) X3,
TRUNC(100.25678, 2) X4
FROM DUAL
Функция SIGN(n)
Функция SIGN определяет знак числа. Если n положительное, то функция возвращает 1. Если отрицательное возвращается -1. Если равно нулю, то возвращается 0. Например:
SELECT SIGN(100.22) X1, SIGN(-100.22) X2, SIGN(0) X3
FROM DUAL
Интересной особенностью данной функции является возможность передачи m равного нулю при этом не возникает ошибки деления на 0.
Функция POWER(n, m)
Функция POWER возводит число n в степень m. Степень может быть дробной и отрицательной, что существенно расширяет возможности данной функции.
SELECT POWER(10, 2) X1, POWER(100, 1/2) X2,
POWER(1000, 1/3) X3, POWER(1000, -1/3) X4
FROM DUAL
| X1 | X2 | X3 | X4 |
| 100 | 10 | 10 | 0,1 |
В некоторых случаях при вызове данной функции может возникнуть исключительная ситуация. Например:
SELECT POWER(-100, 1/2) X2
FROM DUAL
В данном случае производится попытка вычисления квадратного корня от отрицательного числа, что приведет к возникновению ошибки ORA-01428 «Аргумент вне диапазона».
Функция SQRT(n)
Данная функция возвращает квадратный корень от числа n. Например:
SELECT SQRT(100) X
FROM DUAL
Функции EXP(n) и LN(n)
Функция EXP возводит e в степень n, а функция LN вычисляет натуральный логарифм от n (при этом n должно быть больше нуля). Пример:
SELECT EXP(2) X1, LN(1) X2, LN(EXP(2)) X3
Функция NVL
Функция NVL, как правило, применяется чаще всего. Функция получает два параметра: NVL(expr1, expr2). Если первый параметр expr1 не равен NULL, то функция возвращает его значение. Если первый параметр NULL, то вместо него функция возвращает значение второго параметра expr2.
Рассмотрим практический пример. Поле COMM в таблице EMP может содержать значения NULL. При выполнении запроса вида:
SELECT EMPNO, ENAME, COMM, NVL(COMM, 0) NVL_COMM
FROM SCOTT.EMP
значение NULL будет заменено на ноль. Обратите внимание на то, что в случае формирования значения при помощи функции ему назначается псевдоним. Результаты запроса будут иметь вид:
| EMPNO | ENAME | COMM | NVL_COMM |
| 7369 | SMITH | 0 | |
| 7499 | ALLEN | 300 | 300 |
| 7521 | WARD | 500 | 500 |
| 7566 | JONES | 0 | |
| 7654 | MARTIN | 1400 | 1400 |
| 7698 | BLAKE | 0 | |
| 7782 | CLARK | 0 | |
| 7839 | KING | 0 | |
| 7844 | TURNER | 0 | 0 |
| 7900 | JAMES | 0 | |
| 7902 | FORD | 0 | |
| 7934 | MILLER | 0 |
Функция CEIL(n)
Функция CEIL возвращает наименьшее целое, большее или равное переданному в качестве параметра числу n. Например:
SELECT CEIL(100) X1, CEIL(-100) X2, CEIL(100.2) X3 , CEIL(-100.2) X4
FROM DUAL
Функция TRUNC(n [,m])
Функция TRUNC возвращает число n, усеченное до m знаков после десятичной точки. Параметр m может не указываться – в этом случае n усекается до целого.
SELECT TRUNC(100.25678) X1, TRUNC(-100.25678) X2, TRUNC(100.99) X3,
TRUNC(100.25678, 2) X4
FROM DUAL
Функция SIGN(n)
Функция SIGN определяет знак числа. Если n положительное, то функция возвращает 1. Если отрицательное возвращается -1. Если равно нулю, то возвращается 0. Например:
SELECT SIGN(100.22) X1, SIGN(-100.22) X2, SIGN(0) X3
FROM DUAL
Интересной особенностью данной функции является возможность передачи m равного нулю при этом не возникает ошибки деления на 0.
Функция POWER(n, m)
Функция POWER возводит число n в степень m. Степень может быть дробной и отрицательной, что существенно расширяет возможности данной функции.
SELECT POWER(10, 2) X1, POWER(100, 1/2) X2,
POWER(1000, 1/3) X3, POWER(1000, -1/3) X4
FROM DUAL
| X1 | X2 | X3 | X4 |
| 100 | 10 | 10 | 0,1 |
В некоторых случаях при вызове данной функции может возникнуть исключительная ситуация. Например:
SELECT POWER(-100, 1/2) X2
FROM DUAL
В данном случае производится попытка вычисления квадратного корня от отрицательного числа, что приведет к возникновению ошибки ORA-01428 «Аргумент вне диапазона».
Функция SQRT(n)
Данная функция возвращает квадратный корень от числа n. Например:
SELECT SQRT(100) X
FROM DUAL
Функции EXP(n) и LN(n)
Функция EXP возводит e в степень n, а функция LN вычисляет натуральный логарифм от n (при этом n должно быть больше нуля). Пример:
SELECT EXP(2) X1, LN(1) X2, LN(EXP(2)) X3
Вложенные функции мы затронули чуть ранее, сейчас рассмотрим их чуть детальнее. Также рассмотрим функции работы со значением 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.
Достаточно сложно разбираться и строить сложные выражения используя много вложенных вызовов функций, но это приходит со временем и практикой. Можно разбивать такие выражения на части и тестировать отдельно. Таблица 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’.
Функция NVL очень полезна при работе с числами. Она используется для конвертации NULL значений в 0, чтобы арифметические операции над числами не возвращали NULL
Функция NVL 2
Функция 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;
Запрос один возвращает 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 параметр.
Параметры функции 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, , ). Эти параметры используются как показано в слеующем примере псевдокода
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 могут быть выражениями. Тип возвращаемого значения равен типу первого проверяющего элемента – параметра comp 1. Выражение expr 1 неявно преобразуется к типу данных параметра comp 1. Все остальные доступные параметры comp 1 … compN также неявно преобразуются к типу comp 1. 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
}
Программы и игры
