Главная » Microsoft SQL Server, Базы данных » Работа с пустыми значениями

0

Реляционная модель базы данных представляет отсутствие данных с помощью специального значения 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 dbo.Customer 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 dbo.Customer 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 dbo.Customer

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 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии