Oracle DECODE 函數完全指南：語法、技巧、行轉列實戰與 SIGN 搭配大小比較

DECODE 核心語法

DECODE(表達式, 條件1, 結果1, 條件2, 結果2, ..., 預設值)
-- 等同於
CASE 表達式
  WHEN 條件1 THEN 結果1
  WHEN 條件2 THEN 結果2
  ...
  ELSE 預設值
END

Oracle 專有函數，簡潔實現條件邏輯，避免 PL/SQL 流程控制切換上下文。

經典應用場景

1. 薪資分級調整（官方範例改良）

SELECT ENAME, SAL,
       DECODE(SIGN(SAL - 8000),
              1, SAL * 1.15,   -- >8000 加 15%
             -1, SAL * 1.20,   -- <8000 加 20%
              0, SAL * 1.10,   -- =8000 加 10%
              SAL) AS NEW_SAL   -- 預設不調
FROM EMP;

SIGN(n) 回傳：1(正)、-1(負)、0(零)，將大小比較轉為等值比較。

2. 取兩值中較小/較大

-- 取較小值
SELECT DECODE(SIGN(V1 - V2), -1, V1, V2) FROM DUAL;

-- 取較大值
SELECT DECODE(SIGN(V1 - V2), 1, V1, V2) FROM DUAL;

3. 行轉列（樞紐分析）銷售報表

來源表 SALE(MONTH CHAR(6), SELL NUMBER)，資料如 200001,1000 表示 2000 年 1 月銷售 1000。

CREATE OR REPLACE VIEW V_SALE AS
SELECT SUBSTR(MONTH,1,4) AS YEAR,
       SUM(DECODE(SUBSTR(MONTH,5,2), '01', SELL, 0)) AS MONTH1,
       SUM(DECODE(SUBSTR(MONTH,5,2), '02', SELL, 0)) AS MONTH2,
       SUM(DECODE(SUBSTR(MONTH,5,2), '03', SELL, 0)) AS MONTH3,
       SUM(DECODE(SUBSTR(MONTH,5,2), '04', SELL, 0)) AS MONTH4,
       SUM(DECODE(SUBSTR(MONTH,5,2), '05', SELL, 0)) AS MONTH5,
       SUM(DECODE(SUBSTR(MONTH,5,2), '06', SELL, 0)) AS MONTH6,
       SUM(DECODE(SUBSTR(MONTH,5,2), '07', SELL, 0)) AS MONTH7,
       SUM(DECODE(SUBSTR(MONTH,5,2), '08', SELL, 0)) AS MONTH8,
       SUM(DECODE(SUBSTR(MONTH,5,2), '09', SELL, 0)) AS MONTH9,
       SUM(DECODE(SUBSTR(MONTH,5,2), '10', SELL, 0)) AS MONTH10,
       SUM(DECODE(SUBSTR(MONTH,5,2), '11', SELL, 0)) AS MONTH11,
       SUM(DECODE(SUBSTR(MONTH,5,2), '12', SELL, 0)) AS MONTH12
FROM SALE
GROUP BY SUBSTR(MONTH,1,4);

單次掃描完成 12 月欄位聚合，效能遠優於 12 次子查詢或 UNION ALL。

4. 多條件聚合統計

SELECT COUNT(DECODE(STATUS,'Y','X')) AS Y_CNT,
       COUNT(DECODE(STATUS,'N','X')) AS N_CNT
FROM EMP
WHERE ENAME LIKE 'SMITH%';

一次全表掃描完成多類別計數，避免多次掃描。

進階技巧

• DECODE 內可嵌套 DECODE 處理多層邏輯
• 搭配 GREATEST/LEAST、NULLIF、COALESCE 簡化邏輯
• 12c+ 建議改用標準 CASE 表達式，可移植性更好

效能注意事項

• DECODE 在 SQL 層執行，無 PL/SQL 上下文切換，效率高
• 大量嵌套可能導致解析樹過大，影響共享池
• 配合函數索引（CREATE INDEX IDX ON T(DECODE(STATUS,'Y',1))）可加速條件查詢