Oracle NVL 函數完全指南：語法、類型匹配、NVL2/COALESCE 進階用法與實戰範例

Oracle NVL 函數：空值處理核心工具

NVL(expr1, expr2) 是 Oracle 最常用的空值替換函數：若 expr1 IS NULL 則回傳 expr2，否則回傳 expr1。本文涵蓋語法規則、資料類型匹配要求、NVL2/COALESCE 進階用法、效能考量與實戰範例。

基本語法與行為

NVL(expr1, expr2)
-- expr1 為 NULL → 回傳 expr2
-- expr1 不為 NULL → 回傳 expr1
-- 兩者皆 NULL → 回傳 NULL

資料類型匹配規則 (重要)

• expr1 與 expr2 必須為相容類型，或可隱性轉換
• 數值型：NVL(SAL, 0) ✓
• 字串型：NVL(NAME, 'UNKNOWN') ✓
• 日期型：NVL(HIREDATE, DATE '1900-01-01') ✓
• 錯誤範例：NVL(SAL, 'ZERO') → ORA-01722 (數值/字串不匹配)

實戰範例

-- 1. 查詢學生姓名，若為 NULL 顯示 '空值'
SELECT NVL(A.NAME, '空值') AS NAME
FROM STUDENT A JOIN SCHOOL B ON A.ID = B.ID;

-- 2. 計算薪資總和，將 NULL 視為 0
SELECT SUM(NVL(SAL, 0)) FROM EMP;

-- 3. 日期預設值
SELECT NVL(END_DATE, SYSDATE) FROM PROJECTS;

-- 4. 串聯字串避免 NULL 導致整體變 NULL
SELECT FIRST_NAME || ' ' || NVL(MIDDLE_NAME, '') || ' ' || LAST_NAME FROM USERS;

進階函數：NVL2 與 COALESCE

NVL2 範例

-- 獎金計算：有業績給 10%，無業績給固定 1000
SELECT ENAME, NVL2(SALES, SAL * 0.1, 1000) AS BONUS FROM EMP;

-- 狀態標籤
SELECT ORDER_ID, NVL2(SHIP_DATE, 'SHIPPED', 'PENDING') AS STATUS FROM ORDERS;

COALESCE 範例

-- 取第一個非 NULL 電話：手機 → 公司 → 住宅 → 'UNKNOWN'
SELECT CUST_NAME, COALESCE(MOBILE, OFFICE_PHONE, HOME_PHONE, 'UNKNOWN') AS CONTACT_PHONE
FROM CUSTOMERS;

-- 效能優勢：短路求值，若 MOBILE 不為 NULL，後續運算式不執行
-- NVL 巢狀：NVL(MOBILE, NVL(OFFICE_PHONE, NVL(HOME_PHONE, 'UNKNOWN'))) 全部需求值

效能與最佳實踐

1. 索引失效風險：WHERE NVL(COL, 'X') = 'Y' 會導致 COL 索引失效。改寫：WHERE (COL = 'Y' OR (COL IS NULL AND 'X' = 'Y')) 或建立函數索引 CREATE INDEX IDX_NVL_COL ON T(NVL(COL, 'X'))。
2. 短路求值：COALESCE 支援短路，NVL 兩參數皆會求值。複雜運算式優先用 COALESCE。
3. 類型一致性：明確轉換 TO_CHAR/TO_NUMBER/TO_DATE 避免隱性轉換錯誤。
4. 避免過度使用：資料模型設計階段應定義 NOT NULL + DEFAULT，減少查詢層 NVL。

相容性補充

• SQL Server：ISNULL(expr1, expr2) (僅兩參數)
• PostgreSQL/MySQL：COALESCE (標準)、IFNULL (兩參數)
• 跨資料庫開發建議統一使用 COALESCE (ANSI 標準)