Oracle nvl опис. Перетворення NVL для різних типів даних

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

Функція 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

У деяких випадках під час виклику цієї функції може виникнути виняткова ситуація. Наприклад:

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 буде замінено на нуль. Зверніть увагу, що у разі формування значення за допомогою функції йому призначається псевдонім. Результати запиту матимуть вигляд:

Функція 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

У деяких випадках під час виклику цієї функції може виникнути виняткова ситуація. Наприклад:

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

Функція TO_CHAR із числами

Функції перетворення даних на інші типи даних. TO_CHAR(число) перетворює число на текст. TO_NUMBER(рядок) перетворює текст на число.

SELECT TO_CHAR (123) FROM DUAL поверне рядок 123, SELECT TO_NUMBER (`12345") FROM DUAL поверне число 12345.

Лабораторна робота. Зміна формату виведених чисел

Зміна формату числових значень Oracle SQL, функція TO_CHAR для роботи з числовими значеннями.

Завдання:

Напишіть запит, який би виводив інформацію про ім'я, прізвище та зарплату співробітників з таблиці hr.employees у форматі, представленому на рис. 3.4-1:

Рис. 3.4 -1

При цьому дані мають бути відсортовані таким чином, щоб першими виводилися рядки для працівників із найбільшою зарплатою.

Примітка:

Деякі значення зарплати на рис. 3.4-1 було змінено, тому вони можуть не співпадати з вашими значеннями.

Рішення:

SELECT first_name AS "Ім'я", last_name As "Прізвище", TO_CHAR (SALARY, "L999999999.99") As "Оклад" FROM hr.employees ORDER BY SALARY DESC.

Функції TO_NUMBER та TO_DATE

Функція перетворення рядка на дату TO_DATE (рядок, формат). Можливі значення форматів вже розглянуті вище, тому наведу кілька прикладів використання цієї функції. Приклади:

SELECT TO_DATE («01.01.2010", `DD.MM.YYYY") FROM DUAL поверне дату `01.01.2010";

SELECT TO_DATE («01.JAN.2010", `DD.MON.YYYY") FROM DUAL поверне дату `01.01.2009";

SELECT TO_DATE («15-01-10", `DD-MM-YY") FROM DUAL поверне дату `15.01.2010".

Функція перетворення рядка на числове значення TO_NUMBER (рядок, формат). Найпоширеніші значення форматів перелічені у таблиці, тому розглянемо застосування цієї функції на прикладах. Приклади:

SELECT TO_NUMBER (`100") FROM DUAL поверне число 100 SELECT TO_NUMBER (`0010.01", "9999D99") FROM DUAL поверне число 10.01;

SELECT TO_NUMBER ("500,000", "999G999") FROM DUAL поверне число 500 000.

Елемент RR у форматі дати

Елемент формату дати та часу RR схожий на елемент формату YY дати та часу, але це забезпечує додаткову гнучкість для зберігання значень дати та інших століть. Елемент формату RR дати та часу дозволяє зберігати дати 20 століття в 21 столітті, вказавши тільки дві останні цифри року.

Якщо дві останні цифри поточного року є 00 до 49, то рік, що повертається, має ті ж перші дві цифри, як у поточному році.

Якщо дві останні цифри поточного року від 50 до 99, то перші 2 цифри поверненого року є 1 більшою, ніж у перші 2 цифри поточного року.

Якщо дві останні цифри поточного року є 00 до 49, то перші 2 цифри поверненого року є 1 меншою за перші 2 цифри поточного року.

Якщо дві останні цифри поточного року від 50 до 99, то рік, що повертається, має ті ж перші дві цифри, як у поточному році.

Функція NVL

Функція NVL, зазвичай, застосовується найчастіше. Функція отримує два параметри: NVL (expr1, ехрг2). Якщо перший параметр expr1 не дорівнює NULL, функція повертає його значення. Якщо перший параметр NULL, замість нього функція повертає значення другого параметра ехрг2.

Приклад: Виберіть NVL (supplier_city, н / а ") від постачальників:

У заяві SQL вище повернеться н/", якщо поле supplier_city містить нульове значення. В іншому випадку він поверне значення supplier_city.

Інший приклад використання функції NVL в Oracle/PLSQL є:

виберіть supplier_id, NVL (supplier_desc, supplier_name) від постачальників.

Цю SQL заяву буде повернути supplier_nameполе, якщо supplier_descміститься нульове значення. Інакше він поверне supplier_desc.

Останній приклад: використовуючи функцію NVL в Oracle / PLSQL є: оберіть NVL (комісія, 0) від продажів;

Ця SQL заява повернула значення 0, якщо комісіяполе міститься нульове значення. Інакше було б повернути комісіїполе.

Перетворення NVL для різних типів даних

Для перетворення невизначеного значення на фактичне використовується функція NVL: NVL ( вираз1, вираз 2), де:

вираз1-Вихідне чи обчислене значення, що може бути невизначеним.

вираз2- значення, яке підставляється замість невизначеного значення.

Примітка:Функцію NVL можна використовуватиме перетворення будь-якого типу даних, але результат завжди буде такого самого типу, як і вираз1.

Перетворення NVL для різних типів:

NUMBER - NVL (числовий стовпець, 9).

CHAR або VARCHAR2 - NVL (Символи | стовпець,"Недоступно").

Лабораторна робота. Застосування функції NVL

Функція NVL для роботи з невизначеними значеннями Oracle SQL.

Завдання:

Напишіть запит, який виводить інформацію про ім'я та прізвище співробітників з таблиці hr.employees., а також ставку комісії (стовпець COMMISSION_PCT) для співробітника. При цьому для тих співробітників, для яких комісія не визначена, потрібно вивести значення 0. Результат виконання запиту має бути таким, як на рис. 3.5-1.

Рис. 3.5 -1 (показані значення починаючи з рядка 51)

Рішення:

Код відповідного запиту може бути таким:

SELECT first_name AS "Ім'я", last_name As "Прізвище", NVL (COMMISSION_PCT, 0) As "Ставка комісії" FROM hr.employees.

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

Вкладені функції

Вкладені функції використовують значення однієї функції, що повертаються, як вхідний параметр для іншої функції. Функції завжди повертають лише одне значення. Тому ви можете розглядати результат виклику функції як значення-літерал, коли використовуєте його як параметр виклику іншої функції. Рядкові функції можуть бути вкладені до рівня вкладеності. Виклик один функції виглядає так

Function1(parameter1, parameter2, …) = result

Заміна параметра функції на виклик іншої функції може призвести до появи виразів

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

Спочатку обчислюються вкладені функції перед тим, як їх результати використовуються як вхідні значення для інших функцій. Функції обчислюються від найглибшого рівня вкладеності до верхнього зліва направо. Попереднє вираз виконується наступним чином

1. Обчислюється функція F3(param1) і значення, що повертається використовується як третій параметр для функції 2, назвемо його param2.3
2. Потім обчислюється функція F2(param1, param2.2, param2.3) і значення, що повертається використовується як другий параметр функції F1 – param1.2
3. І нарешті обчислюється функція F1(param1, param2, param1.3) і результат повертається в програму, що викликає.

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

Розглянемо запит

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

1. У цьому запиті три функції від нижнього рівня до верхнього – SYSDATE, LAST_DAY, NEXT_DAY. Запит виконується в такий спосіб
2. Виконується найбільша вкладена функція SYSDATE. Вона повертає поточний системний час. Припустимо, що поточна дата 28 жовтня 2009 року
3. Далі обчислюється результат функція другого рівня LAST_DAY. LAST_DATE('28-OCT-2009') повертає останній день жовтня 2009 року, тобто значення 31 жовтня 2009 року.
4. Потім відбувається віднімання з цієї дати семи днів – виходить 24 жовтня.
5. І нарешті обчислюється функція 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

Функція 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;

Запит один повертає 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. Якщо не було знайдено збігу в жодному наборі параметрів, то повертається або якщоfalse якщо цей параметр був заданий, або NULL.

Усі параметри функції DECODE можуть бути виразами. Тип значення, що повертається, дорівнює типу першого перевіряючого елемента – параметра comp 1. Вираз expr 1 неявно перетворюється на тип даних параметра comp1. Всі інші доступні параметри 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

}