在 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;
Oracle 9i 起提供 SYS_REFCURSOR 內建型別,無需在套件中預先定義強型別游標,極大簡化動態查詢開發。
OPEN ... FOR ... USING 語法將動態 SQL 中的佔位符(如 :b1)與 PL/SQL 變數繫結,兼具效能與安全性,避免 SQL 注入風險。
表名、欄位名無法使用繫結變數,必須拼接字串。生產環境務必:
DBMS_ASSERT.ENQUOTE_NAME 驗證物件名稱;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 存儲過程開發的核心技能之一。