Are these snippets equivalents? (NVL vs Exception)

Question

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;

score 2 · Accepted Answer · answered Oct 11 '12 at 13:07

2

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".

answered Oct 11 '12 at 13:07

Toping

754
5
16

If you replace the query on dual with any sort of *useful* query, the difference in execution time becomes negligible. – Vincent Malgrat Oct 11 '12 at 13:16
We dont know how many times and how is gonna be used the function, it IS relevant the execution time.. – Toping Oct 11 '12 at 13:19
Well, this function does absolutely nothing so we can assume that it is never run :) If we assume on the other hand that this select does something useful, then the difference in performance becomes negligible. – Vincent Malgrat Oct 11 '12 at 13:23

Vincent Malgrat · Answer 2 · 2012-10-11T15:18:18.217

Both blocks output 2 so both are "equivalent". I would argue that the second form is more standard and easier to read.

Compare the two functions:

FUNCTION getSal (p_id NUMBER) RETURN NUMBER
IS
   l_return NUMER;
BEGIN
   SELECT sal INTO l_return FROM emp WHERE id = p_id;
   RETURN l_return;
EXCEPTION
   WHEN NO_DATA_FOUND
      THEN RETURN 0;
END;

FUNCTION getSal (p_id NUMBER) RETURN NUMBER
IS
   l_return NUMER;
BEGIN
   SELECT NVL((SELECT sal INTO l_return FROM emp WHERE id = p_id), 
              0)
     INTO l_return
     FROM DUAL;
   RETURN l_return;
END;

While the first one has a few more lines, it is easier to understand that 0 will be returned if the employee is not found. The second one would takes more time to understand and would therefore be more confusing.

I would definitely go for the first one since they will perform the same and readability equals maintainability and is therefore important.

Also note that my two blocks are not fully equivalent. If an employee has a NULL salary, my first query will return NULL whereas my second query will return 0.

Are these snippets equivalents? (NVL vs Exception)

2 Answers2