萬盛學電腦網

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

Oracle 10g R2 新特性:REF CURSOR 作為 IN 參數傳遞給 PL/SQL 存儲過程

技術背景

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;
/

.NET 客戶端呼叫範例 (ODP.NET)

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/中介表單一過程流式處理,記憶體高效

注意事項與限制

  • 游標狀態:傳入的 REF CURSOR 必須為 OPEN 狀態,且位於第一行之前;
  • 型別匹配:強型別 REF CURSOR (如 emp_cur_type) 需與定義完全一致,弱型別 SYS_REFCURSOR 通用性更強;
  • 所有權:游標所有權轉移給被呼叫過程,呼叫端不應再嘗試讀取或關閉;
  • 版本需求:資料庫 10.2.0.1+,ODP.NET 10.2.0.2.20+;
  • 效能:避免在循環中逐行 FETCH 大量資料,考慮 BULK COLLECT ... LIMIT 批量處理。

進階模式:動態 REF CURSOR 建構

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 關鍵互操作性增強,打破了「結果集只能流出不能流入」的限制,為伺服器端流式處理、微服務資料庫代理、審計自動化開闢新架構空間。

copyright © 萬盛學電腦網 all rights reserved