что значит nvl в sql

Что значит nvl в sql

Использование функций Coalesce(), ISNULL() и NVL():

Если Вы взгляните на вышеупомянутый результирующий набор, то заметите, что везде, где значение TEST_COL1 есть NULL, запрос возвратил значение TEST_COL2. Для Informix и MYSQL оба столбца имеют значения NULL, поэтому было возвращено NULL-значение. Теперь давайте выполним подобный оператор с использованием COALESCE. С помощью COALESCE мы хотим получить такие данные: если значение TEST_COL1 есть NULL, то должно быть возвращено значение столбца TEST_COL2, а если значение TEST_COL2 также является NULL, то возвращаться должна константа 9999.

В вышеприведенном коде SQL мы использовали три аргумента в функции COALESCE, и она возвратила первое не NULL значение, при условии, что хотя бы одно выражение или столбец содержит не NULL-значение. Если все значения будут NULL, то и окончательным результатом будет NULL. Вышеприведенный оператор SQL эквивалентен следующему выражению CASE.

После выполнения этот запрос также возвратит то же самое значение, что и оператор с COALESCE. Последний скрывает сложность выражения CASE, когда имеется более 3-х аргументов. Следует помнить один момент, а именно, когда используется COALESCE, все выражения должны иметь один и тот же тип данных или же они должны быть неявно конвертируемыми к одному и тому же типу данных, в противном случае возникает ошибка.

Пожалуйста, не забывайте что, если Вы используете функции на индексируемых столбцах в критериях отбора или в условиях соединения, то индекс использоваться не будет. Есть способы заставить его работать (использование FBI в Oracle, вычисляемые столбцы в SQL Server или столбцы генерации выражения в DB2 LUW), если Вам действительно необходимо это сделать

Другие различия, о которых Вы должны знать:

— Другая важная вещь, когда Вы используете ISNULL(), заключается в том, что эта функция оценивает первое значение, и значение второго параметра автоматически ограничивается этой длиной; COALESCE() не имеет этого ограничения. Вот пример (синтаксис T-SQL):

Функция ISNULL() возвращает ‘A’, в то время как coalesce вернет ‘ABCD’. Нужно иметь это в виду, иначе вы получите неожиданные результаты.

— И точно так же, как в посте UNION/UNION ALL, в котором мы рассуждали о неявных преобразованиях типа данных, приводящих к проблемам, неявные преобразования типа данных могут создать головную боль и здесь. В случае функции COALESCE(), если значения имеют различные типы данных, Вы можете получить как ошибки, так и неверные результаты. Пример:

В результате получаем ошибку: Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘test’ to data type int.
(преобразование значение varchar ‘test’ к тапу данных int вызывает ошибку)

даст:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
(ошибка арифметического переполнения при преобразовании выражения к типу данных datetime)

вернет неверные результаты в результате неявного преобразования типа. Вы получите: «1900-04-11 00:00:00.000» вместо 100.

Итак, вы должны знать, что при использовании COALESCE(), все значения/выражения должны быть одного и того же типа или должны быть неявно преобразовываемыми к одному и тому же типу данных.

Источник

Русские Блоги

Функция NVL в Oracle

Oracle ранее представила обработку строк, функции даты, математические функции и функции преобразования, а также класс функций, которые являются универсальными функциями. В основном: NVL, NVL2, NULLIF, COALESCE, эти функции могут использоваться в различных типах.

Ниже кратко описывается использование нескольких функций.

Прежде чем вводить это, вы должны понять, что является нулем в оракуле

Формат функции NVL выглядит следующим образом: NVL(expr1,expr2)

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

Формат функции NVL2 выглядит следующим образом: NVL2(expr1,expr2, expr3)

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

Функция функции NULLIF (exp1, expr2) должна возвращать NULL, если exp1 и exp2 равны, в противном случае возвращается первое значение.
Ниже приведен пример. Используйте схему HR в Oracle, если HR заблокирован, пожалуйста, включите

