REF CURSOR 是指向伺服器端結果集的指標。舊版 ODP.NET 僅支援將 REF CURSOR 作為 OUT 參數返回給客戶端。Oracle 10g Release 2 重大突破:允許將開啟的 REF CURSOR 作為 IN 參數傳遞給 PL/SQL 存儲過程/函數,實現結果集在伺服器端的二次處理、轉移、審計等進階場景。
-- 1. 建立測試表
CREATE TABLE processing_result (
status VARCHAR2(4000)
);
-- 2. 定義套件規格:宣告 REF CURSOR 型別與過程
CREATE OR REPLACE PACKAGE cursor_in_out AS
TYPE emp_cur_type IS REF CURSOR RETURN employees%ROWTYPE;
PROCEDURE process_cursor(p_cursor IN emp_cur_type);
END;
/
-- 3. 套件主體:處理傳入的游標
CREATE OR REPLACE PACKAGE BODY cursor_in_out AS
PROCEDURE process_cursor(p_cursor IN emp_cur_type) IS
v_emp employees%ROWTYPE;
BEGIN
LOOP
FETCH p_cursor INTO v_emp;
EXIT WHEN p_cursor%NOTFOUND;
-- 業務處理:記錄審計日志
INSERT INTO processing_result VALUES (
'Processed employee #' || v_emp.employee_id || ': ' ||
v_emp.first_name || ' ' || v_emp.last_name
);
END LOOP;
END;
END;
/
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
string connStr = "User Id=hr;Password=hr;Data Source=orcl;";
using (OracleConnection conn = new OracleConnection(connStr)) {
conn.Open();
// 1. 開啟來源游標
OracleCommand cmdSrc = new OracleCommand(
"SELECT * FROM employees WHERE department_id = 60", conn);
OracleRefCursor refCursor = (OracleRefCursor)cmdSrc.ExecuteScalar();
// 或使用 ExecuteReader 獲取 OracleDataReader 後轉換
// 2. 呼叫接收 REF CURSOR 的存儲過程
OracleCommand cmdProc = new OracleCommand("cursor_in_out.process_cursor", conn);
cmdProc.CommandType = CommandType.StoredProcedure;
// 關鍵:綁定 REF CURSOR 參數
OracleParameter param = new OracleParameter("p_cursor", OracleDbType.RefCursor);
param.Direction = ParameterDirection.Input;
param.Value = refCursor; // 傳遞已開啟的游標
cmdProc.Parameters.Add(param);
cmdProc.ExecuteNonQuery();
// 3. 驗證結果
OracleCommand cmdChk = new OracleCommand(
"SELECT * FROM processing_result", conn);
using (OracleDataReader dr = cmdChk.ExecuteReader()) {
while (dr.Read()) Console.WriteLine(dr.GetString(0));
}
}
| 場景 | 傳統做法 | REF CURSOR IN 參數優勢 |
|---|---|---|
| 跨 Schema/DB 數據搬移 | 客戶端提取再插入 (網路雙向傳輸) | 伺服器端直連處理,零網路開銷 |
| 異構系統整合 | 中介層轉換格式 | PL/SQL 直接消費游標,邏輯下沉 |
| 審計/日志記錄 | 應用層攔截 | 資料庫層強制審計,不可繞過 |
| 複雜 ETL 轉換 | 多階段 SQL/中介表 | 單一過程流式處理,記憶體高效 |
emp_cur_type) 需與定義完全一致,弱型別 SYS_REFCURSOR 通用性更強;BULK COLLECT ... LIMIT 批量處理。CREATE OR REPLACE PROCEDURE dynamic_processor(p_query IN VARCHAR2) AS
v_cur SYS_REFCURSOR;
BEGIN
OPEN v_cur FOR p_query; -- 動態構建查詢
cursor_in_out.process_cursor(v_cur); -- 傳遞給通用處理器
END;
此模式實現「查詢定義與處理邏輯解耦」,極大提升框架靈活性。
REF CURSOR 作為 IN 參數是 Oracle 10g R2 關鍵互操作性增強,打破了「結果集只能流出不能流入」的限制,為伺服器端流式處理、微服務資料庫代理、審計自動化開闢新架構空間。