Different Variation of NVL

NVL2 (string1, if_not_null, if_null):

SELECT ename,
       NVL2 (comm, 'Commissionable', 'Non-Commissionable') AS STATUS
  FROM emp;

It is equivalent to

SELECT ename,
       CASE
         WHEN comm IS NOT NULL THEN 'Commissionable'
         ELSE 'Non-Commissionable'
       END AS STATUS
  FROM emp;

SELECT ename,
       DECODE (comm, NULL, 'Non-Commissionable', 'Commissionable') AS STATUS
  FROM emp;

LNNVL (condition):

Returns TRUE if the condition is FALSE or NULL
Returns FALSE if the condition is TRUE

SELECT * FROM emp WHERE LNNVL (comm > 500);

It is equivalent to

SELECT * FROM emp WHERE NVL(comm, 0) <= 500;

NULLIF (A, B):

Return NULL if A = B, else return A

SELECT NULLIF ('Smith', 'Smith'), -- returns NULL
       NULLIF ('Smith', 'SMITH'), -- returns Smith
       NULLIF ('Smith', ''),      -- returns Smith
       NULLIF ('Smith', NULL),    -- returns Smith
       NULLIF ('', 'SMITH'),      -- returns NULL
       NULLIF ('', '')            -- returns NULL
  FROM DUAL;

COALESCE (expr1, expr2, ... expr_n)

Returns the first not null value in the list

SELECT COALESCE( street1, street2, street3 ) address
  FROM customers;

It is equivalent to

IF street1 is not null THEN
   address := street1;
ELSIF address2 is not null THEN
   address := street2;
ELSIF address3 is not null THEN
   address := street3;
ELSE
   address := NULL;
END IF;