萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle NULL 值完整處理指南:特性、比較規則、NVL/COALESCE 函數、索引與排序行為

一、NULL 本質定義

  • **未知、不確定、缺失值**,非零、非空字串、非空格。
  • 任何與 NULL 運算 (+ - * / ||) 結果均為 NULL (字串連接 || 例外,視為忽略 NULL)。
  • 主鍵欄位、NOT NULL 約束欄位**禁止**存 NULL。

二、比較與判斷規則總結表

表達式結果說明
NULL = NULLUNKNOWN (視同 FALSE)不相等,也非不相等
NULL <> NULLUNKNOWN
NULL > 0UNKNOWN所有比較運算子同理
NULL IS NULLTRUE**唯一正確判斷 NULL 的方式**
NULL IS NOT NULLFALSE
NVL(NULL, 0) = NVL(NULL, 0)TRUE轉換後可比較
'' = ''UNKNOWN (Oracle 視 '' 為 NULL)Oracle 特有:空字串 = NULL
NULL 比較規則總結表

三、NULL 相關函數詳細語法

1. NVL(expr1, expr2)

NVL(NULL, 0)        → 0
NVL('abc', 'def')   → 'abc'
NVL('', 0)          → 0   -- 空字串視為 NULL
-- expr1, expr2 應同類型,否則隱含轉換可能報 ORA-01722

2. NVL2(expr1, expr2, expr3)

NVL2(NULL, 'not null', 'is null')  → 'is null'
NVL2('val', 'not null', 'is null') → 'not null'
-- expr2, expr3 類型不同以 expr2 為準轉換

3. NULLIF(expr1, expr2)

NULLIF(1, 3)   → 1   -- 不相等回傳 expr1
NULLIF(1, 1)   → NULL -- 相等回傳 NULL
NULLIF('a', 1) → ORA-00932  -- 類型必須一致

4. COALESCE(expr1, expr2, ..., exprN)

COALESCE(NULL, NULL, 3, 4) → 3  -- 回傳第一個非 NULL
COALESCE(NULL, NULL)       → NULL
-- 所有參數必須同類型,等價於 CASE WHEN expr1 IS NOT NULL THEN expr1 ... END

四、NULL 與索引

  • B*Tree 索引不存全 NULL 鍵值行:CREATE INDEX idx ON t(a,b),若 a IS NULL AND b IS NULL 則不進索引。
  • UNIQUE 索引允許多行全 NULL:(NULL, NULL) 與 (NULL, NULL) 不視為重複。
  • 部分 NULL 進索引:(1, NULL) 會被索引,(1, NULL) 重複則違反 UNIQUE。
  • 解決方案:CREATE INDEX idx ON t(NVL(a, -1), NVL(b, -1)) 或使用 BITMAP INDEX (含 NULL)。

五、NULL 排序行為

ORDER BY col ASC   -- NULL 排最後 (視為最大)
ORDER BY col DESC  -- NULL 排最前 (視為最大)
ORDER BY col ASC NULLS FIRST   -- 強制 NULL 在前
ORDER BY col DESC NULLS LAST   -- 強制 NULL 在後

六、NULL 與效能:NOT NULL 約束代價

-- 測試:1 億次迴圈賦值
DECLARE v NUMBER NOT NULL := 0;  -- 有 NOT NULL 檢查
BEGIN FOR i IN 1..100000000 LOOP v := i; END LOOP; END;  -- 1043 百分秒

DECLARE v NUMBER;                -- 無檢查
BEGIN FOR i IN 1..100000000 LOOP v := i; END LOOP; END;  -- 顯著更快

結論:**非必要勿加 NOT NULL 約束** (特別是 PL/SQL 區域變數),除非業務強制要求。

七、實務防禦性編碼模式

  • WHERE 條件:WHERE col = val OR (col IS NULL AND val IS NULL) 或 WHERE NVL(col, '§') = NVL(val, '§') (特殊字元作哨兵)。
  • 聚合統計:COUNT(col) 忽略 NULL,COUNT(*) 計總行數,AVG(col) 忽略 NULL 行。
  • 外鍵關聯:父表 PK 不可為 NULL,子表 FK 可為 NULL (表示無關聯)。

NULL 是關係型資料庫的「第三邏輯值」,掌握 IS NULL、NVL/COALESCE、索引不存全 NULL、排序 NULLS FIRST/LAST 四大鐵律,能寫出正確且健壯的 SQL。

copyright © 萬盛學電腦網 all rights reserved