Oracle PL/SQL 存儲過程開發 6 大常見陷阱與最佳實踐

前言

PL/SQL 存儲過程是 Oracle 業務邏輯封裝的核心，但語法細節與執行機制若掌握不當，極易導致編譯錯誤、執行期異常或效能問題。本文歸納開發中 6 大高頻問題，提供原因分析與標準解法。

陷阱 1：資料表別名不可使用 AS 關鍵字

-- 正確
SELECT a.appname FROM appinfo a;

-- 錯誤 ORA-00933
SELECT a.appname FROM appinfo AS a;

原因：Oracle SQL 標準不支援 `TABLE AS ALIAS` 語法，`AS` 僅用於欄位別名 (`SELECT col AS alias`)。直接寫 `table alias` 即可。

陷阱 2：SELECT INTO 子句為強制要求

-- 正確：單行查詢必須導向變數
SELECT af.keynode INTO v_kn FROM appfoundation af WHERE af.appid = v_aid;

-- 錯誤 PLS-00428
SELECT af.keynode FROM appfoundation af WHERE af.appid = v_aid;

規則：PL/SQL 區塊中執行 `SELECT` 若非游標 (`OPEN ... FOR`)，必須搭配 `INTO` 將結果綁定至變數。多行結果需使用游標或 `BULK COLLECT`。

陷阱 3：SELECT INTO 找不到資料會拋出 NO_DATA_FOUND

-- 風險寫法
SELECT col INTO v_var FROM tbl WHERE id = v_id;  -- 若無此 id，直接報錯

-- 安全模式：先檢查存在性
DECLARE
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt FROM tbl WHERE id = v_id;
  IF v_cnt > 0 THEN
    SELECT col INTO v_var FROM tbl WHERE id = v_id;
  ELSE
    v_var := NULL;  -- 或預設值
  END IF;
END;

進階：也可用 `EXCEPTION WHEN NO_DATA_FOUND THEN ...` 捕獲處理，但「先查 COUNT」在高併發下可能出現「查到有資料，實際 SELECT 時又被刪除」的競態條件，生產環境建議用例外處理或 `MERGE`/`UPSERT` 模式。

陷阱 4：別名與欄位名稱重名導致執行期錯誤

-- 正確：別名 kn 與欄位 keynode 不衝突
SELECT keynode INTO kn FROM appfoundation WHERE appid = aid;

-- 錯誤 ORA-01422：別名 appid 與欄位 appid 同名，導致 WHERE 條件解析混淆
SELECT af.keynode INTO kn FROM appfoundation af WHERE af.appid = appid AND af.foundationid = foundationid;

根因：當別名與欄位同名時，Oracle 無法區分 `WHERE` 條件中引用的是變數還是欄位，導致條件失效、掃描全表、回傳多行觸發 `TOO_MANY_ROWS` (ORA-01422)。鐵律：變數/參數命名加前綴 (v_, p_)，絕不與欄位名重疊。

陷阱 5：聚合函數回傳 NULL 覆蓋變數預設值

CREATE TABLE A (
  id VARCHAR2(50) PRIMARY KEY,
  vcount NUMBER(8) NOT NULL,
  bid VARCHAR2(50) NOT NULL
);

-- 存儲過程片段
DECLARE
  fcount NUMBER(8) := 0;  -- 有預設值
BEGIN
  SELECT SUM(vcount) INTO fcount FROM A WHERE bid = 'xxxxxx';
  -- 若無符合 bid 記錄，SUM 回傳 NULL，fcount 變為 NULL，後續運算失敗
  IF fcount IS NULL THEN fcount := 0; END IF;  -- 必須額外判斷
END;

最佳實踐：聚合查詢 (`SUM`, `AVG`, `MAX`...) 無資料時回傳 `NULL` 而非 `0`。建議查詢後立即 `NVL(fcount, 0)` 或 `COALESCE` 處理，或在 SQL 中寫 `SELECT NVL(SUM(vcount),0) INTO fcount ...`。

陷阱 6：Hibernate/JPA 呼叫 Oracle Stored Procedure 範本

getHibernateTemplate().execute(new HibernateCallback() {
    public Object doInHibernate(Session session) throws HibernateException, SQLException {
        Connection conn = session.connection();  // Hibernate 3/4
        // Hibernate 5+ 建議: session.doWork(connection -> { ... });
        CallableStatement cs = conn.prepareCall("{call modify_app_pnumber_remain(?)}");
        try {
            cs.setString(1, foundationId);
            cs.execute();
        } finally {
            cs.close();
        }
        return null;
    }
});

關鍵點：

• 使用 JDBC Escape Syntax `{call proc_name(?,?)}` 標準化呼叫。
• `CallableStatement` 需手動關閉，避免游標洩漏。
• 若 Procedure 有 `OUT` 參數，需 `cs.registerOutParameter(index, Types.VARCHAR)` 並 `cs.getString(index)` 取值。
• 交易管理由 Spring/Hibernate 管理，存儲過程內部**不要**執行 `COMMIT/ROLLBACK`，除非為自主交易 (`PRAGMA AUTONOMOUS_TRANSACTION`)。

總結檢查清單