1 COALESCE
語法：COALESCE(expr1, expr2, ..., exprn) n>=2
作用：COALESCE returns the first non-null expr in the expression list. You must specify at least
two expressions. If all occurrences of expr evaluate to null, then the function returns
null.
COALESCE用於返回第一個非空的表達式.表達式只是兩個.如果所有的表達式為null，則返回null.
COALESCE(expr1, expr2)等價于 CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END
例子：
SELECT product_id, list_price, min_price,
COALESCE(0.9*list_price, min_price, 5) "Sale"
FROM product_information
WHERE supplier_id = 102050
ORDER BY product_id;
2 NVL
語法：NVL（expr1,expr2）
NVL lets you replace null (returned as a blank) with a string in the results of a query. If
expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.
NVL 如果expr1為空，則返回expr2，不為空，就返回expr1.
例子：
SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable') commission
FROM employees
WHERE last_name LIKE 'B%'
ORDER BY last_name;
3 NVL2
語法：NVL2（expr1,expr2,expr3）
作用：NVL2 lets you determine the value returned by a query based on whether a specified
expression is null or not null. If expr1 is not null, then NVL2 returns expr2. If expr1 is
null, then NVL2 returns expr3.
如果expr1的值為空，則返回expr2,不為空則返回expr3.
SELECT last_name, salary,
NVL2(commission_pct, salary + (salary * commission_pct), salary) income
FROM employees
WHERE last_name like 'B%'
ORDER BY last_name;
4 DECODE
語法：DECODE(expr,search1,result1,search2,result2...searchN,resultN,default)
作用：DECODE compares expr to each search value one by one. If expr is equal to a search,
then Oracle Database returns the corresponding result. If no match is found, then
Oracle returns default. If default is omitted, then Oracle returns null.
比較expr和search的值，如果相等，則返回result，否則返回default.
就是說如果expr等於search1，則返回result1的值，等於search2，則返回result2...如果都不滿足就返回default值.
例子：
SELECT product_id,
DECODE (warehouse_id, 1, 'Southlake',
2, 'San Francisco',
3, 'New Jersey',
4, 'Seattle',
'Non domestic') "Location"
FROM inventories
WHERE product_id < 1775
ORDER BY product_id, "Location";