您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 資料型別轉換完全指南：TO_NUMBER、TO_CHAR、TO_DATE 實戰與避坑

編輯：Oracle 資料庫基礎

三大核心轉換函數

Oracle 提供顯式轉換函數，避免隱式轉換導致的計畫不穩定、NLS 敏感或效能損耗。

1. TO_NUMBER — 字串 → 數值

SELECT TO_NUMBER('8897') FROM dual;  -- 回傳 8897 (NUMBER)

輸入必須為合法數字字面值（可含正負號、科學符號、群組/小數分隔符）。
格式模型可控制解析細節：TO_NUMBER('1,234.56', '9,999.99')。
非法輸入拋 ORA-01722。

2. TO_CHAR — 任意型別 → 字串

SELECT TO_CHAR(8897) FROM dual;           -- '8897'
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;  -- 日期格式化
SELECT TO_CHAR(12345.67, 'FM99,999.00') FROM dual;          -- 數值格式化

日期格式元素：YYYY/MM/DD HH24:MI:SS、MONTH、DAY、AM/PM 等。
數值格式元素：9 (數字)、0 (前導零)、FM (抑制空白)、, . (分隔符)。
NLS 參數可覆寫預設語言/貨幣/日期格式：TO_CHAR(dt, 'fmt', 'NLS_DATE_LANGUAGE=AMERICAN')。

3. TO_DATE — 字串 → 日期

SELECT TO_DATE('12-DEC-02') FROM dual;                    -- 依 NLS_DATE_FORMAT 解析
SELECT TO_DATE('20021227', 'YYYYMMDD') FROM dual;         -- 明確格式模型
SELECT TO_DATE('20021236', 'YYYYMMDD') FROM dual;         -- ORA-01847: day of month must be between 1 and 31
SELECT TO_DATE('baddate') FROM dual;                      -- ORA-01861: literal does not match format string

常見陷阱與最佳實踐

場景	錯誤做法	正確做法
WHERE 子句隱式轉換	`WHERE id = '123'` (id 為 NUMBER)	`WHERE id = 123` 或 `WHERE TO_CHAR(id) = '123'`
日期字面值	`WHERE dt = '2024-01-01'`	`WHERE dt = DATE '2024-01-01'` (ANSI 標準) 或 `TO_DATE`
跨環境部署	依賴 `NLS_DATE_FORMAT`	所有 TO_DATE/TO_CHAR 明確指定格式模型
效能	索引欄位包裹函數 `TO_CHAR(col) = 'val'`	改寫為 `col = TO_DATE('val', 'fmt')` 使用索引

進階：TIMESTAMP 與時區轉換

-- TIMESTAMP WITH TIME ZONE
SELECT TO_TIMESTAMP_TZ('2024-01-01 12:00:00 +08:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM') FROM dual;

-- 時區轉換
SELECT FROM_TZ(CAST(TO_DATE('2024-01-01 12:00', 'YYYY-MM-DD HH24:MI') AS TIMESTAMP), 'UTC')
       AT TIME ZONE 'Asia/Taipei' FROM dual;