Oracle CASE WHEN 進階應用與最佳實務指南

兩種 CASE 語法形式

1. 簡單 CASE (Simple CASE)

比較單一運算式與多個值。

CASE search_expression
    WHEN expression1 THEN result1
    WHEN expression2 THEN result2
    ...
    ELSE default_result
END

2. 搜尋 CASE (Searched CASE)

評估多個獨立布林條件，功能更強。

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

關鍵差異

• 簡單 CASE 僅能做等值比對；搜尋 CASE 支援任意條件 (>, <, BETWEEN, IN, LIKE 等)。
• 兩者可相互轉換，搜尋 CASE 更通用。
• 順序優先：只回傳第一個符合條件的結果，後續分支自動忽略。

-- 永遠不會回傳 '第二類'
CASE
    WHEN col_1 IN ('a','b') THEN '第一類'
    WHEN col_1 IN ('a')   THEN '第二類'
    ELSE '其他'
END

實戰場景與範例

場景一：依業務規則重新分組統計

原始資料：國家人口表。需求：依洲份彙總人口 (原表無洲份欄位)。

SELECT SUM(population) AS 人口,
    CASE country
        WHEN '中國'   THEN '亞洲'
        WHEN '印度'   THEN '亞洲'
        WHEN '日本'   THEN '亞洲'
        WHEN '美國'   THEN '北美洲'
        WHEN '加拿大' THEN '北美洲'
        WHEN '墨西哥' THEN '北美洲'
        ELSE '其他'
    END AS 洲
FROM Table_A
GROUP BY CASE country
    WHEN '中國'   THEN '亞洲'
    WHEN '印度'   THEN '亞洲'
    WHEN '日本'   THEN '亞洲'
    WHEN '美國'   THEN '北美洲'
    WHEN '加拿大' THEN '北美洲'
    WHEN '墨西哥' THEN '北美洲'
    ELSE '其他'
END;

• GROUP BY 必須重複完整 CASE 運算式 (不可用別名)。
• 避免建立中間 View，支援動態調整分組邏輯。

場景二：數值區間分級統計

薪資分級：≤500→1, 501-600→2, 601-800→3, 801-1000→4, 其他→NULL。

SELECT
    CASE
        WHEN salary <= 500 THEN '1'
        WHEN salary > 500 AND salary <= 600 THEN '2'
        WHEN salary > 600 AND salary <= 800 THEN '3'
        WHEN salary > 800 AND salary <= 1000 THEN '4'
        ELSE NULL
    END AS salary_class,
    COUNT(*)
FROM Table_A
GROUP BY CASE
    WHEN salary <= 500 THEN '1'
    WHEN salary > 500 AND salary <= 600 THEN '2'
    WHEN salary > 600 AND salary <= 800 THEN '3'
    WHEN salary > 800 AND salary <= 1000 THEN '4'
    ELSE NULL
END;

場景三：單一查詢產出交叉表 (樞紐分析)

原始：國家、性別、人口。目標：列為國家，欄為男/女人口。

SELECT country,
    SUM(CASE WHEN sex = '1' THEN population ELSE 0 END) AS 男,
    SUM(CASE WHEN sex = '2' THEN population ELSE 0 END) AS 女
FROM Table_A
GROUP BY country;

• 單掃描完成雙欄位彙總，效能優於 UNION ALL 多掃描。
• 輸出格式直接符合報表需求。

場景四：CHECK 約束中的條件驗證

規則：女員工薪資必須 > 1000；男員工無限制。

-- 正確：使用 CASE 實現條件約束
CONSTRAINT chk_salary CHECK (
    CASE
        WHEN sex = '2' THEN
            CASE WHEN salary > 1000 THEN 1 ELSE 0 END
        ELSE 1  -- 男員工直接通過
    END = 1
);

-- 錯誤：單純 AND 會導致男員工無法插入
CONSTRAINT chk_salary CHECK (sex = '2' AND salary > 1000);

場景五：巢狀 CASE 實現多層邏輯

SELECT id,
    CASE
        WHEN val LIKE 'a%' THEN
            CASE
                WHEN val LIKE 'abcd%' THEN '2'
                ELSE '1'
            END
        ELSE '999'
    END AS case_result
FROM feng_test;

• 內層 CASE 可作為外層 THEN 的結果運算式。
• 注意條件順序：較寬鬆條件 (a%) 應置於較嚴格條件 (abcd%) 之前，否則永遠匹配不到嚴格條件。

最佳實務建議

1. 優先使用搜尋 CASE：通用性高，可維護性好。
2. 條件順序由嚴格到寬鬆：避免前置條件攔截後續邏輯。
3. ELSE 子句不可省略：明確處理預期外情況，避免隱性 NULL 導致下游錯誤。
4. 複雜邏輯封裝為函數：超過 5-7 個分支時，建議寫成 PL/SQL 函數，SQL 保持簡潔。
5. 效能考量：CASE 在查詢中開銷極低；但在巨量資料 GROUP BY CASE ... 時，考慮建立虛擬欄位或物化視圖預先計算分組鍵。