Oracle DECODE 函數多值判斷完整語法與實戰範例

DECODE 是 Oracle 特有的條件轉換函數，支援多值等值比對與預設值。本文詳細說明語法變體並以 DBA_USERS 實戰演示。

語法結構

DECODE(expr, search1, result1)
DECODE(expr, search1, result1, search2, result2)
DECODE(expr, search1, result1, ..., searchn, resultn)
DECODE(expr, search1, result1, default)
DECODE(expr, search1, result1, search2, result2, default)
DECODE(expr, search1, result1, ..., searchn, resultn, default)

邏輯：依序比對 expr = search_i，匹配則回傳 result_i；全不匹配回傳 default (省略則回傳 NULL)。

實戰範例：判斷用戶帳號鎖定狀態

-- 建立測試表
CREATE TABLE t AS SELECT username, default_tablespace, lock_date FROM dba_users;

-- 1. 簡單判斷：lock_date 為 NULL 顯示 'unlocked'
SELECT username, DECODE(lock_date, NULL, 'unlocked') AS status FROM t;
-- 結果：SYS/SYSTEM/DBSNMP/SCOTT 顯示 unlocked，其餘顯示 NULL (即空白)

-- 2. 加上預設值：非 NULL 顯示 'locked'
SELECT username, DECODE(lock_date, NULL, 'unlocked', 'locked') AS status FROM t;
-- 結果：前四位 unlocked，其餘 locked

-- 3. 多值判斷：依表空間分類
SELECT username,
       DECODE(default_tablespace,
              'SYSTEM', 'System TS',
              'SYSAUX', 'Sysaux TS',
              'USERS',  'User TS',
              'Other TS') AS ts_category
FROM t;

輸出結果節錄

USERNAME  STATUS
SYS       unlocked
SYSTEM    unlocked
DBSNMP    unlocked
SCOTT     unlocked
OUTLN     locked
WMSYS     locked
...       ...

進階技巧

• 搭配 SIGN/CASE 可實現範圍判斷，但不如直接用 CASE 直觀。
• 巢狀 DECODE 可處理多欄位組合邏輯，但可讀性差，建議改用 CASE。
• 效能：極簡單等值轉換比 CASE 略快，現代優化器差異可忽略。

善用 DECODE 處理簡單離散值映射，複雜邏輯請轉用標準 CASE。