Oracle CASE 表達式與語句完整語法指南

Oracle CASE 表達式與語句完整語法指南

CASE 結構提供比 IF-THEN-ELSIF 更簡潔、效能更佳的多重條件處理方式。Oracle 支援兩種形式：簡單 CASE 表達式（等值比較）與 搜尋 CASE 表達式（條件比較），並可在 SQL 與 PL/SQL 中使用。

1. 簡單 CASE 表達式（單一選擇器等值比較）

適用於同一運算式與多個離散值比較的場景。

語法

CASE selector
   WHEN value1 THEN result1
   WHEN value2 THEN result2
   ...
   [ELSE else_result]
END;

PL/SQL 範例

DECLARE
   v_count NUMBER;
BEGIN
   SELECT COUNT(*) INTO v_count FROM cip_temps;
   CASE v_count
      WHEN 1 THEN
         DBMS_OUTPUT.PUT_LINE('WHEN 1 - v_count: ' || v_count);
      WHEN 5 THEN
         DBMS_OUTPUT.PUT_LINE('WHEN 5 - v_count: ' || v_count);
      WHEN 10 THEN
         DBMS_OUTPUT.PUT_LINE('WHEN 10 - v_count: ' || v_count);
      ELSE
         DBMS_OUTPUT.PUT_LINE('ELSE - v_count: ' || v_count);
   END CASE;
END;
/

SQL 查詢中使用

SELECT ename,
       CASE deptno
          WHEN 10 THEN 'ACCOUNTING'
          WHEN 20 THEN 'RESEARCH'
          WHEN 30 THEN 'SALES'
          ELSE 'OTHER'
       END AS department
FROM   emp;

2. 搜尋 CASE 表達式（多條件布林比較）

適用於範圍判斷、複合條件、非等值比較場景。

語法

CASE
   WHEN condition1 THEN result1
   WHEN condition2 THEN result2
   ...
   [ELSE else_result]
END;

PL/SQL 範例

DECLARE
   v_count NUMBER;
BEGIN
   SELECT COUNT(*) INTO v_count FROM cip_temps;
   CASE
      WHEN v_count > 10 THEN
         DBMS_OUTPUT.PUT_LINE('>10 - v_count: ' || v_count);
      WHEN v_count > 5 THEN
         DBMS_OUTPUT.PUT_LINE('>5 - v_count: ' || v_count);
      WHEN v_count > 4 THEN
         DBMS_OUTPUT.PUT_LINE('>4 - v_count: ' || v_count);
      ELSE
         DBMS_OUTPUT.PUT_LINE('ELSE - v_count: ' || v_count);
   END CASE;
END;
/

SQL 範例：薪資分級

SELECT ename, sal,
       CASE
          WHEN sal >= 5000 THEN 'HIGH'
          WHEN sal >= 3000 THEN 'MEDIUM'
          WHEN sal >= 1500 THEN 'LOW'
          ELSE 'ENTRY'
       END AS salary_grade
FROM   emp;

CASE 表達式 vs CASE 語句

效能優勢

• CASE 為單一運算式，優化器可更好推導執行計畫
• 簡單 CASE 可轉換為跳躍表，O(1) 查找
• 避免多次評估同一選擇器運算式

常見陷阱

1. 型別一致性：所有 WHEN/ELSE 回傳型別必須相容，否則 ORA-00932
2. NULL 比較：簡單 CASE 使用等值比較，NULL = NULL 為 FALSE，需額外 WHEN NULL THEN（搜尋 CASE 可用 WHEN selector IS NULL）
3. 順序敏感：搜尋 CASE 依序評估，範圍條件須由嚴格到寬鬆排列
4. 缺少 ELSE：若無符合條件且無 ELSE，SQL 回傳 NULL，PL/SQL 拋出 CASE_NOT_FOUND 例外