Oracle 通用函數完整指南：NVL/NVL2/NULLIF/DECODE/CASE/COALESCE 實戰

Oracle 通用函數完整指南

通用函數處理 NULL 值、條件邏輯、值映射，是 SQL 查詢與 PL/SQL 開發核心工具。

NULL 處理三劍客

NVL(expr1, expr2)

• expr1 為 NULL → 返回 expr2
• 否則返回 expr1

SELECT NVL(NULL, 0), NVL(3, 0) FROM dual;  -- 0, 3

NVL2(expr1, expr2, expr3)

• expr1 非 NULL → 返回 expr2
• expr1 為 NULL → 返回 expr3

SELECT e.empno, e.ename, e.job, e.hiredate,
       (e.sal + NVL2(e.comm, e.comm, 0)) * 12 年薪
FROM emp e;

NULLIF(expr1, expr2)

• 兩表達式相等 → 返回 NULL
• 不相等 → 返回 expr1

SELECT NULLIF(1,1), NULLIF(1,2) FROM dual;  -- NULL, 1
-- 比較姓名長度與職位長度
SELECT e.empno, e.ename, e.job, LENGTH(e.ename), LENGTH(e.job),
       NULLIF(LENGTH(e.ename), LENGTH(e.job)) 長度是否相同
FROM emp e;

條件邏輯函數

DECODE(expr, search1, result1, search2, result2, ..., default)

等值多重分支，類似 switch-case。僅支援等值比較。

SELECT e.ename, e.sal,
       DECODE(e.job, 'CLERK', '業務員', 'SALESMAN', '銷售員',
                     'MANAGER', '經理', 'ANALYST', '分析師',
                     'PRESIDENT', '總裁') 職位
FROM emp e;

CASE 表達式（推薦）

支援範圍判斷、複合條件、巢狀邏輯，可讀性優於 DECODE。

-- 簡單 CASE
SELECT e.ename, e.sal, e.job,
       CASE e.job
         WHEN 'CLERK'     THEN e.sal * 1.1
         WHEN 'SALESMAN'  THEN e.sal * 1.2
         WHEN 'MANAGER'   THEN e.sal * 1.3
         ELSE e.sal * 1.5
       END 新薪資
FROM emp e;

-- 搜尋 CASE（支援任意條件）
CASE
  WHEN e.sal < 1000      THEN '低薪'
  WHEN e.sal BETWEEN 1000 AND 3000 THEN '中薪'
  ELSE '高薪'
END

COALESCE(expr1, expr2, ..., exprN)

返回第一個非 NULL 表達式，等同巢狀 NVL。ANSI 標準，可移植。

SELECT e.ename, e.sal, e.comm,
       COALESCE(e.comm, 100, 2000)  -- comm 不為空用 comm，否則 100，再否則 2000
FROM emp e;

函數選擇決策表

組合實戰：年薪計算多種寫法

-- 1. NVL
(e.sal + NVL(e.comm, 0)) * 12

-- 2. NVL2
(e.sal + NVL2(e.comm, e.comm, 0)) * 12

-- 3. COALESCE
(e.sal + COALESCE(e.comm, 0)) * 12

-- 4. CASE
(e.sal + CASE WHEN e.comm IS NULL THEN 0 ELSE e.comm END) * 12

-- 5. NULLIF 防除零（假設需計算平均提成）
AVG(e.comm) / NULLIF(COUNT(e.comm), 0)