Oracle что такое coalesce
Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL . For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because the third value is the first value that isn't null.
Syntax
Arguments
expression
Is an expression of any type.
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
Return Types
Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.
Remarks
If all arguments are NULL , COALESCE returns NULL . At least one of the null values must be a typed NULL .
Comparing COALESCE and CASE
The COALESCE expression is a syntactic shortcut for the CASE expression. That is, the code COALESCE (expression1,. n) is rewritten by the query optimizer as the following CASE expression:
As such, the input values (expression1, expression2, expressionN, and so on) are evaluated multiple times. A value expression that contains a subquery is considered non-deterministic and the subquery is evaluated twice. This result is in compliance with the SQL standard. In either case, different results can be returned between the first evaluation and upcoming evaluations.
For example, when the code COALESCE((subquery), 1) is executed, the subquery is evaluated twice. As a result, you can get different results depending on the isolation level of the query. For example, the code can return NULL under the READ COMMITTED isolation level in a multi-user environment. To ensure stable results are returned, use the SNAPSHOT ISOLATION isolation level, or replace COALESCE with the ISNULL function. As an alternative, you can rewrite the query to push the subquery into a subselect as shown in the following example:
Comparing COALESCE and ISNULL
The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.
Because ISNULL is a function, it's evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times.
Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.
The NULLability of the result expression is different for ISNULL and COALESCE . The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one). By contrast, COALESCE with non-null parameters is considered to be NULL . So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) , although equal, have different nullability values. These values make a difference if you're using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example:
Validations for ISNULL and COALESCE are also different. For example, a NULL value for ISNULL is converted to int though for COALESCE , you must provide a data type.
ISNULL takes only two parameters. By contrast COALESCE takes a variable number of parameters.
Examples
A. Running a simple example
The following example shows how COALESCE selects the data from the first column that has a nonnull value. This example uses the AdventureWorks2012 database.
B. Running a complex example
In the following example, the wages table includes three columns that contain information about the yearly wages of the employees: the hourly wage, salary, and commission. However, an employee receives only one type of pay. To determine the total amount paid to all employees, use COALESCE to receive only the nonnull value found in hourly_wage , salary , and commission .
Here is the result set.
C: Simple Example
The following example demonstrates how COALESCE selects the data from the first column that has a non-null value. Assume for this example that the Products table contains this data:
We then run the following COALESCE query:
Here is the result set.
Notice that in the first row, the FirstNotNull value is PN1278 , not Socks, Mens . This value is this way because the Name column wasn't specified as a parameter for COALESCE in the example.
D: Complex Example
The following example uses COALESCE to compare the values in three columns and return only the non-null value found in the columns.
Если для Вас выборка большого количества данных и помещение их в переменную PL/SQL важнее чем циклический проход по результирующей выборке, то Вы можете использовать выражение BULK COLLECT. Если в Вашей выборке всего несколько колонок, то каждую из них Вы можете сохранить в отдельную переменную - коллекцию. Если Вы выбираете все колонки таблицы, то можете сохранить результат выборки в коллекции записей. Такая коллекция весьма удобна для циклического перебора результирующих записей, поля которых ссылаются на колонки таблицы.
-
Используя BULK COLLECT, Вы можете улучшить код, выполняя больше работы в SQL:
- Если Вам надо пройти по результирующей выборке только один раз, используйте цикл For. Этот подход позволяет избежать выделение памяти на хранение копии результирующих данных.
- Если из результирующих данных Вам требуется выбрать определенные значения и поместить их в меньшую выборку, используйте фильтрацию в основном выражении. В простом случае используйте условия WHERE. Для сравнения двух и более наборов данных применяйте выражения INTERSECT и MINUS.
- Если Вы циклически проходите по результирующей выборке и для каждого ряда выполняете DML-выражение или делаете другую выборку, используйте более эффективных подход. Попробуйте вложенную выборку переделать в подзапрос основной выборки, если возможно, используйте выражения EXISTS или NOT EXISTS. Для DML, рассмотрите возможность использования выражения FORALL, который значительно более быстрый, чем аналогичное выражение, выполненное внутри цикла.
Еще один пример использования BULK COLLECT
DECLARE
TYPE EmployeeSet IS TABLE OF employees%ROWTYPE;
underpaid EmployeeSet; -- Набор рядов таблицы EMPLOYEES.
CURSOR c1 IS SELECT first_name, last_name FROM employees;
TYPE NameSet IS TABLE OF c1%ROWTYPE;
some_names NameSet; -- Набор неполных рядов таблицы EMPLOYEES
-- С помощью одного запроса мы извлекаем все данные, соответствующие условиям, в коллекцию записей
-- Сейчас мы можем обработать данные, выбранные запросом, или передать их в отдельную процедуру.
DBMS_OUTPUT.PUT_LINE(underpaid. COUNT || ' people make less than 5000.' );
FOR i IN underpaid. FIRST ..underpaid. LAST
LOOP
DBMS_OUTPUT.PUT_LINE(underpaid(i).last_name || ' makes ' || underpaid(i).salary);
END LOOP;
-- А сейчас мы сделаем выборку только по некоторым полям таблицы.
-- Получим фамилию и имя десяти случайных сотрудников.
FOR i IN some_names. FIRST ..some_names. LAST
LOOP
DBMS_OUTPUT.PUT_LINE( 'Employee = ' || some_names(i).first_name || ' ' ||
some_names(i).last_name);
END LOOP;
END ;
/
Извлечение результатов выборки в коллекции, используя выражение BULK COLLECT.
Использование ключевых слов BULK COLLECT в выборках - очень эффективный способ получения результирующих данных. Вместо циклической обработки каждого ряда, Вы сохраняете результат в одной или нескольких коллекциях, все это делается в рамках одной операцией. Это ключевое слово может использоваться совместно с выражениями SELECT INTO, FETCH INTO и RETURNING INTO.
При использовании ключевых слов BULK COLLECT все переменные в списке INTO должны быть коллекциями. Колонки таблицы могут быть как скалярными значениями так и структурами, включая объектные типы.
TYPE NumTab IS TABLE OF employees.employee_id%TYPE;
TYPE NameTab IS TABLE OF employees.last_name%TYPE;
enums NumTab; -- Нет необходимости инициализировать коллекцию.
names NameTab; -- Значения будут заполнены выражением SELECT INTO.
-- Извлечение данных по сотрудникам, идентификатор которых больше 1000
-- Все данные помещены в память выражением BULK COLLECT
-- Нет необходимости выполнять FETCH для каждого ряда результирующих данных
-- Выборка приблизительно 20% всех рядов
SELECT employee_id, last_name BULK COLLECT
INTO enums, names
FROM employees SAMPLE (20);
Коллекции инициализируются автоматически. Вложенные таблицы и ассоциативные массивы расширяются для сохранения необходимого количества элементов. Если Вы используете массивы с фиксированным размером, убедитесь, что декларируемый размер массива соответствует объемам выбираемых данных. Элементы вставляются в коллекции, начиная с индекса 1, при этом все существующие значения перезаписываются.
Т.к. обработка выражения BULK COLLECT INTO подобна циклу FETCH, не генерируется исключение NO_DATA_FOUND, если не выбран ни один ряд. Если требуется, наличие выбранных данных надо проверять вручную.
Чтобы предотвратить переполнение памяти данными выборки, Вы можете использовать выражение LIMIT или псевдоколонку ROWNUM для ограничения числа записей в выборке. Кроме того возможно использование выражения SAMPLE для получения набора случайных записей.
-- Получение 10% (приблизительно) записей в таблице
SELECT salary BULK COLLECT
INTO sals
FROM employees SAMPLE (10);
END ;
/
Вы можете обрабатывать большие объемы результирующих данных, указав количество записей, которые будут выбраны из курсора за один раз.
PROCEDURE print_results IS
BEGIN
IF names IS NULL OR names . COUNT = 0 THEN -- проверка, не пустая ли коллекция
DBMS_OUTPUT.PUT_LINE( 'No results!' );
ELSE
DBMS_OUTPUT.PUT_LINE( 'Results: ' );
FOR i IN names . FIRST .. names . LAST
LOOP
DBMS_OUTPUT.PUT_LINE( ' Employee ' || names (i) || ': $' || sals(i));
END LOOP;
END IF ;
END ;
BEGIN
DBMS_OUTPUT.PUT_LINE( '--- Обрабатываем все результаты за раз ---' );
OPEN c1;
FETCH c1 BULK COLLECT INTO names , sals;
CLOSE c1;
print_results();
DBMS_OUTPUT.PUT_LINE( '--- Обрабатываем ' || v_limit || ' рядов за раз ---' );
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO names , sals LIMIT v_limit;
EXIT WHEN names . COUNT = 0;
print_results();
END LOOP;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE( '--- Извлекаем ряды вместо отдельных колонок ---' );
OPEN c1;
FETCH c1 BULK COLLECT INTO recs;
FOR i IN recs. FIRST ..recs. LAST
LOOP
-- Сейчас все колонки берем сразу из результирующего набора данных
DBMS_OUTPUT.PUT_LINE( ' Employee ' || recs(i).last_name || ': $' || recs(i).salary);
END LOOP;
END ;
/
Ограничение числа рядов в выборке с помощью условия Limit
Дополнительное условие LIMIT может использоваться только с выражением FETCH и ограничивает число рядов, выбираемых из баз данных. В следующем примере на каждой итерации цикла извлекается не больше десяти рядов и помещается в таблицу empids. Предыдущие значения перетираются. Обратите внимание на использование empids.count как условия выхода из цикла.
DECLARE
TYPE numtab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
CURSOR c1 IS
SELECT employee_id
FROM employees
WHERE department_id = 80;
empids numtab;
rows PLS_INTEGER := 10;
BEGIN
OPEN c1;
LOOP -- следующее выражение извлекает не больше 10 рядов за одну итерацию
FETCH c1 BULK COLLECT INTO empids LIMIT rows ;
EXIT WHEN empids. COUNT = 0;
-- EXIT WHEN c1%NOTFOUND; -- это условие некорректно, можно потерять часть данных
DBMS_OUTPUT.PUT_LINE( '------- Results from Each Bulk Fetch --------' );
FOR i IN 1..empids. COUNT
LOOP
DBMS_OUTPUT.PUT_LINE( 'Employee Id: ' || empids(i));
END LOOP;
END LOOP;
CLOSE c1;
END ;
/
Передача результатов операций DML в коллекцию, используя выражение RETURNING INTO
Вы можете использовать BULK COLLECT в условии RETURNING INTO выражений INSERT, UPDATE, DELETE.
CREATE TABLE emp_temp AS SELECT * FROM employees;
Совместное использование FORALL и BULK COLLECT
Вы можете объединить условие BULK COLLECT и выражение FORALL. Результирующая коллекция будет заполнена итерациями выражения FORALL. В следующем примере для каждого удаленного ряда значение employee_id сохраняется в коллекцию e_ids. Коллекция depts хранит три элемента, таким образом выражение FORALL выполнит три итерации. Если каждый оператор DELTE выполненный выражением FORALL удалит пять рядов, то в результате коллекция e_ids, которая хранит значения из удаленных рядов, будет содержать 15 элементов.
CREATE TABLE emp_temp AS SELECT * FROM employees;
Значения столбцов, удаленных каждой итерацией, добавляются к ранее полученным значениям коллекций. Если бы использовался цикл FOR вместо выражения FORALL, то набор результирующих значений перетирался бы следующим выполнением выражения DELETE. Не допускается использование конструкции SELECT . BULK COLLECT в выражении FORALL.
Приведём простые примеры пока без имён столбцов и подзапросов.
COALESCE для простой замены NULL-значения
При создании таблицы базы данных можно для ряда столбцов предусмотреть значения по умолчанию NULL. Тогда, если при вставке новой строки в такой столбец не вставить никакого значения, его значение будет неопределённым (NULL). Однако при выводе данных неопределённое (его ещё можно назвать пустым) значение не всегда пригодно. В таких случаях используется функция COALESCE.
В первых примерах работаем с базой данных библиотеки и её таблицей "Книга в выдаче" (BOOKINUSE). Операции будут относиться к столбцам Author (автор книги) и Title (название книги).
Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .
Скрипт для создания базы данных библиотеки, её таблиц и заполения таблиц данными - в файле по этой ссылке .
Пример 1. Есть база данных библиотеки и таблица "Книга в выдаче" (BOOKINUSE). Таблица выглядит так:
| Author | Title | Pubyear | Inv_No | Customer_ID |
| Толстой | Война и мир | 2005 | 28 | 65 |
| Чехов | Вишневый сад | 2000 | 17 | 31 |
| Чехов | Избранные рассказы | 2011 | 19 | 120 |
| Чехов | Вишневый сад | 1991 | 5 | 65 |
| Ильф и Петров | Двенадцать стульев | 1985 | 3 | 31 |
| Маяковский | Поэмы | 1983 | 2 | 120 |
| Пастернак | Доктор Живаго | 2006 | 69 | 120 |
| Толстой | Воскресенье | 2006 | 77 | 47 |
| Толстой | Анна Каренина | 1989 | 7 | 205 |
| Пушкин | Капитанская дочка | 2004 | 25 | 47 |
| Гоголь | Пьесы | 2007 | 81 | 47 |
| Чехов | Избранные рассказы | 1987 | 4 | 205 |
| Пушкин | Сочинения, т.1 | 1984 | 6 | 47 |
| Пастернак | Избранное | 2000 | 137 | 18 |
| Пушкин | Сочинения, т.2 | 1984 | 8 | 205 |
| NULL | Наука и жизнь 9 2018 | 2019 | 127 | 18 |
| Чехов | Ранние рассказы | 2001 | 171 | 31 |
Как видим, в последней строке отсутствует определённное значение столбца Author, так как выданное издание является журналом. Пусть требуется вывести авторов выданных изданий с определёнными инвентарными номерами и при этом ни одно из полей не должно быть пустым. Для этого пишем запрос с использованием фукнции COALESCE:
SELECT COALESCE (Author, 'Журнал') AS InUse FROM Bookinuse WHERE inv_no IN (25, 81, 127)Для издания с инвентарным номером 127 будет возвращено первое не-NULL значение - 'Журнал' и результирующая таблица будет выглядеть так:
| InUse |
| Пушкин |
| Гоголь |
| Журнал |
В информационных системах почти никогда не допускаются пустые строки как результат запроса. Если что-то, что было указано в запросе, отстустствует, то в результирующей строке должно быть указано 0, если речь идёт о количестве, либо "Отсутствует", если требуется текстовый ответ, либо другой подходящий по типу данных результат.
Пример 2. Вновь работаем с таблицей BOOKINUSE базы данных библиотеки. Требуется вывести количество изданий определённого автора, находящихся в выдаче. В таблице видим, что в выдаче находится одна книга Пушкина. Проверяем. Пишем следующий запрос с использованием функции COALESCE:
SELECT COALESCE (( SELECT COUNT (*) FROM Bookinuse WHERE Author='Пушкин'), 0) AS InUseРезультат выполнения этого запроса:
Но среди выданных изданий отстутствуют книги Булгакова. Проверяем. Пишем аналогичный запрос, меняем лишь автора:
SELECT COALESCE (( SELECT COUNT (*) FROM Bookinuse WHERE Author='Булгаков'), 0) AS InUseРезультат выполнения этого запроса:
Таким образом, функция COALESCE вернула первое не-NULL значение: 0 и вместо пустой строки мы получили строку со значением 0.
COALESCE для выбора альтернативы
Нередко некоторое результирующее значение основывается в зависимости от случая на значениях разных столбцов таблицы. Тогда, как правило, не участвующие в формировании результирующего значения значения столбца пусты. Для выбора необходимого столбца применяется функция COALESCE.
Пример 3. В базе данных фирмы есть таблица STAFF, по данным которой можно вычислить годовой доход сотрудника.
| ID | LName | Salary | Comm | Sales |
| 1 | Johnson | 12300 | NULL | NULL |
| 2 | Brown | NULL | 600 | 24 |
| 3 | MacGregor | 1420 | NULL | NULL |
| 4 | Calvin | NULL | 780 | 18 |
| 5 | Levy | 11400 | NULL | NULL |
| 6 | Right | NULL | 800 | NULL |
Если сотрудник получает фиксированную заработную плату (Salary), то значения столбцов Комиссионные (Comm) и Сделки (Sales) пусты (NULL). В таком случае для получения годового дохода следует размер заработной платы умножить на 12. Если же сотрудик получает комиссионные, то значение столбца Salary пусто (NULL). Возможны также случаи, когда сотруднику назначены комиссионные, но он не провёл ни одной сделки. Тогда значение столбца Sales пусто (NULL). В первом случае функция COALESCE возвращает значение Salary*12, во втором - Comm*Sales, в третьем - 0. Итак, для вычисления годового дохода сотрудников пишем следующий запрос с использованием функции COALESCE:
SELECT LName, COALESCE (Salary*12, Comm*Sales, 0) AS Income FROM STAFFРезультатом выполнения запроса будет следующая таблица:
| LName | Income |
| Johnson | 147600 |
| Brown | 14400 |
| MacGregor | 170400 |
| Calvin | 14040 |
| Levy | 136800 |
| Right | 0 |
COALESCE помогает избежать неопределённости в вычислениях
В соединениях таблиц часто невозможно заранее предположить, всем ли значениям некоторого столбца из одной таблице соответствует определённое значение из другой таблице. В случае несоответствия значение является неопределённым (NULL). Но именно на основании этого значения должны производиться дополнительные вычисления. Другая причина, по которой в сложных вычислениях часто применяется функция COALESCE, состоит в том, что запрещается применять агрегатные функции от агрегатной функции, например, SUM(COUNT(*).
Работаем с базой данных "Театр". Таблица Play содержит данные о постановках. Таблица Team - о ролях актёров. Таблица Actor - об актёрах. Таблица Director - о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).
Пример 4. В таблице Team есть столбец MainTeam, содержащий данные о том, является ли роль главной. Если является, то значение столбца - Y, если нет - N. Требуется вывести список актёров с фамилиями и количеством второстепенных ролей.
Потребуется соединение таблиц. Как мы уже замечали, в соединении таблиц Play (постановка) и Team (роль) некоторые значения столбцов могут быть неопределёнными из-за того, что не у всех актёров в каждой постановке обязательно есть и главые, и второстепенные роли. Кроме того, в качестве количества второстепенных ролей требуется подсчитать сумму (SUM) числа строк (COUNT(*)), соответствующих определённому актёру, в которых указано, что роль является второстепеннной. Но использование вложенных агрегатных функций запрещено. В этом случае пишется запрос с применением функции COALESCE, возвращаемое которой значение уже формально не является значением агрегатной функции:
Вычисляет аргументы по порядку и возвращает текущее значение первого выражения, изначально не вычисленного как NULL . Например, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); возвращает третье значение, так как это первое значение, не равное NULL.
Синтаксис
Аргументы
expression
Выражение любого типа данных.
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Типы возвращаемых данных
Возвращает тип данных аргумента expression с наиболее высоким приоритетом. Если ни одно из выражений не допускает значения NULL, то результат типизируется как не допускающий значения NULL.
Remarks
Если все аргументы имеют значение NULL , COALESCE возвращает NULL . По крайней мере одно из значений NULL должно быть типизированным NULL .
Сравнение COALESCE и CASE
Выражение COALESCE — синтаксический ярлык для выражения CASE . Это означает, что код COALESCE (expression1, . n) переписывается оптимизатором запросов как следующее выражение CASE :
Поэтому входные значения (expression1, expression2, expressionN и т. д.) вычисляются многократно. Выражение значения, содержащее вложенный запрос, считается недетерминированным, и вложенный запрос вычисляется дважды. Этот результат соответствует стандарту SQL. В любом случае могут быть возвращены различные результаты для первого и последующих вычислений.
Например, если выполняется код COALESCE((subquery), 1) , вложенный запрос вычисляется дважды. В результате можно получить различные результаты в зависимости от уровня изоляции запроса. Например, код может вернуть NULL при уровне изоляции READ COMMITTED в многопользовательской среде. Чтобы обеспечить устойчивые результаты, используйте уровень изоляции SNAPSHOT ISOLATION или замените COALESCE функцией ISNULL . Кроме того, можно переписать запрос, чтобы поместить вложенный запрос в подзапрос выборки, как показано в следующем примере:
Сравнение COALESCE и ISNULL
Функция ISNULL и выражение COALESCE имеют аналогичные цели, но могут отличаться поведением.
Так как ISNULL — это функция, она вычисляется только один раз. Как было сказано выше, входные значения для выражения COALESCE могут вычисляться несколько раз.
Различается определение типа данных результирующего выражения. ISNULL использует тип данных первого параметра, COALESCE следует правилам выражения CASE и возвращает тип данных значения с самым высоким приоритетом.
Для ISNULL и COALESCE различается допустимость значения NULL для результирующего выражения. Значение, возвращаемое ISNULL , никогда НЕ БЫВАЕТ нулевым (предполагается, что возвращаемое значение является ненулевым). В то время как функция COALESCE с параметрами, которые не допускают значение NULL, считается имеющей значение NULL . Таким образом, выражения ISNULL(NULL, 1) и COALESCE(NULL, 1) , несмотря на одинаковый синтаксис, имеют разные значения допустимости NULL. Эти выражения отличаются при использовании в вычисляемых столбцах, создании ограничений ключа или детерминировании возвращаемого значения определяемой пользователем скалярной функции для возможности индексации, как показано в приведенном ниже примере.
Проверки для ISNULL и COALESCE также различаются. Например, значение NULL для ISNULL преобразуется в значение int, а для COALESCE необходимо предоставить тип данных.
ISNULL принимает только два параметра. А COALESCE принимает переменное количество параметров.
Примеры
A. Выполнение простого примера
В следующем примере показано, как функция COALESCE выбирает из первого столбца данные, отличные от значения NULL. В этом примере используется база данных AdventureWorks2012.
Б. Выполнение сложного примера
В следующем примере таблица wages включает три столбца с данными о ежегодной заработной плате сотрудников: hourly_wage, salary и commission. Однако служащий получает только один тип выплат. Для определения общей оплаты для всех служащих используйте функцию COALESCE для получения не равных NULL значений столбцов hourly_wage , salary и commission .
В. Простой пример
В приведенном ниже примере показано, как COALESCE выбирает данные из первого столбца, содержащего значение, отличное от NULL. В этом примере предполагается, что таблица Products содержит следующие данные:
Затем выполняется следующий запрос COALESCE:
Обратите внимание на то, что в первой строке значение FirstNotNull равно PN1278 , а не Socks, Mens . Этот параметр принимает такое значение, так как столбец Name в примере не был указан в качестве параметра для COALESCE .
Г. Сложный пример
В приведенном ниже примере COALESCE используется для сравнения значений в трех столбцах и возврата только значения, отличного от NULL, найденного в столбцах.
Summary: in this tutorial, you will learn how to use the Oracle NVL() function to substitute null with a more meaningful alternative.
Introduction to Oracle NVL() function
The Oracle NVL() function allows you to replace null with a more meaningful alternative in the results of a query.
The following shows the syntax of the NVL() function:
The NVL() function accepts two arguments. If e1 evaluates to null, then NVL() function returns e2 . If e1 evaluates to non-null, the NVL() function returns e1 .
The two arguments e1 and e2 can have the same or different data types. If their data types are different, Oracle implicit converts one to the other according to the following rules:
- If the data type of e1 is character, Oracle converts e2 to the data type of e1 before comparing them with null and returns VARCHAR2 in the character set of e1 .
- If the data type of e1 is numeric, Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.
- If Oracle cannot implicitly convert one data type to the other, then it issues an error.
Oracle NVL() function examples
The following example returns 100 because the first argument is not null.
The following example returns N/A because the first argument is null:
See the following orders and employees tables from the sample database:
The following query returns order id and the salesman assigned to each sales order.
In this example, we retrieved all sales order in 2016. If a sales order did not have a value in the salesman_id column, then the first_name is null according to the LEFT JOIN operation. The NVL() function returned the first name of salesman if there was a salesman assigned to the sales order, otherwise, it returned the literal string Not Assigned.
Oracle NVL() and CASE expression
The NVL() function is similar to the CASE expression when it comes to test a value for NULL .
The following function call:
is equivalent to
You can use the CASE expression to rewrite the query that returns order id and salesman as follows:
Oracle NVL() vs. COALESCE()
The COALESCE() function is a generalization of the NVL() function.
The following function:
returns the same result as
However, the COALESCE() function evaluates its argument in order and stops evaluation when it can determine the result i.e., when it can find the first non-NULL argument. This feature is known as short-circuit evaluation. In contrast, the NVL() function evaluates all of its arguments to determine the result.
In this tutorial, you have learned how to use the Oracle NVL() function to substitute null with more meaningful information.
Читайте также: