Oracle三大核心資料類型全解析：字元、數值、特殊類型深度指南

資料類型分類總覽

Oracle資料類型分為三大類：字元類型、數值類型、其他特殊類型。正確選型直接影響儲存效率、查詢效能與應用正確性。

一、字元資料類型

關鍵差異：CHAR vs VARCHAR2

-- CHAR(10) 存 'ABC' → 實際佔用10位元組 'ABC       '
-- VARCHAR2(10) 存 'ABC' → 實際佔用3位元組 'ABC'
-- 查詢時：WHERE char_col = 'ABC' 需注意空格匹配問題

Oracle官方強烈建議：優先使用VARCHAR2，節省空間並避免空格比較陷阱。

國際化支援：NLS字符集

• 資料庫建立時指定主字符集（如AL32UTF8）
• 可指定輔助字符集（National Character Set）
• NCHAR/NVARCHAR2使用輔助字符集，適合多語言混存環境
• Oracle 9i+支援以「字元」而非「位元組」定義N類型長度

二、數值資料類型

NUMBER精度與刻度完整語法

NUMBER[(precision[, scale])]
-- precision: 有效數字總位數 (1-38)，預設38
-- scale: 小數點右邊位數 (-84 到 127)，預設0

日期時間類型

日期格式控制： NLS_DATE_FORMAT參數、ALTER SESSION SET NLS_DATE_FORMAT、TO_DATE/TO_CHAR格式模型。

三、其他特殊資料類型

二進制與原始資料

ROWID與UROWID

• ROWID：物理地址偽欄，最快單行存取路徑
• 特性：匯出/匯入、分區移動後會變更，不可長期儲存依賴
• UROWID：通用ROWID，支援索引組織表、外部表

大型物件（LOB）家族

LOB可儲存於資料庫內（內部LOB）或指向外部檔案（BFILE）。Oracle 9i+提供LONG→LOB自動轉換功能。

新世代類型（Oracle 9i+）

• XMLType：原生XML文檔儲存、查詢、索引（XPath、XQuery）
• 用戶自定義類型：物件型別、集合型別（VARRAY、Nested Table）
• AnyType/AnyData/AnyDataSet：動態型別系統，支援任意資料結構封裝

四、類型轉換與操作規則

隱式轉換規則

Oracle依上下文自動轉換（如數字||字串→字串，字串比較數字→數字）。風險：隱式轉換導致索引失效、效能損耗、結果不可預期。

顯式轉換函數

TO_CHAR(number|date, fmt)   -- 轉字串
TO_NUMBER(char, fmt)        -- 轉數字
TO_DATE(char, fmt)          -- 轉日期
CAST(expr AS type)          -- ANSI標準轉換

字串連接

-- 連接運算子 ||
SELECT 'Hello' || ' ' || 'World' FROM dual;  -- Hello World
-- 數字自動轉字串參與連接
SELECT 'Salary: ' || 50000 FROM dual;  -- Salary: 50000

NULL處理鐵律

• NULL ≠ 空字串 ≠ 0 ≠ 任何值
• 任何與NULL運算/比較結果為UNKNOWN（非TRUE/FALSE）
• 檢測NULL唯一合法語法：IS NULL / IS NOT NULL
• NVL(expr1, expr2)、COALESCE、NULLIF、NVL2處理空值

五、選型決策矩陣

總結

資料類型選型是資料庫設計的基石。核心原則：字元用VARCHAR2、數值用NUMBER(p,s)、大物件用LOB、日期用TIMESTAMP系列、國際化用N類型、特殊需求用XMLType/物件型別。嚴禁使用LONG/LONG RAW，嚴謹處理NULL語意，顯式控制類型轉換，建立統一的命名與選型規範。