Oracle TO_CHAR 函式進階陷阱與最佳實務：數值、日期、SELECT INTO 與 ROWNUM 注意事項

TO_CHAR 功能強大，但數值格式化、日期轉數值、SELECT INTO 例外處理、ROWNUM 排序誤區、NULL 判斷邏輯等細節極易踩坑。本文彙整實戰常見錯誤與標準解法。

數值格式化三大陷阱

1. 小數點前導零消失

TO_CHAR(0.123)          -- '.123' (遺失前導 0)
TO_CHAR(0.123, '0.999') -- '0.123' (正確)

2. FM 修飾詞殘留小數點

TO_CHAR(100, 'FM999.999')  -- '100.' (整數仍留小數點)
-- 解法：RTRIM(TO_CHAR(val, 'FM999.999'), '.')

3. 四捨五入非截斷

TO_CHAR(1.999, '0.99')  -- '2.00' (四捨五入)
-- 需截斷請先 TRUNC：TO_CHAR(TRUNC(1.999,2), '0.99')

日期轉數值再格式化技巧

Oracle 允許將日期轉為數值（如 TO_NUMBER(TO_CHAR(dt, 'J')) 取 Julian Day），再套用數值格式，可實現特殊輸出需求。

SELECT INTO 必須處理的兩大例外

DECLARE
  v_sal employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_sal FROM employees WHERE employee_id = 99999;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- 查無資料
    v_sal := 0;
  WHEN TOO_MANY_ROWS THEN
    -- 多筆資料
    RAISE_APPLICATION_ERROR(-20001, '資料重複');
END;

建議： 改用游標 FOR LOOP 或 BULK COLLECT，天然處理 0/N 筆資料，避免例外開銷。

ROWNUM 不支援排序與跳頁誤區

-- 錯誤：先 ROWNUM 再 ORDER BY
SELECT * FROM (SELECT * FROM emp ORDER BY sal DESC) WHERE ROWNUM <= 10; -- 正確

-- 錯誤：ROWNUM = 2 取第二筆
-- ROWNUM 從 1 開始遞增，WHERE ROWNUM = 2 永無結果
-- 正確分頁寫法：
SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (SELECT * FROM emp ORDER BY sal) a
  WHERE ROWNUM <= 20
) WHERE rn > 10;

NULL 判斷必須用 IS NULL / NVL

-- 錯誤
IF v_var = NULL THEN ... END IF;  -- 永遠為 UNKNOWN，不進入
WHERE col = NULL  -- 不會命中任何列

-- 正確
IF v_var IS NULL THEN ... END IF;
WHERE col IS NULL
WHERE NVL(col, 'DEFAULT') = 'DEFAULT'

預存程序參數命名避開資料表欄位名

-- 錯誤：參數名 = 欄位名
CREATE PROCEDURE proc(p_ename VARCHAR2) IS
BEGIN
  SELECT * FROM emp WHERE ename = p_ename; -- Oracle 視 p_ename 為欄位而非參數
END;

-- 正確：加前綴或不同命名
CREATE PROCEDURE proc(p_in_ename VARCHAR2) IS ...

總結檢查清單

• [ ] 數值格式化用 0.999 強制前導零，RTRIM(..., '.') 處理整數小數點。
• [ ] SELECT INTO 包裹 NO_DATA_FOUND/TOO_MANY_ROWS 或改用游標。
• [ ] 分頁查詢採用雙層 ROWNUM 子查詢寫法。
• [ ] NULL 比較一律 IS NULL/NVL。
• [ ] 程序參數加 p_in_/p_out_ 前綴避免名稱衝突。

細節決定成敗，將這些陷阱內化為編碼習慣，能減少 80% 以上的 PL/SQL 執行期錯誤。