Oracle PL/SQL: NVL / NVL2 / COALESCE / CASE / IF / DECODE Options

codeling Posts: 1089 Points: 4569
Posted: Friday, June 14, 2019 9:04:21 AM

Always use CASE instead of NVL2 if parameter 2 or 3 of NVL2 is either a function call or a SELECT statement.

The NVL2 function always evaluates all parameters before deciding which one to use. This can be harmful if parameter 2/3 is either a function call or a select statement, as it will be executed regardless of whether parameter 1 contains a NULL value or not. 

--Bad

select nvl2(dummy, 'yes', 'no') from dual;

--Good

select case

             when dummy is null then 'no'

             else 'yes'

from dual;

codeling Posts: 1089 Points: 4569
Posted: Friday, June 14, 2019 9:08:59 AM

The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic used in various languages. The DECODE function decodes expression after comparing it to each search value. If the expression is the same as search, result is returned.

If the default value is omitted, a null value is returned where a search value does not match any of the result values.

DECODE(col|expression, search1, result1 
 [, search2, result2,...,][, default])

codeling Posts: 1089 Points: 4569
Posted: Friday, June 14, 2019 9:25:43 AM

Try to use CASE rather than DECODE.

DECODE is an old function that has been replaced by the easier-to-understand and more common CASE function. Contrary to the DECODE statement CASE may also be used directly within PL/SQL. 
 

--Bad

select decode(dummy, 'A', 1,
                     'B', 2, 
                     'C', 3, 
                     'D', 4,
                     5)
from dual;

--Good

select case dummy
                 when 'A' then 1
                 when 'B' then 2
                 when 'C' then 3
                 when 'D' then 4
                 else 5
            end
from dual;

codeling Posts: 1089 Points: 4569
Posted: Friday, June 14, 2019 9:28:31 AM

Try to use CASE rather than an IF statement with multiple ELSIF paths. IF statements containing multiple ELSIF tend to become complex quickly. 

Users browsing this topic
Guest