Троичная логика, конструкция WITH, функции nvl, coalesce, case, decode.

В Oracle для неопределенного/пустого значения в полях используется NULL. NULL может быть и результатом логического выражения. Например NULL-ом будет результат сравнения f1>1, где f1 принимает значение NULL. В Oracle имеет место троичная логика:

TRUE AND TRUE -> TRUE

TRUE AND FALSE -> FALSE

FALSE AND FALSE -> FALSE

TRUE AND NULL -> NULL

FALSE AND NULL -> FALSE

TRUE OR TRUE -> TRUE

TRUE OR FALSE -> TRUE

FALSE OR FALSE -> FALSE

TRUE OR NULL -> TRUE

FALSE OR NULL -> NULL

Подзапросы в Oracle можно именовать с помощью конструкции WITH и выносить в начало запроса (можно писать и несколько WITH через запятую).

Этот запрос демонстрирует использование функций nvl, coalesce, case, decode:

with a as

(select null f1, null f2, 1 f3, 2 f4, 3 f5 from dual)

select nvl(f1, f3), -- выдать первый аргумент, а, если он IS NULL, то второй аргумент

nvl(f4, f5),

coalesce(f1, f2, f3, f4), -- выдать первый NOT NULL аргумент из списка

case

when f2 is null then

f3

when f4 = 2 then

f5

else

end, -- выдача результата по первому совпавшему условию

decode(f1, null, 7, 8, 9, 0) -- упрощенная запись case when f1 is null then 7 when f1 = 8 then 9 else 0 end

from a;

Отметим здесь, что в oracle NULL служит одновременно результатом логического выражения и значением пустого поля.

Запросы по поиску пустых значений строкового поля

select * from tab1 where field1 = '';

и

select * from tab1 where field1 is null;

дадут одинаковые результаты.

Если null участвует в арифметическом выражении, то результатом выражения становится null. Запрос

select 1+null, 1-null, 1*null, 1/null from dual;

в качестве результата выдаст 4 null-а.

Поэтому, если в арифметическом выражении участвует поле, которое может принимать значения NULL, надо не забывать обёртывать его функцией nvl().

В то же время в агрегатных и аналитических (о них будет глава) функциях NULL игнорируется, в частности sum() воспринимает его как 0. Убедимся в этом на примере запроса:

with smpl as

(select 1 a

from dual

union all

select 2 a

from dual

union all

select null a from dual)

select min(a), max(a), avg(a), sum(a) from smpl;

Для указания порядка NULL в результате сортировки order by существуют опции nulls first и nulls last:

with smpl as

(select 1 a

from dual

union all

select 2 a

from dual

union all

select null a from dual)

select a from smpl order by a nulls first;

with smpl as

(select 1 a

from dual

union all

select 2 a

from dual

union all

select null a from dual)

select a from smpl order by a nulls last;