Search code examples
exceptionplsqlnvlno-data

Are these snippets equivalents? (NVL vs Exception)

I have a doubt managing the situation of asigning values to variables based in sql statements, when no data is found. First, are these blocks equivalents? and if that is true, is better one of the two?

declare
nonsense number;
begin
  select nvl((select 1 from dual where 1 <> 1),2) into nonsense from dual;
  dbms_output.put_line(nonsense);
end;

declare
nonsense number;
begin
  begin
  select 1 into nonsense from dual where 1<>1;
  exception when no_data_found then
    nonsense := 2;
  end;
  dbms_output.put_line(nonsense);
end;
Solution

  • Short answer YES, long answer, the nvl is faster in this case, the result value if not found is right inside de select, in the exception it first execute the select and AFTER the process it calls the exception. In this case nvl is better because you have a fixed value.

    Always opt for nvl if you have an "else".