函數定義
NVL(expr1, expr2)
-- 若 expr1 為 NULL，回傳 expr2；否則回傳 expr1。
參數與回傳值
expr1、expr2：任意資料型別 (字串、數值、日期、TIMESTAMP 等)。- 回傳型別：遵循 隱含型別轉換規則，以較高精度/長度者為準。若兩者皆 NULL，回傳 NULL。
核心用法範例
-- 1. 顯示預設值
SELECT NVL(commission_pct, 0) FROM employees;
-- 2. 字串替換
SELECT NVL(last_name, 'Unknown') FROM employees;
-- 3. 日期預設
SELECT NVL(hire_date, SYSDATE) FROM employees;
-- 4. 多層替代 (巢狀)
SELECT NVL(NVL(phone_number, email), 'No Contact') FROM employees;
-- 5. 關鍵：型別必須相容
-- 錯誤：NVL(date_col, 'N/A') -- ORA-01843
-- 正確：NVL(TO_CHAR(date_col), 'N/A')
NVL vs NVL2 vs COALESCE
| 函數 | 語法 | 行為 | 適用場景 |
|---|
| NVL | NVL(e1, e2) | e1 非 NULL → e1；否則 e2 | 單一預設值替換 |
| NVL2 | NVL2(e1, e2, e3) | e1 非 NULL → e2；NULL → e3 | 依 NULL/非 NULL 分支回傳不同值 |
| COALESCE | COALESCE(e1, e2, ..., en) | 回傳第一個非 NULL 參數 | 多重備選、短路評估 (效能較好) |
實戰避坑清單
- 型別不匹配：
NVL(number_col, 'text') 拋出 ORA-01722。統一用 TO_CHAR 或 TO_NUMBER。
- 隱含轉換導致索引失效：
WHERE NVL(col, 'X') = 'Y' 無法使用 col 索引。改用 WHERE (col = 'Y' OR col IS NULL) 或函數索引。
- 效能：
COALESCE 支援短路，參數多時優於巢狀 NVL。
- NULL 語意：
NVL('', 'X') 在 Oracle 中 '' 等同 NULL，回傳 'X'；SQL Server 則視為空字串。跨庫遷移必須注意。
進階：函數索引支援 NVL 查詢
CREATE INDEX idx_emp_comm_nvl ON employees (NVL(commission_pct, -1));
-- 查詢可用索引
SELECT * FROM employees WHERE NVL(commission_pct, -1) = -1;
