Что такое nvl в sql

Введение в Oracle 10g. Часть 4

Введение

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

Функция 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 будет заменено на ноль. Обратите внимание на то, что в случае формирования значения при помощи функции ему назначается псевдоним. Результаты запроса будут иметь вид:

Числовые функции

В Oracle предусмотрен ряд встроенных функций для работы с числами. Их не очень много, поэтому рассмотрим их все.

Функция ABS(n)

Функция ABS возвращает абсолютное значение числа. Например:

SELECT ABS(100) X1, ABS(-100) X2, ABS(-100.2) X3

FROM DUAL

Функция CEIL(n)

Функция CEIL возвращает наименьшее целое, большее или равное переданному в качестве параметра числу n. Например:

FROM DUAL

Функция FLOOR(n)

Функция FLOOR возвращает наибольшее целое, меньшее или равное переданному в качестве параметра числу n. Например:

SELECT FLOOR(100.22) X1, FLOOR(-100.22) X2,

FLOOR(100.99) X3, FLOOR(100.01) 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

Функция ROUND(n [,m])

Функция ROUND возвращает число n, округленное до m знаков после десятичной точки по правилам математического округления. Параметр m может не указываться — в этом случае n округляется до целого.

SELECT ROUND(100.25678) X1, ROUND(100.5) X2, ROUND(100.99) X3, ROUND(100.25678, 2) X4

FROM DUAL

Функция SIGN(n)

SELECT SIGN(100.22) X1, SIGN(-100.22) X2, SIGN(0) X3

FROM DUAL

Функция MOD(n, m)

Функция MOD возвращает от деления n на m. Например:

SELECT MOD(10, 3) X1, MOD(10, 2) X2, MOD(100, 0) X3

FROM DUAL

Интересной особенностью данной функции является возможность передачи m равного нулю — при этом не возникает ошибки деления на 0.

Функция POWER(n, m)

Функция POWER возводит число n в степень m. Степень может быть дробной и отрицательной, что существенно расширяет возможности данной функции.

SELECT POWER(10, 2) X1, POWER(100, 1/2) X2,

FROM DUAL

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

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

FROM DUAL

Попытка передать функции LN отрицательное значение приводит к возникновению ошибки ORA-1428.

Функция LOG(n, m)

Функция LOG производит вычисление логарифма m по основанию n. Пример:

SELECT LOG(2, 8) X1, LOG(10, 100) X2

FROM DUAL

Тригонометрические функции

Oracle поддерживает вычисление основных тригонометрических функций:

SIN(n) — синус n (где n — угол в радианах)

COS(n) — косинус n (где n — угол в радианах)

TAN(n) — тангенс n (где n — угол в радианах)

SINH(n) — гиперболический синус n

COSH(n) — гиперболический косинус n

TANH(n) — гиперболический тангенс n

SELECT SIN(0) X1, COS(0) X2, TAN(0) X3

FROM DUAL

Заключение

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

Источник

Основные функции SQL | NVL, NVL2, DECODE, COALESCE, NULLIF, LNNVL и NANVL

В этой статье мы обсудим некоторые мощные общие функции SQL, а именно: NVL, NVL2, DECODE, COALESCE, NULLIF, LNNVL и NANVL.

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

expr1 — это исходное значение или выражение, которое может содержать ноль.
expr2 — это целевое значение для преобразования нуля.

Выход :

Синтаксис —

expr1 — это исходное значение или выражение, которое может содержать ноль
expr2 — это значение, возвращаемое, если expr1 не является нулевым
expr3 — это значение, возвращаемое, если expr1 равен нулю

Выход :

Синтаксис —

Выход :

Синтаксис —

Выход :

Синтаксис —

Выход :

Синтаксис —

Выход :

Теперь вышеприведенные примеры не учитывают те сотрудники, которые вообще не имеют комиссионных.
Чтобы включить их, мы используем LNNVL ()

Выход :

Синтаксис —

Рассмотрим следующую таблицу с именем nanvl_demo:

Выход :

Ссылка : Введение в Oracle9i SQL (том-1, книга)

Источник

Что такое 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(), все значения/выражения должны быть одного и того же типа или должны быть неявно преобразовываемыми к одному и тому же типу данных.

Источник

Oracle NVL Function

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 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:

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 following function call:

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.

Источник

Oracle / PLSQL: NVL Function

This Oracle tutorial explains how to use the Oracle/PLSQL NVL function with syntax and examples.

Description

The Oracle/PLSQL NVL function lets you substitute a value when a null value is encountered.

Syntax

The syntax for the NVL function in Oracle/PLSQL is:

Parameters or Arguments

Returns

The NVL function returns a substitute value.

Applies To

The NVL function can be used in the following versions of Oracle/PLSQL:

Example

Let’s look at some Oracle NVL function examples and explore how to use the NVL function in Oracle/PLSQL.

The SQL statement above would return ‘n/a’ if the supplier_city field contained a null value. Otherwise, it would return the supplier_city value.

Another example using the NVL function in Oracle/PLSQL is:

This SQL statement would return the supplier_name field if the supplier_desc contained a null value. Otherwise, it would return the supplier_desc.

A final example using the NVL function in Oracle/PLSQL is:

This SQL statement would return 0 if the commission field contained a null value. Otherwise, it would return the commission field.

Frequently Asked Questions

Question: I tried to use the NVL function through VB to access Oracle DB.

I got an oracle error when I use distinct clause with NVL, but when I remove distinct it works fine.

Answer: It is possible to the use the DISTINCT clause with the NVL function. However, the DISTINCT must come before the use of the NVL function. For example:

Question: Is it possible to use the NVL function with more than one column with the same function call? To be clear, if i need to apply this NVL function to more than one column like this:

Answer: You will need to make separate NVL function calls for each column. For example:

Источник