Oracle DECODE 函數進階實戰：從薪資分級調整到行列轉換的 SQL 魔法

DECODE 核心語法

DECODE(expr, search1, result1, search2, result2, ..., default)
-- 相當於：
CASE expr
  WHEN search1 THEN result1
  WHEN search2 THEN result2
  ...
  ELSE default
END

優點：**在 SQL 內部實現條件邏輯**，避免 PL/SQL 迴圈/上下文切換，適合集合操作。

案例一：薪資分級調整 (單表更新)

-- 需求：薪資 < 8000 漲 20%，≥ 8000 漲 15%
-- 傳統寫法：PL/SQL 迴圈 SELECT ... IF ... UPDATE
-- DECODE 一條 SQL 搞定
UPDATE employee
SET salary = DECODE(SIGN(salary - 8000),
                      1, salary * 1.15,   -- > 8000
                     -1, salary * 1.20,   -- < 8000
                      0, salary * 1.15);  -- = 8000 歸入 ≥ 档
-- SIGN() 返回 1/0/-1，完美映射三段邏輯

案例二：動態取較大/較小值 (無 GREATEST/LEAST 時)

-- 取較小值
SELECT DECODE(SIGN(a - b), -1, a, b) FROM dual;
-- 取較大值
SELECT DECODE(SIGN(a - b),  1, a, b) FROM dual;

案例三：行列轉換 (PIVOT) — 銷售月報橫表化

源表 SALE

MONTH      SELL
200001     1000
200002     1100
200003     1200
...
200301     1300

目標橫表

YEAR   JAN   FEB   MAR   APR   MAY   JUN   JUL   AUG   SEP   OCT   NOV   DEC
2000   1000  1100  1200  1300  1400  1500  1600  ...   ...   ...   ...   ...
2001   1100  1200  ...   ...   ...   ...   ...   ...   ...   ...   ...   ...
2002   1200  ...   ...   ...   ...   ...   ...   ...   ...   ...   ...   ...
2003   1300  ...   ...   ...   ...   ...   ...   ...   ...   ...   ...   ...

DECODE 實現 (11g 前無 PIVOT 時標準寫法)

CREATE OR REPLACE VIEW v_sale_pivot AS
SELECT
  SUBSTR(month,1,4) AS year,
  SUM(DECODE(SUBSTR(month,5,2), '01', sell, 0)) AS jan,
  SUM(DECODE(SUBSTR(month,5,2), '02', sell, 0)) AS feb,
  SUM(DECODE(SUBSTR(month,5,2), '03', sell, 0)) AS mar,
  SUM(DECODE(SUBSTR(month,5,2), '04', sell, 0)) AS apr,
  SUM(DECODE(SUBSTR(month,5,2), '05', sell, 0)) AS may,
  SUM(DECODE(SUBSTR(month,5,2), '06', sell, 0)) AS jun,
  SUM(DECODE(SUBSTR(month,5,2), '07', sell, 0)) AS jul,
  SUM(DECODE(SUBSTR(month,5,2), '08', sell, 0)) AS aug,
  SUM(DECODE(SUBSTR(month,5,2), '09', sell, 0)) AS sep,
  SUM(DECODE(SUBSTR(month,5,2), '10', sell, 0)) AS oct,
  SUM(DECODE(SUBSTR(month,5,2), '11', sell, 0)) AS nov,
  SUM(DECODE(SUBSTR(month,5,2), '12', sell, 0)) AS dec
FROM sale
GROUP BY SUBSTR(month,1,4)
ORDER BY year;

案例四：複雜條件分組統計 (避免多次掃描)

-- 單次掃描同時統計：總筆數、金額>1000筆數、狀態='PAID'筆數、各類型筆數
SELECT
  COUNT(*) AS total_cnt,
  SUM(DECODE(SIGN(amount - 1000), 1, 1, 0)) AS amt_gt_1000_cnt,
  SUM(DECODE(status, 'PAID', 1, 0)) AS paid_cnt,
  SUM(DECODE(type, 'A', 1, 0)) AS type_a_cnt,
  SUM(DECODE(type, 'B', 1, 0)) AS type_b_cnt
FROM transactions;

11g+ 現代替代：CASE 表達式與 PIVOT/UNPIVOT 子句

結語

DECODE 是 Oracle 早期 **「SQL 內部流程控制」的瑞士軍刀**，在無 CASE/PIVOT 年代支撐了無數複雜報表邏輯。現代開發 **優先使用 CASE 表達式 (可讀性) 與 PIVOT/UNPIVOT (行列轉換)**，但理解 DECODE 的 `SIGN()` 映射技巧、單次掃描多指標統計模式，對維護舊系統、閱讀經典優化案例、構建動態 SQL 生成器仍具不可替代價值。