Nvl oracle что это
This Oracle tutorial explains how to use the Oracle/PLSQL NVL function with syntax and examples.
Числовые функции
В Oracle предусмотрен ряд встроенных функций для работы с числами. Их не очень много, поэтому рассмотрим их все.
Oracle NVL2() overview
The Oracle NVL2() function is an extension of the NVL() function with different options based on whether a NULL value exists.
The Oracle NVL2() function accepts three arguments. If the first argument is not null, then it returns the second argument. In case the second argument is null, then it returns the third argument.
The following illustrates the syntax of the Oracle NVL2() function:
In this syntax, the first argument e1 can be a value of any data type. The second and third arguments can be values of any data types except LONG.
In case the data types of e2 and e3 are different:
- If e2 is numeric, Oracle implicitly converts either e2 or e3 to the data type of the higher precedence and returns a value of that data type.
- If e2 is character data, Oracle converts e3 to the data type of e2 before comparing them and returns VARCHAR2 in the character set of e2. Oracle does not carry this conversion in case e3 is a null constant because it is not necessary.
The following flowchart illustrates how the Oracle NVL2() function works.
Short-circuit evaluation
The COALESCE() function uses short-circuit evaluation. It means that the function stops evaluating the remaining expressions once it finds the first one evaluates to a non-null value.
Consider the following example:
In this example, the COALESCE() function only evaluated the first expression because the result of the first expression was two (1+1). It did not evaluate the second expression (1/0). If it had done so, Oracle would have issued the division by zero error.
Функция 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
Oracle COALESCE() and CASE expression
You can use the COALESCE() function instead of the longer CASE expression when it comes to test for null in multiple expressions. The COALESCE() function is more concise than a CASE expression that involves null evaluations.
If you check for NULL in two expressions, the COALESCE() function is equivalent to the CASE expression.
For example, the following COALESCE() function:
is equivalent to:
is equivalent to
Функция 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 будет заменено на ноль. Обратите внимание на то, что в случае формирования значения при помощи функции ему назначается псевдоним. Результаты запроса будут иметь вид:
B) Oracle NVL2() function with character data type example
The following example returns the second argument which is the ABC string because the first argument is not null.
Функция LOG(n, m)
Функция LOG производит вычисление логарифма m по основанию n. Пример:
SELECT LOG(2, 8) X1, LOG(10, 100) X2
FROM DUAL
C) Oracle NVL2() function with orders example
See the following employees and orders tables from the sample database:
The following query retrieves order id, order date, and the name of the sales employee of sales orders in 2017:
In this example, the NVL2() function checks if the first name is not null, then it returns the full name of the sales employee assigned to the sales order. Otherwise, it returns a literal string Not assigned .
Description
The Oracle/PLSQL NVL function lets you substitute a value when a null value is encountered.
Функция MOD(n, m)
Функция MOD возвращает от деления n на m. Например:
SELECT MOD(10, 3) X1, MOD(10, 2) X2, MOD(100, 0) X3
FROM DUAL
Интересной особенностью данной функции является возможность передачи m равного нулю при этом не возникает ошибки деления на 0.
Oracle NVL2() function examples
Let’s take some examples of using the Oracle NVL2() function to understand how it works.
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.
A) Oracle NVL2() function with numeric data type example
The following statement returns two because the first argument is null.
Oracle COALESCE() vs. NVL()
The COALESCE() function is a part of SQL ANSI-92 standard while NVL() function is Oracle specific.
In case of two expressions, the COALESCE() function and NVL() seems to be similar but their implementations are different. See the following statements:
Both statements return the same result which is one. However, the COALESCE() function only evaluates the first expression to determine the result while the NVL() function evaluates both expressions.
Let’s see the following example:
The statement above returned 1 whereas the following example causes an error:
Because the NVL() function evaluated the second expression 1/0 that causes the error.
In this tutorial, you have learned how to use the Oracle COALESCE() function to return the first non-null expression in a list of expressions.
Заключение
Итак, мы рассмотрели все основные встроенные функции Oracle, предназначенные для работы с числовой информацией. В следующей статье мы будем изучать функции для работы со строками и датами.
1999 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
2000 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
2001 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
2002 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
2003 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
2004 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
2005 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
2006 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
2007 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
2008 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
2009 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
2010 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
2011 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
2012 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
2013 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
В настоящем обзоре мы рассмотрим модель моноблока от компании HP, которая является признанным лидером в производстве компьютеров как для домашнего использования, так и для офисов. Моноблок HP 205 G4 22 — модель нового семейства, которая построена на базе процессоров AMD последнего поколения и отличается неплохой производительностью вкупе с привлекательной ценой
Швейцарская компания Logitech G представила беспроводную игровую мышь Logitech G PRO X Superlight. Новинка предназначена для профессиональных киберспортсменов, а слово Superlight в ее названии указывает на малый вес этой модели, который не превышает 63 г. Это почти на четверть меньше по сравнению с анонсированным пару лет тому назад манипулятором Logitech G PRO Wireless
Как показало недавнее исследование Кембриджского университета — количество людей, которые пользуются сегодня криптовалютами, приближается к размеру населения небольшой страны и это только начало, мир меняется. Поэтому компания ASRock разработала и выпустила в продажу весьма необычную материнскую плату — H110 PRO BTC+, которую мы и рассмотрим в этом обзоре
Компания Rapoo анонсировала в Китае беспроводную клавиатуру Ralemo Pre 5 Fabric Edition. Новинка выполнена в формате TKL (без секции цифровых клавиш) и привлекает внимание оригинальным дизайном. Одна из отличительных особенностей этой модели — верхняя панель, обтянутая тканью с меланжевым рисунком
Линейку компьютерных мониторов MSI пополнила модель Optix MAG301 CR2, адресованная любителям игр. Она оборудована ЖК-панелью типа VA со сверхширокоформатным (21:9) экраном изогнутой формы (радиус закругления — 1,5 м). Его размер — 29,5 дюйма по диагонали, разрешение — 2560×1080 пикселов
Каталог продукции компании SilverStone пополнил комплект MS12. Он позволяет создать портативный накопитель на базе стандартного SSD типоразмера M.2 2280 с интерфейсом PCI Express
Компания ADATA Technology анонсировала твердотельные накопители серии XPG Spectrix S20G. Они предназначены для оснащения игровых ПК и, как утверждают их создатели, сочетают высокую производительность и эффектный внешний вид
Линейку видеоадаптеров ASUS на базе графических процессоров NVIDIA пополнила модель GeForce RTX 3070 Turbo (заводской индекс TURBO-RTX3070-8G), предназначенная для оснащения игровых ПК. Одной из особенностей новинки является конструкция системы охлаждения
КомпьютерПресс использует
Summary: in this tutorial, you will learn how to use the Oracle NVL() function to substitute null with a more meaningful alternative.
Функция 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
В некоторых случаях при вызове данной функции может возникнуть исключительная ситуация. Например:
SELECT POWER(-100, 1/2) X2
FROM DUAL
В данном случае производится попытка вычисления квадратного корня от отрицательного числа, что приведет к возникновению ошибки ORA-01428 «Аргумент вне диапазона».
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:
Hope this helps!
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:
В предыдущей статье мы рассмотрели псевдонимы и псевдостолбцы, а также правила их использования в запросах. В настоящей статье мы продолжим рассмотрение Oracle SQL речь пойдет о базовых встроенных функциях, которые могут применяться в запросах.
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.
Функция FLOOR(n)
Функция FLOOR возвращает наибольшее целое, меньшее или равное переданному в качестве параметра числу n. Например:
SELECT FLOOR(100.22) X1, FLOOR(-100.22) X2,
FLOOR(100.99) X3, FLOOR(100.01) X4
FROM DUAL
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:
Функция SQRT(n)
Данная функция возвращает квадратный корень от числа n. Например:
SELECT SQRT(100) X
FROM DUAL
Introduction to the Oracle COALESCE() function
The Oracle COALESCE() function accepts a list of arguments and returns the first one that evaluates to a non-null value.
The following illustrates the syntax of the Oracle COALESCE() function:
In this syntax, the COALESCE() function returns the first non-null expression in the list. It requires at least two expressions. In case all expressions evaluate to null, the function returns null.
The following example returns one because it is the first non-null argument:
The following example returns null because all arguments are null:
Returns
The NVL function returns a substitute value.
D) Oracle NVL2() function with expression example
First, the following statement creates a compensation table that stores employee’s salary and commission:
Second, we insert some sample data into the compensations table for the demonstration:
The following query calculates the total compensations of employees from the beginning of 2017:
In this example, we used the NVL2() function to implement a logic that if the commission IS NOT NULL, then the total compensations is just salary. In case the commission IS NULL, calculate the full payment as salary plus commission.
Функция ABS(n)
Функция ABS возвращает абсолютное значение числа. Например:
SELECT ABS(100) X1, ABS(-100) X2, ABS(-100.2) X3
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.
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.
Summary: in this tutorial, you will learn how to use the Oracle NVL2() function to substitute a null value with different options.
Return type
If all arguments have the same data type, the COALESCE() function returns a value of that data type.
If the arguments have different data types, the COALESCE() function implicitly converts all arguments to the data type of the first non-null argument. In case the conversion fails, then Oracle issues an error.
The following example returns a value with character type because all arguments are characters.
Similarly, the following example returns a value with numeric type:
However, the following example uses arguments of different types for the COALESCE() function:
Oracle issued the following error:
Because the COALESCE() function tried to convert the character type of the third argument to a numeric type of the second argument that resulted in an error.
Applies To
The NVL function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Parameters or Arguments
Функция CEIL(n)
Функция CEIL возвращает наименьшее целое, большее или равное переданному в качестве параметра числу n. Например:
SELECT CEIL(100) X1, CEIL(-100) X2, CEIL(100.2) X3 , CEIL(-100.2) X4
FROM DUAL
Oracle COALESCE() example
Let’s set up a sample table for testing.
Suppose you have to record emergency contacts of employees with various phone numbers: work phone, home phone, and cell phone. To do it, you create a new table named emergency_contacts as follows:
The following statements insert some emergency contacts into the table:
Some contacts have only work phone while others may have a home phone and cell phone or don’t have any phone number at all.
The following query retrieves the employees and their emergency contacts with phone numbers:
In this example, we used the COALESCE() function to select home phone, work phone, or cell phone if each is available. In case no phone numbers are available, we returned N/A literal string which stands for not available.
The following shows the result of the query:
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 поддерживает вычисление основных тригонометрических функций:
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
Функция SIGN(n)
Функция SIGN определяет знак числа. Если n положительное, то функция возвращает 1. Если отрицательное возвращается -1. Если равно нулю, то возвращается 0. Например:
SELECT SIGN(100.22) X1, SIGN(-100.22) X2, SIGN(0) X3
FROM DUAL
Oracle NVL2() function and CASE expression
The NVL2() function is logically equivalent to the following CASE expression:
As you can see, the NVL2() function is more concise and less verbose.
In this tutorial, you have learned how to use the Oracle NVL2() function to substitute a null value with different options.
Summary: in this tutorial, you will learn how to use the Oracle COALESCE() function to return the first non-null arguments in a list.
Функция 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
Syntax
The syntax for the NVL function in Oracle/PLSQL is:
Читайте также: