Робота з порожніми значеннями

Реляційна модель бази даних являє відсутність даних за допомогою спеціального значення NULL У перекладі на звичайну мову його можна перевести так: Значення не відомо. На практиці такі ситуації виникають, коли дані ще не введені повністю або коли даний стовпець не застосовний до конкретної рядку Фактично NULL являє собою невизначене або пусте значення

Оскільки значення NULL не відомо, чи то і результат будь-якої операції, що включає в себе NULL, не може бути відомий Якщо величина банківського рахунку не відома, а він включений до загального списку стану, то і загальна величина стану не відома Та ж концепція справедлива і в SQL, що й демонструє наступний код Філ шеннн, відомий розробник баз даних, якось сказав: Пусте значення вбиває життя будь-якого іншого . SELECT 1 + NULL

Результатом буде NULL

У звязку з тим що порожні значення надають таке нищівної вплив на вираження, деякі розробники виключають можливість їх появи Вони проектують свої бази даних так, щоб значення NULL в стовпцях не допускалися, при цьому замінюючи їх якими зумовленими значеннями (порожніми рядками, нулями або рядком п / а) Інші розробники вважають, що заміна порожніх значень нулями або іншими замінниками тільки для спрощення програмування неправомочна Особисто я належу до другого табору Порожні значення в базах даних мають свою цінність, так як інформує

ють про стан даних Таким чином, зявляється можливість написати програму, яка перевіряє базу даних на наявність порожніх значень і обробляє знайдені похибки відповідним чином

Перевірка на порожні значення

Так як значення NULL не відомо, чи одне значення NULL не може дорівнювати іншому значенню NULL Повертаючись до прикладу з банківськими рахунками, припустимо, що величина рахунку 123 не відома і величина рахунки 234 також не відома Так яким же чином можна стверджувати, що стану цих рахунків рівні Оскільки оператор рівності (=) не застосовний до порожнім значенням, в мові SQL введено спеціальний оператор IS, який використовується для тестування на рівність спеціальним значенням Наприклад:

WHERE вираз IS NULL

Умова IS NULL використовується для тестування на порожні значення

IF NULL = NULL SELECT =

ELSE

SELECT •=

Результатом виконання цього коду буде ! = .

У тій же ситуації оператор IS поводиться по-іншому:

IF NULL IS NULL SELECT IS

ELSE

SELECT ’IS NOT

Результат даного виразу – IS.

Оператор порівняння IS може використовуватися в реченні WHERE інструкції SELECT для відбору рядків, що містять порожні значення Більшість клієнтів компанії Cape Hatteras Adventures не мають в базі даних псевдонімів Наступний запит поверне тільки тих клієнтів, у яких в стовпці Nickname (псевдонім) міститься пусте значення:

USE СНА2

SELECT FirstName, LastName, Nickname FROM dboCustomer WHERE Nickname IS NULL ORDER BY LastName, FirstName

Результат буде наступним:

FirstName LastName                                  Nickname

Debbie      Andrews                                     NULL

Dave          Bettys                                         NULL

Jay            Brown                                          NULL

Lauren      Davis                                           NULL

Оператор IS можна комбінувати з оператором NOT для тестування на наявність значення Наприклад, для відбору тільки тих клієнтів, у яких є псевдоніми, можна використовувати умова Nickname IS NOT NULL:

SELECT FirstName, LastName, Nickname FROM dboCustomer WHERE Nickname IS NOT NULL ORDER BY LastName, FirstName

Результат буде наступним:

FirstName LastName                                 Nickname

Joe            Adams                                                 Slim

Melissa    Anderson                                        Missy

Frank       Goldberg                                     Frankie

Raymond                                                       Johnson                      Ray

Обробка порожніх значень

Коли дані відбираються для звітів, кінцевих користувачів або додатків, порожні значення рідко вітаються Часто пусте значення потрібно перетворити в деякий допустимий, щоб дані можна було зрозуміти або щоб вираз мало результат

Порожні значення потребують спеціальної обробки при використанні у виразах, і мова SQL містить ряд функцій, спеціально призначених для роботи з порожніми значеннями Функції Isnull () і coalesce () перетворять порожні значення в придатні для використання, а функція null if () створює пусте значення, якщо виконується певна умова

SQL Server при роботі з булевими виразами використовує логіку з трьох станів Так, порівняння значення true з NULL дає NULL

Використання функції isNuii ()

Найбільш часто використовуваної функцією, призначеної для роботи з порожніми значеннями, є IsNull (), яка насправді відрізняється від умови IS NULL Ця функція як аргумент приймає один вираз або стовпець, а також підстановлювальний значення Якщо перший аргумент є допустимим значенням (тобто не порожнім), ця функція повертає його Однак якщо перший аргумент являє собою порожнє значення, то повертається значення другого аргументу Загальний синтаксис функції наступний:

IsNull {ісходное_вираженіе, замещающее_значеніе)

Функція Isnull () рівносильна наступному оператору CASE:

CASE

WHEN source_expression IS NULL THEN replacement_value ELSE source_expression END AS ISNULL

Наступний приклад побудований на попередніх запитах він підставляє рядок попі замість порожнього значення там, де клієнти не мають псевдоніма:

SELECT FirstName, LastName, ISNULL(Nickname, 1 none)

FROM Customer

ORDER BY LastName, FirstName

Результат виконання інструкції наступний:

FirstName LastName                                 Nickname

Joe            Adams                                                 Slim

Melissa    Anderson                                        Missy

Debbie     Andrews                                            none

Dave         Bettys                                          none

Якщо рядок у стовпці Nickname має певне значення, то воно передається через функцію isnull () недоторканим Однак якщо значення цього стовпця пусте, то воно обробляється функцією Isnull () і перетвориться в рядок 1 попі .

Функції isnull () і null if Про специфічні для мови Т-SQL і не входять в стандарт ANSI SQL

Функція Coalesce ()

Ця функція використовується досить рідко, можливо, тому, що вона мало кому відома У той же час це досить корисна функція Вона приймає список виразів або стовпців і повертає перше значення, яке виявиться не порожнім Її загальний синтаксис наступний: Coalesce {вираз, вираз, ..)

Функція Coalesce () отримала свою назву з комбінації латинських слів зі і alesce, що значить рух до загального кінця або спільний ріст Ключове слово в SQL сталося від альтернативного значення – Вирости з комбінації різних елементів. У цьому сенсі функція coalesce () зводить разом кілька різних значень з невідомою ступенем корисності і витягує з них одне допустиме значення

Функціонально вона є аналогом наступного оператора CASE:

CASE

WHEN вираз 1 IS NOT NULL THEN вираз

WHEN вираженіе2 IS NOT NULL THEN вираженіе2 WHEN вираз3 IS NOT NULL THEN вираз3

END AS COLEASCE

У наступному прикладі продемонстровано функція coalesce (), що повертає перший непорожнє значення (в даному випадку це 1 +2):

SELECT Coalesce(NULL, 1+NULL, 1+2, abc)

Результатом буде число 3

Функція coalesce () ідеально підходить для злиття розрізнених даних Наприклад, якщо в таблиці в різних стовпцях містяться частини одного цілого, то ця функція допоможе зібрати їх воєдино В одному проекті, над яким я працював, клієнт хотів зібрати назви і адреси контактів з декількох баз даних і додатків в одну таблицю Контактні особи і назви компаній містилися в коректних стовпцях, однак адреси були розкидані по стовпцях Addressl, Address2 і Address3 Деякі рядки мали другу частину адреси в стовпці Address2 Якщо в якому-небудь з адресних стовпців містився адресу, то в стовпці SalesNotes дійсно знаходилося примітка Однак у багатьох випадках сама адреса знаходився в стовпці SalesNotes Наступний код допоміг зібрати адреси з усього цього місива: SELECT Coalesce (

Addressl + str(13)+str(10) + Adress2,

Addressl,

Address2,

Address3,

SalesNote) AS NewAddress FROM TempSalesContacts

У кожному рядку таблиці TempSalesContact функція coalesce () виконує пошук в перерахованих стовпцях і повертає перший непорожнє значення Перший вираз повер-

щает значення тільки в тому випадку, якщо і Addressl, і Address2 містять непорожні значення, оскільки конкатенація з порожнім значенням дає в результаті також порожнє значення Таким чином, якщо існує дворядовий адресу, він буде повернутий в іншому випадку повертає адресу з одного з стовпців, Addressl, Address2 або Address3 Якщо жодна з умов не виконано, буде повернуто адресу з шпальти SalesNotes Природно, результат, отриманий з такої складної таблиці, потрібно перевірити вручну

