Development and Programming
Oracle PL/SQL: NVL / NVL2 / COALESCE / CASE / IF / DECODE
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.
select nvl2(dummy, 'yes', 'no') from dual;
when dummy is null then 'no'
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])
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.
select decode(dummy, 'A', 1,
select case dummy
when 'A' then 1
when 'B' then 2
when 'C' then 3
when 'D' then 4
Try to use CASE rather than an IF statement with multiple ELSIF paths. IF statements containing multiple ELSIF tend to become complex quickly.
© 2019 Digcode.com. All rights reserved.