Oracle 數值格式化四大方案：TO_CHAR、CASE、COLUMN FORMAT 與 TRIM 實戰比較

需求場景

將數值欄位格式化為「固定兩位小數」，並妥善處理：0 → 0.00、1234 → 1,234.00、1234.5 → 1,234.50、.5 → 0.50 等邊界情況。

方案一：TO_CHAR + FM 格式模型 (推薦)

TO_CHAR(ROUND(salary, 2), 'FM99999999999999.00')

• FM：Fill Mode，抑制前導/尾隨空白
• 9：數字位，前導零不顯示
• 0：數字位，前導零顯示
• .00：強制兩位小數

缺點：salary=0 時顯示 .00 而非 0.00

修正版 (處理 0 值)

DECODE(salary, 0, '0.00', 
  TO_CHAR(ROUND(salary, 2), 'FM99999999999999.00'))

方案二：CASE WHEN 逐情況處理

CASE
  WHEN INSTR(TO_CHAR(salary), '.') < 1      THEN TO_CHAR(salary) || '.00'      -- 無小數點
  WHEN INSTR(TO_CHAR(salary), '.') + 1 = LENGTH(TO_CHAR(salary)) 
                                             THEN TO_CHAR(salary) || '0'       -- 單位小數
  ELSE TO_CHAR(ROUND(salary, 2))
END AS amount_format

• 邏輯直觀，無格式模型陷阱
• 缺點：SQL 冗長、效能稍差 (多次 TO_CHAR/INSTR)

方案三：SQL*Plus COLUMN FORMAT (僅限客戶端顯示)

COLUMN salary FORMAT L9999999999.99
-- 或
COLUMN salary FORMAT 999,999,999.99

• 優點：簡單、不修改 SQL
• 缺點：僅影響 SQL*Plus/SQLcl/SQL Developer 顯示，不影響應用程式取回的資料；Session 級生效，連線中斷失效；位數不足顯示 #######

方案四：TRIM + TO_CHAR (推薦替代方案)

TRIM(TO_CHAR(salary, '99999999999999.99'))
-- 或
LTRIM(TRIM(TO_CHAR(salary, '00000000000000.00')), '0')

• TRIM 移除格式模型產生的前導/尾隨空白
• 使用 9 而非 0 避免需額外 LTRIM('0')

缺點：同方案一，salary=0 顯示 .00

修正版

DECODE(salary, 0, '0.00',
  TRIM(TO_CHAR(salary, '99999999999999.99')))

方案比較總表

最佳實務建議

1. 應用層格式化：資料庫回傳 NUMBER，由 Java DecimalFormat、C# ToString("N2")、JS toFixed(2) 處理顯示，分離關注點
2. 資料庫層必須格式化：使用 方案一修正版 或 方案四修正版
3. 格式模型位數：9 的個數需 > 最大值位數，建議預留 14-16 位 (99999999999999)
4. 區域設定：跨國系統考慮 NLS_NUMERIC_CHARACTERS，明確指定 TO_CHAR(..., 'FM999G999D00', 'NLS_NUMERIC_CHARACTERS='',.''')

完整測試腳本

WITH test_data AS (
  SELECT 0 AS salary FROM dual UNION ALL
  SELECT 1234 FROM dual UNION ALL
  SELECT 1234.5 FROM dual UNION ALL
  SELECT 1234.56 FROM dual UNION ALL
  SELECT 1234.567 FROM dual UNION ALL
  SELECT .5 FROM dual UNION ALL
  SELECT -100.25 FROM dual
)
SELECT 
  salary,
  DECODE(salary, 0, '0.00', TO_CHAR(ROUND(salary,2), 'FM99999999999999.00')) AS fmt1,
  DECODE(salary, 0, '0.00', TRIM(TO_CHAR(salary, '99999999999999.99'))) AS fmt4
FROM test_data;

數值格式化看似簡單，邊界條件 (0、負數、超長、區域設定) 處理不當極易產生顯示錯誤。建立專案統一的格式化函數/工具類，集中管理格式邏輯，是避免「同一欄位在不同畫面顯示不一致」的關鍵。