Oracle 開發常見錯誤 8 類與最佳實踐修正

1. INSERT 缺欄位清單

-- 錯誤：依賴表結構順序
INSERT INTO t1 VALUES (...);

-- 正確：顯式指定欄位
INSERT INTO t1 (col1, col2, col3) VALUES (v1, v2, v3);

防止表結構變更導致插入錯誤或資料錯位。

2. TO_CHAR 格式模式錯誤

-- 錯誤：HH 為 12 小時制，MI 分，SS 秒
TO_CHAR(sysdate, 'YYYYMMDDHHMMSS')

-- 正確：HH24 24 小時制
TO_CHAR(sysdate, 'YYYYMMDDHH24MISS')

3. COUNT(*) 不會觸發 NO_DATA_FOUND

-- 錯誤：以為無資料會拋出異常
SELECT COUNT(*) INTO v_cnt FROM t1 WHERE c1 = ?;
IF v_cnt = 0 THEN ...  -- 正確判斷

`SELECT ... INTO` 只有在查詢**零行**時才拋 `NO_DATA_FOUND`，聚合函數永遠回傳一行。

4. 回傳值布林旗標管理

-- 不佳：預設 true，處處檢查設 false
result := TRUE;
IF ... THEN result := FALSE; END IF;
IF ... THEN result := FALSE; END IF;
-- 易漏設、邏輯分散

-- 最佳：預設 false，成功路徑統一設 true
result := FALSE;
-- 業務邏輯...
result := TRUE;  -- 僅在正常結束處設一次
EXCEPTION WHEN OTHERS THEN result := FALSE; RAISE;

5. 查無資料不回傳 NULL 而是拋異常

-- 錯誤：以為變數會變 NULL/0
SELECT c1, c2 INTO v_c1, v_c2 FROM t1 WHERE c3 = v_c3;
IF v_c1 = 0 OR v_c2 = 0 THEN ...  -- 實際會拋 NO_DATA_FOUND

-- 正確：捕獲異常
BEGIN
  SELECT c1, c2 INTO v_c1, v_c2 FROM t1 WHERE c3 = v_c3;
EXCEPTION WHEN NO_DATA_FOUND THEN
  v_c1 := 0; v_c2 := 0;  -- 或其他預設值
END;

6. 動態 SQL 字串拼接缺單引號導致索引失效

-- 錯誤：字符欄位未加引號
'... WHERE c1 = ' || v_c1;
-- Oracle 內部轉換為 to_char，導致隱式轉換，索引失效

-- 正確：手動加引號
'... WHERE c1 = ' || CHR(39) || v_c1 || CHR(39);
-- 或使用繫結變數 (最佳)
'... WHERE c1 = :b1' USING v_c1;

7. 存在性檢查未限制 ROWNUM

-- 低效：全表掃描計數
SELECT COUNT(*) INTO v_cnt FROM t1 WHERE c1 = v_c1;

-- 高效：只要存在一筆即停止
SELECT COUNT(*) INTO v_cnt FROM t1 WHERE c1 = v_c1 AND ROWNUM < 2;

8. EXISTS 子查詢選 * 浪費資源

-- 低效
WHERE EXISTS (SELECT * FROM t1 WHERE ...)

-- 標準寫法：選常數
WHERE EXISTS (SELECT 'X' FROM t1 WHERE ...)

9. RAISE_APPLICATION_ERROR 錯誤碼重複

-- 不佳：全部用 -20000
RAISE_APPLICATION_ERROR(-20000, 'msg1');
RAISE_APPLICATION_ERROR(-20000, 'msg2');

-- 規範：-20000 至 -20999 分配不同業務錯誤
RAISE_APPLICATION_ERROR(-20001, '驗證失敗');
RAISE_APPLICATION_ERROR(-20002, '權限不足');
RAISE_APPLICATION_ERROR(-20003, '資料衝突');

總結檢查清單