Oracle DECODE 函數深度解剖：語法、NULL陷阱、類型轉換與實戰避坑

語法結構

DECODE(expr, search1, result1 [, search2, result2 ...] [, default])
-- 依次比較 expr = search_i，匹配返回 result_i，全不匹配返回 default (默認 NULL)

經典案例：操作日誌標誌解碼

SELECT id,
       user_name,
       oper_time,
       oper_type,
       DECODE(oper_flag, '1', '成功', '0', '失敗', '未知') AS flag_desc
FROM   sec_log_operation;

實戰避坑：隱式類型轉換導致 ORA-01722

現象：DECODE(oper_flag, 1, '成功', '失敗') 報錯 ORA-01722: invalid number。
原因：oper_flag VARCHAR2 列含非數字值 (如 '失敗')，Oracle 嘗試將 expr 隱式轉換為 NUMBER 與 search=1 比較，遇到非數字拋異常。

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

-- 正確：搜索值加引號，強制字符串比較
DECODE(oper_flag, '1', '成功', '0', '失敗', '未知')

NULL 語義特殊規則

• DECODE(NULL, NULL, 'is null', 'not null') → 'is null' (Oracle 視 NULL=NULL 為真)。
• CASE WHEN NULL IS NULL THEN ... 同樣為真，但 CASE col WHEN NULL THEN ... 為假 (簡單 CASE 用 = 比較)。
• 建議：涉及 NULL 判斷時顯式用 NVL(col,'@NULL@') 或搜索 CASE。

類型優先級與隱式轉換規則

1. 所有 result_i 和 default 隱式轉換為最高優先級類型 (NUMBER > DATE > CHAR)。
2. 若 result1 是 NUMBER、result2 是 VARCHAR2 → 返回 NUMBER，VARCHAR2 隱式 TO_NUMBER，可能報錯。
3. 最佳實踐：TO_CHAR 統一返回字符串，或顯式 CAST。

性能與替代方案

經典面試題：DECODE 實現行轉列

SELECT deptno,
       SUM(DECODE(job,'CLERK',sal))   AS clerk_sal,
       SUM(DECODE(job,'SALESMAN',sal)) AS sales_sal,
       SUM(DECODE(job,'MANAGER',sal))  AS mgr_sal
FROM   emp
GROUP BY deptno;

11g+ 推薦用 PIVOT：SELECT * FROM emp PIVOT (SUM(sal) FOR job IN ('CLERK','SALESMAN','MANAGER'))。