web analytics
Sign In | Register
Welcome, Guest
1 2

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

 Options
Previous Topic Next Topic
@2019-06-14 09:04:21

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;
@2019-06-14 09:08:59

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])
@2019-06-14 09:25:43

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;
@2019-06-14 09:28:31

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

Comments
You must Sign In to comment on this topic.


© 2024 Digcode.com