Немного о case, nvl и decode

Иногда конструкции языка, кажущиеся знакомыми и вполне обычными, могут вести себя несколько неожиданно. Рассмотрим в качестве простейшего примера следующую функцию на PL/SQL:

create or replace function get_number_five
return number
is
begin
  dbms_output.put_line('function get_number_five is invoked');
  return 5;
end;

Как видим, она просто возвращает число 5, сопровождая свою работу выводом строки в буфер. То есть перед нами пример функции, которая не только возвращает требуемый результат, но и выполняет некоторые действия. Сейчас нам это нужно, чтобы определить происходил ли вызов функции.

Попытаемся угадать, что произойдет при следующем вызове:

declare
 x number;
 p number;
begin
  p :=5;
  x := nvl(p,get_number_five);
end;

На первый взгляд, выходной буфер должен оставаться пустым, так как переменная p не равна null, а значит, функция get_number_five вроде и не должна вызываться. Но она вызывается. Функция nvl является самой обыкновенной подпрограммой в прямом смысле этого слова. А именно - сначала вычисляются значения передаваемых параметров, а уж потом отрабатывает ее тело, возвращая нужный результат. Если мы напишем свою функцию на PL/SQL и поместим внутрь логику nvl, то эффект будет тем же.

На практике с этим можно столкнуться, к примеру, при работе с последовательностями Oracle - помещая seq.nextval в функцию nvl, рассчитывая воспользоваться ею при отсутствии какого-то другого значения, можно получить огромные бреши между номерами - ведь всякий раз, когда последовательность не будет востребована, генерация нового элемента всё равно будет происходить.

Все ли конструкции  ведут себя подобным образом? Отнюдь нет.

declare
 x number;
 p number;
begin
  p :=5;
  select decode(p,null, get_number_five, p) into x from dual;
end;

declare
 x number;
 p number;
begin
  p :=5;
  x := coalesce(p,get_number_five);
end;

В этих двух случаях на экране не будет ничего. Функция decode по сути эквивалентна оператору ветвления, похожим же образом работает coalesce(впрочем, утверждают, что до десятой версии СУБД Oracle она работала аналогично nvl).

В этой связи нужно быть внимательнее при использовании nvl в случае если в качестве параметров используются высоконагруженные функции или сложные подзапросы. Возможно, следует просто заменить функцию на coalesce.

С таким же успехом мы можем записать:

declare
 x number;
 p number;
begin
  p :=5;
  x := case
         when p is not null
            then p
            else 
              get_number_five 
         end;
end;

Этот вариант является чуть более громоздким, чем все предыдущие, зато последовательность действий задается явно и не вызывает никаких сомнений.