前言
正確選擇資料型別是資料庫設計的基石，影響儲存效率、查詢效能、應用程式相容性。Oracle 提供豐富的內建型別，本文系統分類 22 種核心型別，說明特性、適用場景、常見陷阱與選型建議。
一、字元型別
| 型別 | 最大長度 | 特性 | 適用場景 |
|---|
| CHAR(n) | 2000 bytes | 固定長度，不足右補空格 | 固定長代碼 (國別碼、狀態碼)、極短固定欄位 (Y/N) |
| VARCHAR2(n) | 4000 bytes | 可變長度，不補空格，**首選** | 一般文字欄位 (姓名、地址、描述) |
| VARCHAR(n) | 4000 bytes | 同 VARCHAR2，保留相容性，**勿用** | — |
| NCHAR(n) | 2000 bytes | Unicode 固定長度，按字元計長 | 多語言固定代碼 |
| NVARCHAR2(n) | 4000 bytes | Unicode 可變長度，按字元計長 | 多語言文字欄位 |
| CLOB | (4GB-1) × DB_BLOCK_SIZE | 大字元物件，支援隨機讀寫 | 長文本、JSON、XML、日誌 |
| NCLOB | 同上 | Unicode CLOB | 多語言大文本 |
| LONG | 2 GB | **已棄用**，諸多限制 (不支援索引、函數、分散式) | 遷移至 CLOB |
字元語義 vs 位元組語義
-- 位元組語義 (預設)
CREATE TABLE t1 (c1 VARCHAR2(100)); -- 100 bytes
-- 字元語義 (多位元組字元集如 AL32UTF8 必須使用)
CREATE TABLE t2 (c1 VARCHAR2(100 CHAR)); -- 100 characters
CREATE TABLE t3 (c1 VARCHAR2(100 BYTE)); -- 明確指定 bytes
建議： 多語言環境一律使用 CHAR 語義，避免中文 3 bytes 導致截斷。
二、數值型別
| 型別 | 精度/範圍 | 儲存 | 適用場景 |
|---|
| NUMBER(p,s) | p=1~38, s=-84~127 | 可變長 (1~22 bytes) | **通用首選**，金融、科學計算、整數、浮點 |
| BINARY_FLOAT | 32-bit IEEE 754 | 5 bytes (含標頭) | 單精度科學計算、圖形、近似值可接受場景 |
| BINARY_DOUBLE | 64-bit IEEE 754 | 9 bytes | 雙精度科學計算、高精度近似值 |
NUMBER 精度與範圍範例
NUMBER(5) -- 整數 5 位 ( -99999 ~ 99999 )
NUMBER(5,2) -- 共 5 位，小數 2 位 ( -999.99 ~ 999.99 )
NUMBER(*, 2) -- 精度不限，小數固定 2 位
NUMBER -- 最大精度 38，範圍最大
內部儲存： 科學記號法，1 byte 指數 + 最多 20 bytes 尾數。用 VSIZE() 或 DUMP() 檢查實際佔用。
三、日期時間型別
| 型別 | 精度 | 時區 | 版本 | 適用場景 |
|---|
| DATE | 秒 | 無 | 所有 | 一般業務日期、不需時區、相容舊系統 |
| TIMESTAMP | 奈秒 (9 位) | 無 | 9i+ | 高精度時間戳記、不需時區 |
| TIMESTAMP WITH TIME ZONE | 奈秒 | 有 (儲存位移) | 9i+ | 跨時區應用、全球化系統 |
| TIMESTAMP WITH LOCAL TIME ZONE | 奈秒 | 正規化為 DB 時區，查詢轉為 Session 時區 | 9i+ | 多時區用戶、自動轉換顯示 |
日期格式化
-- NLS_DATE_FORMAT 影響 DATE 顯示
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
-- TIMESTAMP 格式參數
NLS_TIMESTAMP_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
四、間隔型別
| 型別 | 組成 | 儲存 | 適用場景 |
|---|
| INTERVAL YEAR TO MONTH | 年、月 | 5 bytes | 年齡、合約期限、保固年月 |
| INTERVAL DAY TO SECOND | 日、時、分、秒(奈秒) | 11 bytes | 持續時間、處理時長、SLA 計算 |
字面常數語法
INTERVAL '123-2' YEAR(3) TO MONTH -- 123 年 2 月
INTERVAL '4 5:12:10.222' DAY TO SECOND(3) -- 4 天 5:12:10.222
INTERVAL '300' MONTH(3) -- 300 月
五、二進位與大物件
| 型別 | 最大長度 | 特性 | 適用場景 |
|---|
| RAW(n) | 2000 bytes | 二進位，不做字元集轉換 | 小型二進位 (雜湊值、加密金鑰、GUID) |
| BLOB | (4GB-1) × DB_BLOCK_SIZE | 大二進位物件 | 圖片、文件、影音、二進位序列化物件 |
| LONG RAW | 2 GB | **已棄用** | 遷移至 BLOB |
| BFILE | 4 GB (OS 檔案) | 唯讀，指向外部檔案 | 超大檔案、唯讀媒體、外部資料湖 |
六、特殊型別
| 型別 | 用途 |
|---|
| ROWID | 實體列地址 (Base64 編碼 18 字元)，最快單列存取路徑 |
| UROWID | 通用列地址 (支援 Index-Organized Table、外部表) |
七、型別選型決策樹
- 是文字？ → 固定長且極短 →
CHAR / NCHAR；否 → VARCHAR2 / NVARCHAR2；超過 4000 → CLOB / NCLOB。
- 是數字？ → 需精確計算 (金融) →
NUMBER；科學近似值 → BINARY_FLOAT/DOUBLE。
- 是日期？ → 需時區 →
TIMESTAMP WITH TIME ZONE / LOCAL TIME ZONE；需奈秒精度 → TIMESTAMP；一般業務 → DATE。
- 是二進位？ → 小 →
RAW；大 → BLOB；外部唯讀 → BFILE。
- 是間隔？ → 年月 →
INTERVAL YEAR TO MONTH；日時分秒 → INTERVAL DAY TO SECOND。
八、常見陷阱與避坑指南
| 陷阱 | 後果 | 正確做法 |
|---|
| 用 CHAR 存可變長文字 | 空間浪費、比較需 TRIM、索引效能差 | 改用 VARCHAR2 |
| 用 NUMBER 存整數主鍵未指定精度 | 隱含轉換、應用層型別不匹配 | 用 NUMBER(10) 或 NUMBER(19) 對應 int/long |
| DATE 存跨時區時間 | 時區轉換錯誤、夏令時問題 | 用 TIMESTAMP WITH TIME ZONE |
| 用 LONG/LONG RAW 新開發 | 功能受限、無法索引、遷移困難 | 一律用 CLOB/BLOB |
| 字元集不一致導致 CONVERT 損壞資料 | 資料遺失、亂碼 | 資料庫統一 AL32UTF8，應用層處理編碼 |
| BFILE 當作一般欄位更新 | BFILE 唯讀，無法 INSERT/UPDATE 內容 | 只存路徑，內容由外部系統管理 |
九、壓縮與儲存優化 (11g+)
-- 表壓縮 (僅限直接路徑載入: CTAS, INSERT /*+ APPEND */, SQL*Loader Direct)
CREATE TABLE t_compressed (id NUMBER, data VARCHAR2(2000)) COMPRESS FOR OLTP;
-- 現有表啟用壓縮
ALTER TABLE t_existing MOVE COMPRESS FOR OLTP;
-- 混合欄位壓縮 (Hybrid Columnar Compression, HCC) - Exadata/Storage 專用
CREATE TABLE t_hcc COMPRESS FOR ARCHIVE HIGH AS SELECT * FROM big_table;
總結：VARCHAR2、NUMBER、DATE/TIMESTAMP WITH TIME ZONE、CLOB/BLOB 是 90% 場景的核心型別組合。設計時優先考慮「業務語義精確表達」與「未來擴充彈性」，定期用 DBMS_METADATA 匯出 DDL 檢視型別分布。
