Oracle 空白字元 和 NULL

Oracle 有一個與其他主流資料庫非常不同的特性：空字串（’’）在 Oracle 中等同於 NULL。這個行為符合 Oracle 自己的實作，但不符合 ANSI SQL 標準，是從其他資料庫遷移到 Oracle 時最常踩到的地雷之一。

Oracle 的空字串行為

在 Oracle 中，插入空字串 ’’ 時，資料庫會自動將其轉為 NULL：

1
2
3
4
5
6

-- 插入空字串
INSERT INTO users (name) VALUES ('');

-- 查詢結果
SELECT name, DUMP(name) FROM users;
-- name 欄位的值是 NULL，不是空字串

因此，用 WHERE name = ’’ 查詢永遠找不到任何資料（因為 NULL = NULL 在 SQL 中是 UNKNOWN，不是 TRUE）：

1
2
3
4
5

-- 這樣查不到任何資料
SELECT * FROM users WHERE name = '';

-- 正確做法：使用 IS NULL
SELECT * FROM users WHERE name IS NULL;

與其他資料庫的差異

這表示在 MySQL 或 SQL Server 中正常運作的查詢，移植到 Oracle 後行為可能完全不同。

NULL 處理函式

NVL：NULL 替換預設值

NVL(expr, default_value)：如果 xpr 是 NULL，回傳 default_value，否則回傳 xpr。

1
2
3
4
5

-- 若 name 為 NULL，顯示「(無名稱)」
SELECT NVL(name, '(無名稱)') FROM users;

-- 計算時避免 NULL 造成結果為 NULL
SELECT NVL(salary, 0) + NVL(bonus, 0) AS total FROM employees;

NVL2：根據是否為 NULL 回傳不同值

NVL2(expr, not_null_value, null_value)：如果 xpr 不是 NULL 回傳
ot_null_value，否則回傳
ull_value。

1
2

-- 若有電話號碼顯示「有」，否則顯示「無」
SELECT NVL2(phone, '有', '無') AS has_phone FROM users;

NULLIF：兩值相同時回傳 NULL

NULLIF(expr1, expr2)：如果兩個值相等，回傳 NULL；否則回傳 xpr1。

1
2

-- 若分母為 0 時避免除以零錯誤
SELECT numerator / NULLIF(denominator, 0) FROM calculations;

COALESCE：回傳第一個非 NULL 值

COALESCE 是 ANSI SQL 標準函式，Oracle 也支援：

1
2

-- 依序取第一個非 NULL 的值
SELECT COALESCE(nickname, name, '匿名') FROM users;

資料庫遷移注意事項

從 MySQL/SQL Server 遷移到 Oracle 時，需要特別檢查：

1. 所有用 = ’’ 比較空字串的地方，改為 IS NULL
2. 所有 INSERT INTO … VALUES (’’) 的資料，在 Oracle 中實際上儲存為 NULL
3. 所有 != ’’ 的條件，改為 IS NOT NULL

參考資料

• Oracle中的NULL、’’（空字符串）以及’_’（空格）- 博客园
• Oracle 官方文件 - NULL 相關函式