DECODE 是 Oracle 獨有的強大條件表達式函數,可將輸入值與一組搜尋值逐一比對,返回對應結果,支援 NULL 值比對(這是標準 CASE 早期版本不支援的特性)。雖然 ANSI SQL CASE 表達式已成為標準,但 DECODE 在簡潔性、NULL 處理、ORDER BY 自訂排序等場景仍具獨特優勢。本文完整涵蓋語法、參數說明、基礎與進階範例、搭配 SIGN 進行數值比較、ORDER BY 應用,並與 CASE 對比。
DECODE(control_value,
value1, result1,
[value2, result2, ...],
[default_result])
| 參數 | 說明 |
|---|---|
control_value | 待比較的輸入值(運算式、欄位、函數回傳值)。 |
valueN | 搜尋值。若 control_value = valueN(含 NULL = NULL),返回對應 resultN。搜尋值可為常數、運算式、NULL 關鍵字。 |
resultN | 對應返回值。型別需與其他 result 相容(隱式轉換)。 |
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'
-- NULL 值處理(DECODE 強項)
SELECT DECODE(col, NULL, 'is null', 'not null') FROM dual;
-- col 為 NULL → 'is null';否則 → 'not null'
-- 標準 CASE WHEN col IS NULL 則需顯式 IS NULL 判斷
-- SIGN(n): 正數→1, 負數→-1, 零→0
SELECT DECODE(SIGN(5 - 6),
1, 'Is Positive',
-1, 'Is Negative',
'Is Zero') FROM dual;
-- 結果: 'Is Negative'
-- 等價 CASE 寫法
SELECT CASE SIGN(5 - 6)
WHEN 1 THEN 'Is Positive'
WHEN -1 THEN 'Is Negative'
ELSE 'Is Zero'
END FROM dual;
此技巧常用於:餘額正負判斷、閾值比較轉換為離散標籤。
-- 需求:依 subject_name 指定順序:國文 → 數學 → 英文
SELECT *
FROM table_subject
ORDER BY DECODE(subject_name, '國文', 1, '數學', 2, '英文', 3, 99);
default_result=99 將未列舉值排至最後。CASE WHEN 或臨時表 Join。-- 邏輯:若 dept=10 且 sal>5000 → 'HIGH'
-- 若 dept=10 且 sal<=5000 → 'MID'
-- 其他 → 'LOW'
SELECT DECODE(deptno, 10,
DECODE(SIGN(sal - 5000), 1, 'HIGH', 'MID'),
'LOW') FROM emp;
| 維度 | DECODE | CASE (Simple/Searched) |
|---|---|---|
| 標準性 | Oracle 專有 | ANSI SQL 標準,跨資料庫可攜 |
| NULL 比較 | 原生支援 DECODE(col, NULL, ...) | Simple CASE 不支援;Searched CASE 需 WHEN col IS NULL |
| 比較型別 | 隱式轉換為 VARCHAR2 比較(見下文陷阱) | 遵循標準 SQL 比較語意(CHAR 補空格) |
| 可讀性 | 簡單映射極簡潔 | 複雜邏輯結構更清晰 |
| 效能 | 極微小優勢(內部實現較輕量) | 現代優化器幾乎等價 |
| 嵌套靈活度 | 巢狀較難閱讀 | 多層 WHEN/ELSE 結構清晰 |
CREATE TABLE tt (c1 CHAR(2), c2 VARCHAR2(2));
INSERT INTO tt VALUES ('A', 'A');
INSERT INTO tt VALUES('A ', 'A '); -- 含尾隨空格
-- DECODE: 視為 VARCHAR2 比較,不補空格
SELECT DECODE(c1, 'A', 'MATCH', 'NOMATCH') FROM tt;
-- 結果: NOMATCH, NOMATCH (因 'A' != 'A ')
-- CASE: 遵循 CHAR 比較規則,補空格
SELECT CASE c1 WHEN 'A' THEN 'MATCH' ELSE 'NOMATCH' END FROM tt;
-- 結果: MATCH, MATCH (因 'A' 補空格 = 'A ')
原因: DECODE 內部參數定義以 VARCHAR2 為主,導致隱式轉換改變比較語意;CASE 為語法結構,保留原資料型別語意。
DECODE,程式碼最精簡。CASE(Searched CASE),可讀性與維護性優越。CASE 或顯式 TRIM/RPAD 統一格式。CASE;既有 DECODE 正常運作無需強制改寫。DECODE 是 Oracle 開發者的利器,精通其 NULL 原生支援、SIGN 搭配技巧、ORDER BY 自訂排序,能寫出更簡潔高效的 SQL。但務必警惕其對 CHAR 型別的隱式 VARCHAR2 轉換行為,關鍵比較邏輯建議改用標準 CASE 確保語意正確。將兩者視為互補工具而非單純替代關係,才是成熟的 SQL 開發態度。