您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 存儲過程開發避坑指南：別名、INTO、NULL、Hibernate 調用六大高頻錯誤

編輯：Oracle 資料庫基礎

PL/SQL 存儲過程六大經典陷阱與修正模式

從編譯通過到運行拋異常，Oracle 存儲過程開發中存在多個語法與語義陷阱。本文匯總六大高頻錯誤：表別名限制、SELECT INTO 強制性、無資料異常、別名衝突、聚合 NULL 處理、Hibernate CallableStatement 調用，提供標準修正模式與最佳實踐。

陷阱一：表別名不可使用 AS

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

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

原因：Oracle 解析器將 AS 視為別名關鍵字保留字，與 PL/SQL 宣告區 AS/IS 衝突。所有表別名直接跟在表名後，不加 AS。

陷阱二：SELECT 必須緊跟 INTO (非游標查詢)

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

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

規則：PL/SQL 靜態 SELECT 必須將結果導向變數/記錄，否則編譯失敗。動態 SQL 需 EXECUTE IMMEDIATE ... INTO。

陷阱三：SELECT INTO 要求精確單行，否則拋異常

情況	異常	錯誤碼
查無資料	NO_DATA_FOUND	ORA-01403
多行結果	TOO_MANY_ROWS	ORA-01422

-- 防禦性寫法
SELECT COUNT(*) INTO v_cnt FROM appfoundation WHERE appid = aid;
IF v_cnt = 1 THEN
  SELECT keynode INTO v_keynode FROM appfoundation WHERE appid = aid;
ELSIF v_cnt = 0 THEN
  v_keynode := NULL;  -- 或預設值
ELSE
  RAISE_APPLICATION_ERROR(-20001, '資料不唯一: appid=' || aid);
END IF;

陷阱四：別名與欄位名同名導致運行時多行錯誤

-- 運行時報 ORA-01422: exact fetch returns more than requested number of rows
SELECT af.keynode INTO v_kn FROM appfoundation af WHERE af.appid = appid AND af.foundationid = foundationid;

-- 正確：別名與參數/變數區分
SELECT af.keynode INTO v_kn FROM appfoundation af WHERE af.appid = p_appid AND af.foundationid = p_foundationid;

根因：WHERE af.appid = appid 中右側 appid 解析為表欄位而非參數，導致條件失效、全表掃描、多行匹配。

命名規範：參數前綴 p_、變數 v_、常數 c_、游標 c_、記錄 r_，避免與欄位名衝突。

陷阱五：聚合函數無資料返回 NULL 覆蓋預設值

v_fcount NUMBER(8) := 0;  -- 預設 0

SELECT SUM(vcount) INTO v_fcount FROM a WHERE bid = 'xxxxxx';
-- 若無匹配列：v_fcount 變為 NULL，而非 0！

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

原理：SQL 聚合函數對空集合返回 NULL (ANSI 標準)，INTO 直接覆蓋 PL/SQL 變數預設值。NVL(SUM(...),0) 在 SQL 層處理更高效。

陷阱六：Hibernate 調用存儲過程 CallableStatement 模式

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

關鍵點：

大括號語法 {call proc_name(?)} 為 JDBC 標準逃逸序列，跨資料庫可移植
session.connection() 獲取底層 java.sql.Connection (Hibernate 3/4)，Hibernate 5+ 建議 session.doWork() 或 session.doReturningWork()
OUT 參數需 cs.registerOutParameter(index, Types.VARCHAR) 並 cs.getString(index) 取值
交易邊界：Hibernate 管理交易，存儲過程內 COMMIT/ROLLBACK 可能破壞交易一致性，建議由 Hibernate 控制