Oracle 數值格式化實戰：TO_CHAR 解決前導零遺失、科學記號與貨幣顯示

問題現象

數值型欄位 (NUMBER/BINARY_FLOAT) 經由用戶端工具或隱含轉換顯示時，小於 1 的小數會遺失前導零：

SELECT 0.35 FROM dual;  -- 顯示 .35
SELECT .001 FROM dual;  -- 顯示 .001

根本原因

• Oracle 內部儲存為科學記號格式 (尾數 + 指數)
• 預設格式模型 TM (Text Minimum) 省略前導零
• 客戶端工具 (SQL*Plus、JDBC、ODBC) 各自有預設格式

TO_CHAR 格式模型完整指南

常用格式範例

-- 固定 2 位小數，保留前導零
TO_CHAR(0.35, 'FM9999999990.00')     -- '0.35'
TO_CHAR(1234.5, 'FM9999999990.00')   -- '1234.50'

-- 貨幣格式 (含千分位、貨幣符號)
TO_CHAR(1234567.89, 'FML999G999G999D00')  -- 'NT$1,234,567.89' (依 NLS)

-- 百分比
TO_CHAR(0.1234, 'FM999.99') || '%'   -- '12.34%'

-- 科學記號
TO_CHAR(0.000123, 'FM9.99EEEE')      -- '1.23E-04'

-- 前導零補齊 6 位
TO_CHAR(42, 'FM000000')              -- '000042'

WITH 子句 (CTE) 實戰範例

WITH tmp1 AS (
  SELECT 1 AS a, 2 AS b FROM dual UNION ALL
  SELECT 1, 3 FROM dual UNION ALL
  SELECT 1, 4 FROM dual
),
tmp2 AS (
  SELECT 1 AS a, 2 AS b FROM dual UNION ALL
  SELECT 1, 3 FROM dual UNION ALL
  SELECT 2, 4 FROM dual
)
SELECT t1.*, t2.*
FROM tmp1 t1 JOIN tmp2 t2 ON t1.a = t2.a;

優點：可讀性高、避免重複子查詢、優化器可選擇 Materialize/Inline。

客戶端層面修正

最佳實踐原則

1. 資料層儲存精確值：使用 NUMBER(p,s) 明確精度
2. 應用層格式化：DB 傳回原始值，前端依 Locale 格式化
3. 報表/匯出才用 TO_CHAR：避免隱含轉換導致計畫失效
4. 統一 NLS 設定：資料庫/Session/客戶端三層一致