萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 存儲過程動態查詢實作:以 REF CURSOR 返回結果集

需求背景

在 Oracle 存儲過程中,常需根據傳入參數動態構建查詢條件、表名或欄位,並將結果集返回給呼叫端(如 Java、.NET、報表工具)。傳統靜態游標無法滿足此類動態需求,REF CURSOR(弱型別游標) 是最佳解決方案。

核心程式碼範例

CREATE OR REPLACE PROCEDURE zxm_sb_gz_get (
  p_table   IN  VARCHAR2,        -- 動態表名
  p_name    IN  VARCHAR2,        -- 動態條件欄位
  p_value   IN  VARCHAR2,        -- 條件值
  out_cursor OUT SYS_REFCURSOR   -- 返回弱型別游標
) AS
  v_sql   VARCHAR2(2000);
BEGIN
  -- 動態構建 SQL,防止注入請使用 DBMS_ASSERT 或繫結變數
  v_sql := 'SELECT * FROM ' || p_table || 
           ' WHERE ' || p_name || ' = :b1';
           
  -- 開啟游標並繫結參數
  OPEN out_cursor FOR v_sql USING p_value;
END;

關鍵技術點解析

1. 弱型別 REF CURSOR (SYS_REFCURSOR)

Oracle 9i 起提供 SYS_REFCURSOR 內建型別,無需在套件中預先定義強型別游標,極大簡化動態查詢開發。

2. USING 子句繫結變數

OPEN ... FOR ... USING 語法將動態 SQL 中的佔位符(如 :b1)與 PL/SQL 變數繫結,兼具效能與安全性,避免 SQL 注入風險。

3. 動態表名/欄位名的安全處理

表名、欄位名無法使用繫結變數,必須拼接字串。生產環境務必:

  • 使用 DBMS_ASSERT.ENQUOTE_NAME 驗證物件名稱;
  • 建立白名單機制,僅允許預定義的表/欄位;
  • 記錄稽核日志,追蹤異常呼叫。

呼叫端範例 (Java/JDBC)

CallableStatement cs = conn.prepareCall("{call zxm_sb_gz_get(?, ?, ?, ?)}");
cs.setString(1, "EMPLOYEES");
cs.setString(2, "DEPARTMENT_ID");
cs.setString(3, "60");
cs.registerOutParameter(4, OracleTypes.CURSOR);
cs.execute();

ResultSet rs = (ResultSet) cs.getObject(4);
while (rs.next()) {
  // 處理結果集
}
rs.close();
cs.close();

進階擴展:多結果集與分頁

若需返回多個結果集,可定義多個 OUT 參數皆為 SYS_REFCURSOR。分頁查詢建議在動態 SQL 中直接嵌入 OFFSET ... FETCH NEXT (12c+) 或傳統 ROWNUM 寫法,避免將大量資料傳至中介層再過濾。

常見錯誤與避坑指南

錯誤現象原因解決方案
ORA-01001: invalid cursor游標未開啟或已關閉檢查 OPEN 邏輯,確保所有路徑皆開啟游標
ORA-00933: SQL command not properly ended動態 SQL 語法錯誤輸出 v_sql 除錯,注意單引號逸出與空白字元
結果集為空但無錯誤條件值型別不匹配確認繫結變數型別與表欄位一致,必要時 TO_CHAR/TO_DATE

總結

善用 SYS_REFCURSOR 搭配動態 SQL 與繫結變數,能構建靈活、安全、高效的通用查詢介面,是 Oracle 存儲過程開發的核心技能之一。

copyright © 萬盛學電腦網 all rights reserved