Роль здесь состоит в том, чтобы показать, что те, кто сменил работу, сейчас работают.

Вы можете увидеть всех сотрудников. job_id и job_histroy.job_id равны, оба результата NULL в результате пустые, в противном случае сотрудник отображается. job_id

Функция функции Coalese состоит в том, что функция NVL немного похожа, и ее преимущество в том, что есть больше опций.

Формат выглядит следующим образом:

Заполнитель, указывающий, что можно указать несколько выражений. Все выражения должны быть одного типа или могут быть неявно преобразованы в один и тот же тип.
Возвращает первое непустое выражение в выражении, если есть следующее утверждение: SELECT COALESCE (NULL, NULL, 3,4,5) FROM dual Результат возврата: 3
Если все аргументы равны NULL, COALESCE возвращает значение NULL. COALESCE (выражение1,… n) эквивалентно этой функции CASE:
Эта функция на самом деле является переработкой NVL, поэтому здесь я не приведу пример.

Источник

SQL Функции NULL

SQL Функции IFNULL(), ISNULL(), COALESCE(), и NVL()

Посмотрите на следующую таблицу «Products»:

Предположим, что столбец «UnitsOnOrder» является необязательным и может содержать нулевые значения.

Посмотрите на следующую инструкцию SELECT:

В приведенном выше примере, если какое-либо из значений «UnitsOnOrder» равно NULL, результат будет равен нулю.

Решения

MySQL функция IFNULL() позволяет возвращать альтернативное значение, если выражение равно нулю:

или мы можем использовать функцию COALESCE(), например:

SQL Server функция ISNULL() позволяет возвращать альтернативное значение, если выражение равно нулю:

MS Access функция IsNull() возвращает TRUE (-1), если выражение имеет нулевое значение, в противном случае FALSE (0):

Oracle функция NVL() достигает того же результата:

Упражнения

Тесты

КАК СДЕЛАТЬ

ПОДЕЛИТЬСЯ

СЕРТИФИКАТЫ

Сообщить об ошибке

Если вы хотите сообщить об ошибке или сделать предложение, не стесняйтесь, присылайте нам электронное письмо:

Ваше предложение:

Спасибо, за вашу помощь!

Ваше сообщение было отправлено в SchoolsW3.

Топ Учебники

Топ Справочники

Топ Примеры

Веб Сертификаты

Источник

Особенности использования функций COALESCE(), ISNULL(), NVL()

Использование функций Coalesce(), ISNULL() и NVL():

Если Вы взгляните на вышеупомянутый результирующий набор, то заметите, что везде, где значение TEST_COL1 есть NULL, запрос возвратил значение TEST_COL2. Для Informix и MYSQL оба столбца имеют значения NULL, поэтому было возвращено NULL-значение. Теперь давайте выполним подобный оператор с использованием COALESCE. С помощью COALESCE мы хотим получить такие данные: если значение TEST_COL1 есть NULL, то должно быть возвращено значение столбца TEST_COL2, а если значение TEST_COL2 также является NULL, то возвращаться должна константа 9999.

В вышеприведенном коде SQL мы использовали три аргумента в функции COALESCE, и она возвратила первое не NULL значение, при условии, что хотя бы одно выражение или столбец содержит не NULL-значение. Если все значения будут NULL, то и окончательным результатом будет NULL. Вышеприведенный оператор SQL эквивалентен следующему выражению CASE.

После выполнения этот запрос также возвратит то же самое значение, что и оператор с COALESCE. Последний скрывает сложность выражения CASE, когда имеется более 3-х аргументов. Следует помнить один момент, а именно, когда используется COALESCE, все выражения должны иметь один и тот же тип данных или же они должны быть неявно конвертируемыми к одному и тому же типу данных, в противном случае возникает ошибка.

Пожалуйста, не забывайте что, если Вы используете функции на индексируемых столбцах в критериях отбора или в условиях соединения, то индекс использоваться не будет. Есть способы заставить его работать (использование FBI в Oracle, вычисляемые столбцы в SQL Server или столбцы генерации выражения в DB2 LUW), если Вам действительно необходимо это сделать

