Oracle DECODE 函數完整指南：語法、與 CASE 對比、資料型別陷阱與實戰範例

DECODE 基本語法

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

• 依序比較 expr 與 searchN，相等則回傳對應 resultN
• 所有 searchN 未匹配時回傳 default (可省略，預設 NULL)
• 相當於 switch-case 但無 break，僅單一匹配

DECODE vs CASE 對比

經典範例

1. 狀態碼轉描述

SELECT id,
       DECODE(status, 'A', 'Active', 'I', 'Inactive', 'P', 'Pending', 'Unknown') AS status_desc
FROM orders;

2. NULL 值處理 (DECODE 特有優勢)

SELECT DECODE(flag, 'Y', 'Yes', 'N', 'No', NULL, 'Not Set', 'Default') 
FROM test_table;
-- flag 為 NULL 時回傳 'Not Set'，DECODE 視 NULL=NULL 為真

CASE 寫法需額外處理：CASE WHEN flag IS NULL THEN ... WHEN flag = 'Y' THEN ...

3. 數值範圍分組 (搭配 SIGN/TRUNC)

SELECT DECODE(SIGN(score - 60), -1, 'Fail', 0, 'Pass', 1, 'Pass') 
FROM students;
-- SIGN 回傳 -1/0/1，巧妙實現範圍判斷

資料型別陷阱：隱式轉換導致錯誤

問題重現

-- oper_flag 為 VARCHAR2，但資料混含 '1'、'0'、'失敗'
SELECT DECODE(oper_flag, 1, '成功', '失敗') FROM sec_log_operation;
-- ORA-01722: invalid number
-- 原因：1 為 NUMBER，Oracle 嘗試將 oper_flag 隱式轉 NUMBER，遇 '失敗' 失敗

解決方案：統一為字串比較

SELECT DECODE(oper_flag, '1', '成功', '失敗') FROM sec_log_operation;
-- 所有 search 值加引號，強制字串比較，避免隱式轉換

進階技巧

1. 多條件組合 (巢狀 DECODE)

SELECT DECODE(dept_id, 
  10, DECODE(job_id, 'CLERK', 'Admin-Clerk', 'Mgr', 'Admin-Mgr', 'Admin-Other'),
  20, DECODE(job_id, 'CLERK', 'Sales-Clerk', 'Sales-Other'),
  'Other-Dept'
) FROM employees;

2. 搭配聚合函數做條件統計

SELECT 
  COUNT(DECODE(status, 'A', 1)) AS active_cnt,
  COUNT(DECODE(status, 'I', 1)) AS inactive_cnt,
  SUM(DECODE(type, 'PREMIUM', amount, 0)) AS premium_revenue
FROM orders;
-- DECODE 回傳非 NULL 即被 COUNT 計數，回傳 0 參與 SUM

3. 動態排序

SELECT * FROM products
ORDER BY DECODE(:sort_by, 'PRICE', price, 'NAME', name, 'DATE', create_date);

最佳實務建議

1. 新代碼優先用 CASE：可讀性更好、支援複雜條件、ANSI 標準
2. DECODE 專用場景：NULL 等值匹配、簡單常數映射、舊系統維護
3. 明確指定資料型別：搜尋值加引號 (字串)、用 TO_NUMBER/TO_DATE 避免隱式轉換
4. 回傳值型別一致：所有 result 盡量同型別，避免隱式轉換效能損耗

遷移範例：DECODE → CASE

-- DECODE 版
SELECT DECODE(grade, 'A', 'Excellent', 'B', 'Good', 'C', 'Fair', 'Poor') FROM students;

-- CASE 版 (推薦)
SELECT CASE grade 
         WHEN 'A' THEN 'Excellent'
         WHEN 'B' THEN 'Good'
         WHEN 'C' THEN 'Fair'
         ELSE 'Poor'
       END
FROM students;

-- 條件複雜時 CASE 必勝
SELECT CASE 
         WHEN score >= 90 THEN 'A'
         WHEN score >= 80 THEN 'B'
         WHEN score >= 70 THEN 'C'
         ELSE 'F'
       END
FROM students;

DECODE 是 Oracle 早期特有的簡潔條件函數，雖然功能被 CASE 涵蓋並超越，但其「NULL=NULL 匹配」特性在特定場景 (如狀態碼含 NULL) 仍具優勢。現代開發建議：新代碼用 CASE，維護舊代碼時理解 DECODE 行為即可。