CODEWITHSUNDEEP
sqloracle-databaseplsql
John Wick
John Wick

Reputation: 745

Using COALESCE vs EXCEPTION when no rows are returned (Oracle)

I am currently running into an error where I receive a 'NO DATA FOUND' error message when I'm running a stored procedure that is defining/selecting into Variables that I have set-up.

I've tried two different methods. One coalescing the column and null (as shown below):

BEGIN
  BEGIN
      SELECT COALESCE (max_value, 0) AS MAX_VALUE
        INTO v_max_v
        FROM table1
       WHERE car_code = rcar;
      --
      SELECT COALESCE (min_value, 0) AS MIN_VALUE
        INTO v_in_v
        FROM table1
       WHERE car_code = rcar;
  END
END;

Another Method is to handle the exception where no rows are returned for EACH variable. 

BEGIN
  BEGIN
      SELECT max_value AS MAX_VALUE
        INTO v_max_v
        FROM table1
       WHERE car_code = rcar;
  EXCEPTION
     WHEN NO_DATA_FOUND
     THEN v_max_v := NULL;
  END
      --
      SELECT min_value AS MIN_VALUE
        INTO v_min_v
        FROM table1
       WHERE car_code = rcar;
  EXCEPTION
     WHEN NO_DATA_FOUND
     THEN v_min_v := NULL;
  END
END;

Which is the more efficient / less costly method? Is there a objective way to check for myself? (explain plans only work on queries i believe)

Thanks in advance.

Upvotes: 0

Views: 695

Answers (1)

Himanshu
Himanshu

Reputation: 3970

NO_DATA_FOUND doesn't simply correspond to whether some value as null or not. It;s rather a default exception type used to notify/deal with if records exist or not.

Your comparison is invalid. The first one is not related to any exception at all: it's simply checking and replacing the value if null, and isn't dealing with any kind of exception. But in the other case the exception block is there but is of no use until the situation arises where no rows exist, not that the data value or column value is null.

In terms of performance the first one is faster though as there's no extra exception code added but you can embed the same also there won't be any difference.

Upvotes: 1

Related Questions