Ви можете не використовувати функцію coalesce щодня, проте її корисно мати в арсеналі своїх інструментів

Функція Nullif ()

Іноді пусте значення потрібно створити на місці його замінює сурогатного Якщо база даних заповнена значеннями п / а, – або порожніми рядками там, де повинні знаходитися порожні значення, ви можете скористатися функцією nullif () і розчистити базу даних

Функція nullif () приймає два аргументи Якщо вони рівні, то повертається порожнє значення, в іншому випадку повертається перший параметр Функціонально nullif () є аналогом наступного оператора CASE:

CASE

WHEN Expressionl = Expression2 THEN NULL ELSE Expressionl END AS NULLIF

Наступний фрагмент коду перетворює всі прогалини в стовпці Nickname у порожні значення Перша інструкція, заносить в одну з рядків прогалини, створена тільки в цілях тестування:

UPDATE Customer

SET Nickname = 1

WHERE LastName = Adams

SELECT LastName, FirstName,

CASE NickName WHEN ‘ THEN blank

ELSE Nickname END AS Nickname,

Nullif(Nickname, ‘) as NicknameNullIf FROM dboCustomer

WHERE LastName IN (Adams, Anderson, Andrews)

ORDER BY LastName, FirstName

Результат буде наступним:

LastName FirstName Nickname NicknameNullIf

Adams                             Joe                     blank               NULL

Anderson Melissa           Missy                 Missy

Andrews                          Debbie               NULL             NULL

Третій стовпець використовує операцію CASE для представлення прогалин як рядки blank, після чого функція nullif () перетворює його в порожнє значення четвертого стовпця Наступні рядки демонструють інші ситуації Псевдонім Мелісси (Melissa) був залишений функцією nullif () без змін, а у Деббі (Debbie) порожнє значення так і залишилося порожнім

Нестандартне поводження з порожніми значеннями

До цих пір ми обговорювали тільки обробку порожніх значень, прийняту в SQL Server за замовчуванням Однак SQL Server – найвищою мірою гнучка СУБД, і режим роботи з порожніми значеннями можна також змінити

Чисто логічно конкатенація з порожнім значенням повинна в результаті дати пусте значення Однак такий режим можна змінити Параметр підключення concat_null_yields_ null визначає результат конкатенації з порожнім значенням Цей параметр підключення спочатку визначений однойменною замовчанням у базі даних Зміна режиму поводження з порожнім значенням важко перевірити, оскільки інструмент Query Analyzer також має прийнятий за замовчуванням набір параметрів підключення, застосовуваних до кожного нового підключенню

Наступний фрагмент коду встановлює даний параметр бази даних, змінюючи режим, прийнятий за умовчанням:

– Встановлення параметрів бази даних

sp_dboption СНА2# CONCAT_NULL_YIELDS_NULL/ false

– Перевіряємо параметри

SELECT DATABASEPROPERTYEX (СНА2# IsNullConcat)

В результаті буде отримано значення 0

Встановлюємо параметр підключення:

SET CONCAT_NULL_YIELDS_NULL OFF

Тепер виконуємо конкатенацію з порожнім значенням:

SELECT NULL + abc

Результатом операції буде рядок abc.

За звичайних умов стандарт ANSI SQL (так само як і SQL Server) припускає, що порівняння з порожнім значенням дає в результаті також порожнє значення Наприклад, результатом виразу (1> NULL) буде значення NULL Однак такий режим можна змінити за допомогою оператора ANSI NULLS OFF Найбільшим ефектом від такої зміни буде те, що порожні значення можуть бути протестовані за допомогою оператора рівності, а не тільки оператора IS

Як і в попередньому випадку з конкатенацією, враховуються параметри підключення У наступному фрагменті встановлюється режим роботи бази даних за замовчуванням, при цьому відключається стандартний режим ANSI:

– Встановлення параметрів бази даних sp_dboption СНА2, ANSI_NULLS, false

– Перевірка встановлених параметрів

SELECT DATABASEPROPERTYEX (СНА2# IsAnsiNulIsEnabled)

Результатом цього виразу буде 0

Встановлюємо режим роботи з порожнім значенням:

SET ANSI_NULLS OFF

Тепер перевіряємо операцію порівняння двох порожніх значень:

SELECT true WHERE (NULL=NULL)

Результатом буде рядок true.

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


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

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*