DECODE 是 Oracle 獨有的強大條件表達式函數，支援空值識別、多條件匹配、默認值返回，廣泛用於報表轉換、標籤化、動態排序等場景。本文系統梳理語法規則、空值處理機制、SIGN 組合實現範圍判斷、與 CASE 的差異對比、ORDER BY 中的自定義排序技巧。
DECODE(control_value,
value1, result1,
value2, result2,
...
[default_result])
| 參數 | 說明 | 特殊規則 |
|---|---|---|
control_value |
被比較的表達式 (列、變量、函數結果) | 可為 NULL |
valueN |
匹配值 | 可為 NULL，NULL = NULL 為真 (獨特特性) |
resultN |
匹配成功返回值 | 可為 NULL，使用 NULL 關鍵字 |
default_result |
無匹配時返回 (可選) | 省略則返回 NULL |
-- 簡單值映射
SELECT DECODE(x, 1, 'x is 1', 2, 'x is 2', 'others') FROM dual;
-- x=1 → 'x is 1', x=2 → 'x is 2', 其他 → 'others'
-- 空值匹配 (DECODE 獨有優勢)
SELECT DECODE(col, NULL, 'is null', 'not null') FROM dual;
-- col 為 NULL → 'is null', 否則 → 'not null'
-- 注意：NVL(col, 'x') 無法區分『原本是 NULL』與『原本是字符串 "x"』
-- 返回 NULL
SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', NULL) FROM users;
-- status 不為 A/I → 返回 NULL
SIGN(n) 返回：1 (正數)、-1 (負數)、0 (零)。
-- 判斷 5-6 的正負
SELECT DECODE(SIGN(5-6), 1, 'Is Positive', -1, 'Is Negative', 'Is Zero') FROM dual;
-- SIGN(-1) = -1 → 'Is Negative'
-- 範圍分級：薪資分級
SELECT DECODE(SIGN(sal-5000), 1, 'High',
DECODE(SIGN(sal-2000), 1, 'Medium', 'Low'))
FROM emp;
-- 等價 CASE 寫法 (更可讀)
SELECT CASE SIGN(5-6)
WHEN 1 THEN 'Is Positive'
WHEN -1 THEN 'Is Negative'
ELSE 'Is Zero' END
FROM dual;
-- 需求：按 學科順序『語文、數學、英語』排序 (非字母順)
SELECT * FROM table_subject
ORDER BY DECODE(subject_name, '語文', 1, '數學', 2, '英語', 3, 99);
-- 進階：多級排序 (先按學科分類，再按分數降序)
SELECT * FROM scores
ORDER BY DECODE(subject, '語文', 1, '數學', 2, '英語', 3, 4), -- 學科優先級
score DESC; -- 同學科內按分數降序
| 維度 | DECODE | CASE (簡單/搜索) |
|---|---|---|
| 語法類型 | 函數 | 表達式 (SQL 標準) |
| 可讀性 | 多條件嵌套難讀 | 結構化清晰，支援縮進 |
| 空值匹配 | 原生支援 NULL=NULL | 需用 IS NULL，WHEN NULL THEN 不生效 |
| 比較運算符 | 僅等值 (=) | 支援 >, <, >=, <=, !=, LIKE, IN, BETWEEN |
| 返回類型推導 | 基於所有 result 類型推導 | 同左 |
| CHAR 比較語義 | 視為 VARCHAR2 (不填充空格) | 遵循 CHAR 語義 (填充空格) |
| 性能 | 略優 (內部優化) | 現代版本差異可忽略 |
| 可移植性 | Oracle 專有 | ANSI SQL 標準，跨庫兼容 |
CREATE TABLE tt (c1 CHAR(2), c2 VARCHAR2(2));
INSERT INTO tt VALUES ('A', 'A');
INSERT INTO tt VALUES('A', 'A ');
COMMIT;
-- DECODE: 不填充空格
SELECT DECODE(c1, 'A', 'MATCH', 'NO') FROM tt; -- NO, NO ('A' != 'A ')
SELECT DECODE(c2, 'A', 'MATCH', 'NO') FROM tt; -- MATCH, NO
-- CASE: 填充空格
SELECT CASE c1 WHEN 'A' THEN 'MATCH' ELSE 'NO' END FROM tt; -- MATCH, MATCH
SELECT CASE c2 WHEN 'A' THEN 'MATCH' ELSE 'NO' END FROM tt; -- MATCH, NO
此差異源於 DECODE 參數類型解析為 VARCHAR2，而 CASE 保持操作數原類型。
-- 將查詢結果動態生成 DECODE 表達式 (用於報表工具)
SELECT 'DECODE(' || column_name || ', ' ||
LISTAGG('''' || value || ''', ''' || label || '''', ', ') WITHIN GROUP (ORDER BY sort_order) ||
', ''Unknown'')' AS decode_expr
FROM code_mapping
WHERE table_name = 'ORDERS' AND column_name = 'STATUS'
GROUP BY column_name;
-- 輸出: DECODE(STATUS, 'N', 'New', 'P', 'Processing', 'S', 'Shipped', 'C', 'Cancelled', 'Unknown')
DECODE 精髓：等值匹配、原生 NULL 支援、函數語法、Oracle 專有。
CASE 精髓：範圍判斷、複雜邏輯、標準 SQL、可讀性優。
選型口訣：簡單映射用 DECODE，複雜判斷用 CASE，跨庫遷移用 CASE，空值匹配用 DECODE。
ORDER BY + DECODE = 自定義排序神器，報表開發必會。
掌握 DECODE 的空值特性、SIGN 組合範圍判斷、CHAR 比較陷阱與 CASE 差異，能讓您在 SQL 轉換邏輯中遊刃有餘，編寫簡潔高效的條件表達式。
