Oracle 數字轉字符串丟失小數點前導零問題的 TO_CHAR 格式化解決方案

問題現象

SELECT 0.35 FROM dual;  -- 客戶端顯示 .35 (丟失前導 0)
-- 或
SELECT TO_CHAR(0.35) FROM dual;  -- 同樣輸出 .35

數據庫內部存儲正確，僅為**顯示/字符串轉換**層面的格式問題。

標準解決方案：TO_CHAR 格式模型

SELECT TO_CHAR(0.338, 'FM9999999990.00') FROM dual;
-- 結果: 0.34  (四捨五入保留 2 位小數，保留前導 0)

格式模型解析

FM9999999990.00 表示：整數部最多 10 位 (最後一位用 0 強制顯示)，小數部固定 2 位，無多餘空格。

常用格式模板

-- 金額/價格: 保留 2 位小數，千分位分隔
TO_CHAR(amount, 'FM999,999,990.00')

-- 百分比: 乘 100 後保留 2 位
TO_CHAR(ratio*100, 'FM990.00') || '%'

-- 科學計數/高精度
TO_CHAR(val, 'FM9999999990.999999')

-- 整數補零 (如單號)
TO_CHAR(seq, 'FM0000000000')

補充：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 tmp1.*, tmp2.*
FROM tmp1 JOIN tmp2 ON tmp1.a = tmp2.a;

• WITH 子句 (CTE) 簡化複雜查詢結構，避免重複衍生表。
• Oracle 11g+ 支持 MATERIALIZE Hint 強制物化 (WITH tmp AS (SELECT /*+ MATERIALIZE */ ...))。
• 遞歸 CTE (WITH RECURSIVE) 適用於層級/圖遍歷 (11gR2+)。

客戶端顯示設置 (NLS_NUMERIC_CHARACTERS)

若需全局影響 SQL*Plus/PLSQL Developer 等工具顯示：

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';  -- 小數點、千分位
-- 或環境變量
export NLS_NUMERIC_CHARACTERS='.,'

但**應用層建議顯式 TO_CHAR**，避免依賴 Session 參數導致跨環境不一致。