Oracle 預存程序開發六大常見陷阱與解決方案

前言

PL/SQL 預存程序開發中，語法細節與執行期行為差異常導致難以發現的錯誤。本文整理六大實戰陷阱與應對技巧。

1. 表格別名不可使用 AS

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

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

Oracle 不支援 AS 關鍵字定義表格別名，直接空白隔開即可。

2. SELECT INTO 子句不可省略

-- 正確
SELECT af.keynode INTO kn FROM appfoundation af WHERE af.appid = aid;

-- 錯誤：PLS-00428: an INTO clause is expected
SELECT af.keynode FROM appfoundation af WHERE af.appid = aid;

在 PL/SQL 區塊中，SELECT 必須搭配 INTO 將結果存入變數，否則改用游標。

3. SELECT INTO 需確保有且僅有一筆資料

若查無資料會拋出 NO_DATA_FOUND 例外；若超過一筆拋出 TOO_MANY_ROWS。

防禦寫法：

DECLARE v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt FROM appfoundation WHERE appid = aid;
  IF v_cnt = 1 THEN
    SELECT keynode INTO kn FROM appfoundation WHERE appid = aid;
  ELSE
    -- 處理 0 或多筆情況
  END IF;
END;

4. 別名不可與欄位名稱相同

-- 正確
SELECT keynode INTO kn FROM appfoundation WHERE appid = aid;

-- 執行期錯誤 ORA-01422: exact fetch returns more than requested number of rows
SELECT af.keynode INTO kn FROM appfoundation af WHERE af.appid = appid AND af.foundationid = foundationid;

當別名與參數/欄位同名，WHERE 條件 af.appid = appid 被解讀為 af.appid = af.appid 永真條件，導致回傳多筆。

規則：別名、變數、參數命名需與資料表欄位名稱區別（如加前綴 p_、v_）。

5. 聚合函數回傳 NULL 需額外處理

SELECT SUM(vcount) INTO fcount FROM a WHERE bid = 'xxxxxx';
-- 若無符合資料，fcount 為 NULL，即使宣告時預設 0 也會被覆蓋

-- 修正
IF fcount IS NULL THEN fcount := 0; END IF;

6. Hibernate 呼叫 Oracle 預存程序範例

getHibernateTemplate().execute(new HibernateCallback() {
  public Object doInHibernate(Session session) throws HibernateException, SQLException {
    CallableStatement cs = session.connection().prepareCall("{call modifyappnumber_remain(?)}");
    cs.setString(1, foundationid);
    cs.execute();
    return null;
  }
});

注意：Oracle 預存程序參數以位置繫結（?），不支援命名參數。

小結

命名規範、防禦性檢查、理解 PL/SQL 執行語意，是寫出健壯預存程序的關鍵。