Something About CASE, NVL And DECODE

Sometimes the programming language constructions which seem to be well known and usual, can behave in odd way. Here is the PL/SQL function for the simpliest example of the situation:

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

As we can see, this function just returns the number 5 and outputs some string to standard buffer. So, here is an example of function which not only returns the result, but also performs some action, in other words it has some side effect. We need this quality to trace the function executons.

Let's try to guess the result of the following code:

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

At first glance, the buffer has to be empty, because the p variable is not null, so the function get_number_five doesn't seem to be called. But it is called. Function nvl is the casual subroutine without any exceptions. Thus, at the very beginning, all the parameters are calculated and then body of the function works returning necessary result. If we write our own PL/SQL function and put inside it the logic of nvl, the result will be the same.

We can face it in practise, for example, working with Oracle sequences. If we put seq.nextval in the nvl, counting on using it in case of absence some other value (which is the first parameter of nvl), we can get huge gaps between the numbers, because each time our sequence is not really used, it generates the new value anyway.

Do all the construction behave in the same way? Not at all.

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;

In these two cases the standard output will be empty. Function decode is equal to branch operator. Function coalesce works in the same way. Although, it is said that in versions of Oracle earlier than 10g it worked as nvl.

So, we have to be more attentive while using nvl if the parameters are high-loaded functions or complicated subqueries. May be it is meaningful to just change the function with coalesce.
Also we can write:

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

This variant can look kind of cumbersome, but the consequence of actions is explicitly stated and don't cause any doubts.