Oracle CASE 表達式完全指南：簡單/搜索/嵌套/NULL處理與性能技巧

兩種基本語法形式

1. 簡單 CASE (Simple CASE) — 等值判斷

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  ELSE default_result
END

適用於單一列/表達式與多個離散值比較。

2. 搜索 CASE (Searched CASE) — 條件判斷

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE default_result
END

適用於範圍、複合條件、多列比較。

實戰範例：產品類型解碼

-- 簡單 CASE
SELECT product_id,
       CASE product_type_id
         WHEN 1 THEN 'Book'
         WHEN 2 THEN 'Video'
         WHEN 3 THEN 'DVD'
         WHEN 4 THEN 'CD'
         ELSE 'Magazine'
       END AS product_category
FROM   products;

-- 搜索 CASE (等價邏輯)
SELECT product_id,
       CASE
         WHEN product_type_id = 1 THEN 'Book'
         WHEN product_type_id = 2 THEN 'Video'
         WHEN product_type_id = 3 THEN 'DVD'
         WHEN product_type_id = 4 THEN 'CD'
         ELSE 'Magazine'
       END AS product_category
FROM   products;

進階用法

嵌套 CASE

CASE
  WHEN region = 'APAC' THEN
    CASE
      WHEN country = 'CN' THEN 'China'
      WHEN country = 'JP' THEN 'Japan'
      ELSE 'Other APAC'
    END
  WHEN region = 'EMEA' THEN 'Europe/Middle East/Africa'
  ELSE 'Americas'
END

NULL 語義陷阱

• CASE col WHEN NULL THEN ... 永遠不匹配 (NULL != NULL)。
• 正確寫法：WHEN col IS NULL THEN ... (需用搜索 CASE)。
• 結果類型：所有 THEN/ELSE 表達式隱式轉換為最高優先級類型，建議顯式 TO_CHAR/TO_NUMBER 統一。

在 WHERE/ORDER BY/GROUP BY 中使用

-- 動態排序
ORDER BY CASE :sort_col
           WHEN 'name'  THEN ename
           WHEN 'sal'   THEN TO_CHAR(sal,'FM999990.00')
           WHEN 'hired' THEN hiredate
         END;

-- 條件聚合 (單掃描多指標)
SELECT deptno,
       COUNT(CASE WHEN sal > 3000 THEN 1 END) AS high_sal_cnt,
       SUM(CASE WHEN job = 'SALESMAN' THEN comm ELSE 0 END) AS sales_comm
FROM   emp
GROUP BY deptno;

CASE vs DECODE 對比

結論： 新代碼統一使用 CASE，遷移舊 DECODE 時注意 NULL 語義差異。

性能優化提示

1. 將高選擇性條件放前面 (短路求值)。
2. 避免在 THEN/ELSE 中調用昂貴函數，改用標量子查詢或物化視圖預計算。
3. 大量 WHEN 分支 (＞50) 考慮 DECODE 或查找表 JOIN，解析更快。