Другие различия, о которых Вы должны знать:

— Другая важная вещь, когда Вы используете ISNULL(), заключается в том, что эта функция оценивает первое значение, и значение второго параметра автоматически ограничивается этой длиной; COALESCE() не имеет этого ограничения. Вот пример (синтаксис T-SQL):

Функция ISNULL() возвращает ‘A’, в то время как coalesce вернет ‘ABCD’. Нужно иметь это в виду, иначе вы получите неожиданные результаты.

— И точно так же, как в посте UNION/UNION ALL, в котором мы рассуждали о неявных преобразованиях типа данных, приводящих к проблемам, неявные преобразования типа данных могут создать головную боль и здесь. В случае функции COALESCE(), если значения имеют различные типы данных, Вы можете получить как ошибки, так и неверные результаты. Пример:

В результате получаем ошибку: Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘test’ to data type int.
(преобразование значение varchar ‘test’ к тапу данных int вызывает ошибку)

даст:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
(ошибка арифметического переполнения при преобразовании выражения к типу данных datetime)

вернет неверные результаты в результате неявного преобразования типа. Вы получите: «1900-04-11 00:00:00.000» вместо 100.

Итак, вы должны знать, что при использовании COALESCE(), все значения/выражения должны быть одного и того же типа или должны быть неявно преобразовываемыми к одному и тому же типу данных.

Источник

Операторы ветвления в команде SELECT

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

Вложенные функции

Вложенные функции использую возвращаемые значение одной функции как входной параметр для другой функции. Функции всегда возвращают только одно значение. Поэтому вы можете рассматривать результат вызова функции как значение-литерал, когда используете его в качестве параметра вызова другой функции. Строчные функции могут быть вложены до любого уровня вложенности. Вызов один функции выглядит так

Function1(parameter1, parameter2, …) = result

Замена параметра функции на вызов другой функции может привести к появлению выражений вида

F1( param1.1, F2( param2.1, param2.2, F3( param3.1)), param1.3)

Вначале вычисляются вложенные функции перед тем как их результаты используются как входные значения для других функций. Функции вычисляются от самого глубокого уровня вложенности к самому верхнему слева направо. Предыдущее выражение выполняется следующим образом

Таким образом функция F3 находится на третьем уровне вложенности.

select next_day(last_day(sysdate)-7, ‘tue’) from dual;

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

Функция 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 параметр.

Параметры функции 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 могут быть выражениями. Тип возвращаемого значения равен типу первого проверяющего элемента – параметра 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 выражения он выглядит так

WHEN comparison_expr1 THEN iftrue1

[WHEN comparison _expr2 THEN iftrue2

WHEN comparison _exprN Then iftrueN

Выражение заключается в CASE … END блок и должно иметь хотя бы один WHEN … THEN элемент. В своей простейшей форме с одинм WHEN … THEN элементом параметр search_expr сравнивается с comparison_expr1 и, если они равны, возвращается iftrue1. Если нет, то возвращается значение NULL если не указан элемент ELSE. Если ELSE присутствует в выражении, то возвращается значение iffalse. Если в выражении больше чем один WHEN … THEN, то происходит сравнение значения search_expr пока не будет найдено совпадение.

Параметрами search_expr, comparison и iftrue могут быть значения столбца, выражения или литералы, но тип данных должен быть одинаковым. Рассмотрим следующий запрос

case substr(1234, 1, 3)

when concat(‘1′, ’23’) then

concat(‘1′, ’23’)||’ is a match’

Параметр 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) и синтаксис такого выражения

WHEN condition1 THEN iftrue1

[WHEN condition2 THEN iftrue2

WHEN conditionN THEN iftrueN

Сложное выражение заключается в конструкцию 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»,

when trunc(months_between(sysdate, hire_date)/60)

Источник