Oracle DECODE 函數完全指南：語法、巢狀用法、CASE 對比與效能優化

Oracle DECODE 函數完全指南：語法、巢狀用法、CASE 對比與效能優化

DECODE 是 Oracle 專有的條件判斷函數，自早期版本即存在，雖然 ANSI SQL CASE 表達式更標準、功能更強，但 DECODE 在簡單等值比較場景下語法更簡潔、執行效率略優，且大量既有程式碼仍在使用。本文系統整理語法、巢狀寫法、與 CASE 差異、資料型別隱性轉換陷阱、效能優化建議與實戰模式。

基本語法

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


expr：被比較的運算式 (欄位、常數、函數回傳值)
searchN：比較值，若 expr = searchN 則回傳 resultN
resultN：對應回傳值
default：(可選) 所有 search 都不匹配時回傳，省略則回傳 NULL

等同於：

CASE expr WHEN search1 THEN result1 WHEN search2 THEN result2 ... ELSE default END

核心範例
-- 1. 簡單等值映射：部門代碼轉名稱
SELECT ename, deptno,
       DECODE(deptno, 10, 'ACCOUNTING',
                    20, 'RESEARCH',
                    30, 'SALES',
                    40, 'OPERATIONS',
                    'UNKNOWN') AS dname
FROM   emp;

-- 2. 巢狀 DECODE：多層條件 (職位 + 部門)
SELECT ename, job, deptno,
       DECODE(job, 'MANAGER', DECODE(deptno, 10, 'ADMIN_MGR', 20, 'RES_MGR', 'OTHER_MGR'),
                  'CLERK',    DECODE(deptno, 10, 'ADMIN_CLK', 'OTHER_CLK'),
                  'ANALYST',  'DATA_ANALYST',
                  'OTHER') AS role_type
FROM   emp;

-- 3. 搭配群組函數：條件式彙總 (單掃描多指標)
SELECT deptno,
       COUNT(*) AS total,
       SUM(DECODE(job, 'MANAGER', 1, 0)) AS mgr_cnt,
       SUM(DECODE(job, 'CLERK', 1, 0))   AS clk_cnt,
       AVG(DECODE(job, 'SALESMAN', sal)) AS avg_sales_sal
FROM   emp
GROUP BY deptno;

-- 4. 排序控制：自訂順序
SELECT ename, job
FROM   emp
ORDER BY DECODE(job, 'PRESIDENT', 1, 'MANAGER', 2, 'ANALYST', 3, 'SALESMAN', 4, 'CLERK', 5, 6);

DECODE vs CASE 完整對照

維度
DECODE
CASE (簡單/搜尋式)
標準
Oracle 專有
ANSI SQL:1999 標準
比較型別
僅支援 等值比較 (expr = search)
支援任意布林運算式 (<, >, BETWEEN, LIKE, IN, IS NULL...)
可讀性
參數位置式，參數多易誤序
關鍵字式，結構清晰，易維護
巢狀限制
無硬性限制，但極難閱讀
支援 CASE WHEN ... THEN ... END 巢狀，較清晰
NULL 處理
DECODE(NULL, NULL, 'A', 'B') → 'A' (NULL=NULL 視為真)
CASE WHEN NULL = NULL THEN 'A' ELSE 'B' END → 'B' (NULL=NULL 為 UNKNOWN)
效能
內部實作較輕量，簡單等值略快
現代最佳化器對兩者產生相同執行計畫
回傳型別
隱性轉換規則複雜，易導致 ORA-01722
型別推導較嚴格，錯誤訊息較明確


關鍵陷阱：NULL 語意差異
-- DECODE 視 NULL = NULL 為匹配
SELECT DECODE(NULL, NULL, 'MATCH', 'NO_MATCH') FROM dual;  -- → 'MATCH'

-- CASE 搜尋式視 NULL = NULL 為 UNKNOWN (不匹配)
SELECT CASE WHEN NULL = NULL THEN 'MATCH' ELSE 'NO_MATCH' END FROM dual;  -- → 'NO_MATCH'

-- CASE 簡單式同 DECODE
SELECT CASE NULL WHEN NULL THEN 'MATCH' ELSE 'NO_MATCH' END FROM dual;  -- → 'MATCH'

-- 實務建議：明確處理 NULL
SELECT DECODE(col, NULL, 'IS_NULL', col, 'HAS_VALUE', 'OTHER') ...  -- DECODE 允許 search 為 NULL
SELECT CASE WHEN col IS NULL THEN 'IS_NULL' ELSE 'HAS_VALUE' END ...  -- CASE 較明確

資料型別隱性轉換風險
SELECT deptno,
       SUM(DECODE(job, 'CLERK', sal))   AS clerk_sal,
       SUM(DECODE(job, 'MANAGER', sal)) AS mgr_sal,
       SUM(DECODE(job, 'SALESMAN', sal)) AS sales_sal,
       SUM(DECODE(job, 'ANALYST', sal))  AS anal_sal
FROM   emp
GROUP BY deptno;

2. 字串彙總 (11gR2 前替代 LISTAGG)
-- 利用 DECODE + SYS_CONNECT_BY_PATH (舊版技巧)
SELECT deptno,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(ename, ',')) KEEP (DENSE_RANK LAST ORDER BY curr), ',') AS emp_list
FROM   (SELECT deptno, ename,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) - 1 AS prev
        FROM emp)
WHERE  CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR curr = prev AND PRIOR deptno = deptno
START WITH prev = 0
GROUP BY deptno;
-- 現代環境請直接用 LISTAGG

3. 資料清洗：標準化代碼值
UPDATE staging_table
SET    std_gender = DECODE(UPPER(TRIM(raw_gender)),
                           'M', 'MALE', 'MALE', 'MAN', 'MALE',
                           'F', 'FEMALE', 'FEMALE', 'WOMAN', 'FEMALE',
                           'U', 'UNKNOWN', 'UNKNOWN', 'OTHER', 'UNKNOWN',
                           'UNKNOWN');  -- default

遷移建議：逐步轉向 CASE

新程式碼 一律使用 CASE (標準、可讀、功能完整)。
既有 DECODE 若無效能瓶頸、無維護困擾，不必強制重寫。
重構時將巢狀 DECODE 改為 CASE WHEN ... THEN ... END，同步加入單元測試驗證語義等價。
針對條件式彙總 SUM(DECODE(...))，若已升級 12c+，可考慮改寫為 FILTER 子句 (標準 SQL)：

SUM(sal) FILTER (WHERE job = 'MANAGER') (PostgreSQL/Oracle 21c+ 支援)。


理解 DECODE 的 NULL 語意、型別轉換行為與效能特徵，能在維護舊系統與撰寫高效能 SQL 時遊刃有餘。