Взаимодействие с NULL

Взаимодействие с `NULL`

Проверка на NULL - если это имеет значение
Определение, одинаковы ли значения полей
Замещение NULL значением

Этот раздел содержит некоторые практические советы и примеры, которые могут быть использованы вами в вашей повседневной работе с 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.