Firebird взаимодействие с NULL
Проверка на NULL - если это имеет значение
Достаточно часто вам нет необходимости принимать специальные меры для полей или переменных, которые могут быть NULL. Например, если вы делаете так:
select * from Customers where Town = 'Ralston'
|
очевидно, что вы не хотите видеть клиентов, город которых не указан. Аналогично:
if (Age >= 18) then CanVote = 'Yes'
|
не будут включены люди с неизвестным возрастом, что так же верно и безопасно. Но:
if (Age >= 18) then CanVote = 'Yes';
else CanVote = 'No';
|
выглядит менее обоснованным: если вы не знаете возраст человека, вы не можете явно лишить его права голоса. Хуже того:
if (Age < 18) then CanVote = 'No';
else CanVote = 'Yes';
|
не принесет ожидаемый результат, как и в предыдущем случае. Если кто-то с возрастом NULL имеет реальный возраст до 18, вы предоставите несовершеннолетнему право голоса!
Правильным подходом является явная проверка на NULL:
if (Age is null) then CanVote = 'Unsure';
else
if (Age >= 18) then CanVote = 'Yes';
else CanVote = 'No';
|
Замечание
else всегда ссылается на последний if в этом же блоке. Но чаще всего лучше предотвращать путаницу, помещая ключевые слова begin...end вокруг группы строк. Я не сделал этого здесь, чтобы сократить количество строк. И поэтому я был вынужден добавить это примечание. ;-)
Определение, одинаковы ли значения полей
Иногда вы хотите определить, являются ли значения двух полей или двух переменных одинаковыми, и вы будете считать их равными, если они оба NULL. Правильной проверкой для этого является следующая:
if (A = B or A is null and B is null) then...
|
или, если вы хотите убрать возможное недопонимание:
if ((A = B) or (A is null and B is null)) then...
|
Предупреждение. Если только одна из величин A и B является NULL, тестовое выражение станет NULL, а не false! Это нормально для оператора if, и мы даже можем добавить предложение else, которое будет выполнено, если A и B не равны (включая случай, когда одна из величин NULL, а другая - нет):
if (A = B or A is null and B is null)
then ...код для выполнения, если A равно B...
else ...код для выполнения, если A и B различаются... |
Однако откажитесь от идеи инвертирования выражения и использования его как проверки на неэквивалентность (как я это однажды сделал):
/* Не делайте так! */ if (not(A = B or A is null and B is null))
then ...код для выполнения, если A отличается от B...
|
Приведенный выше код работает корректно, если оба A и B являются NULL или оба не являются NULL. Но в нем не выполняется предложение then, если только одна из частей (A или B) является NULL.
Если вы хотите выполнять что-либо, когда A и B отличаются, вы должны либо использовать корректное выражение, приведенное выше, и поместить пустой оператор в предложение then, или использовать это более длинное выражение для проверки:
/* Это корректный тест на неэквивалентность: */
if (A <> B or A is null and B is not null
or A is not null and B is null) then...
|
Определение изменения значения поля
В триггерах часто бывает полезным знать, что значение определенного поля изменилось (включая переход от NULL к не-NULL значению и наоборот) или осталось тем же самым. Это не что иное, как особый случай использования проверки на (не)эквивалентность значений двух полей. Просто вместо A и B используйте New.ИмяПоля и Old.ИмяПоля:
if (New.Job = Old.Job or New.Job is null and Old.Job is null)
then ...поле Job осталось тем же...
else ...поле Job изменилось...
|
Замещение NULL значением
Функция COALESCE
В Firebird 1.5 есть функция, которая может конвертировать NULL во что-то еще. Это позволит вам выполнять преобразование «на лету» и использовать результат в последующей обработке без необходимости использования конструкции «if (MyExpression is null) then». Функция называется COALESCE, и вы можете вызвать ее так:
COALESCE(Expr1, Expr2, Expr3, ...)
|
COALESCE возвращает первое не-NULL выражение из списка аргументов. Если все выражения являются NULL, она вернет NULL.
Вот как вы можете сконструировать полное имя человека с помощью COALESCE из первого, среднего и последнего имени, предполагая, что среднее имя может иметь значение NULL:
select FirstName
|| coalesce(' ' || MiddleName, '')
|| ' ' || Lastname
from Persons
|
Или для создания наиболее информативного имени, насколько это возможно, из таблицы, которая содержит псевдонимы, и в предположении, что и псевдоним и первое имя могут быть NULL:
select coalesce (Nickname, FirstName, 'Mr./Mrs.')
|| ' ' || Lastname
from OtherPersons
|
COALESCE поможет вам только в тех ситуациях, когда NULL можно трактовать одинаково, как некоторое допустимое значение для типа данных. Если для NULL необходима специальная обработка, как в примере с «правом голоса», показанном ранее, вы можете использовать только выражение «if (MyExpression is null) then».
Firebird 1.0: функции *NVL
В СУБД Firebird 1.0 не существует функции COALESCE. Однако, вы можете использовать четыре UDF, которые предоставляют большую часть функциональности функции COALESCE. Эти UDF расположены в библиотеке fbudf и называются
- iNVL для целочисленных аргументов
- i64NVL для аргументов типа bigint
- dNVL для аргументов типа double precision
- sNVL для строк
Функции *NVL получают два аргумента. По аналогии с COALESCE, они возвращают первый аргумент, если он не является NULL; в противном случае они возвращают второй аргумент. Пожалуйста, обратите внимание, что это библиотека для Firebird 1.0 fbudf - и поэтому функции *NVL доступны только для Windows.