Oracle 10g R2 新功能：Ref Cursor 作為 PL/SQL 輸入參數傳遞

Ref Cursor 回顧

Ref Cursor 是伺服器端結果集的參考。開啟時不傳資料，只回傳伺服器端指標，用戶端自行決定何時、如何取資料。舊版 ODP.NET 僅支援作為輸出參數接收，10g R2 新增支援作為 輸入參數 傳給 PL/SQL 存儲程序/函數。

資料庫端準備

CREATE TABLE PROCESSING_RESULT (STATUS VARCHAR2(64));

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

CREATE OR REPLACE PACKAGE BODY CURSOR_IN_OUT AS
  PROCEDURE PROCESS_CURSOR(P_CURSOR IN EMP_CUR_TYPE) IS
    EMP_REC EMPLOYEES%ROWTYPE;
  BEGIN
    LOOP
      FETCH P_CURSOR INTO EMP_REC;
      EXIT WHEN P_CURSOR%NOTFOUND;
      INSERT INTO PROCESSING_RESULT
      VALUES('Processed employee #' || EMP_REC.EMPLOYEE_ID || ': ' ||
             EMP_REC.FIRST_NAME || ' ' || EMP_REC.LAST_NAME);
    END LOOP;
  END;
END;
/

.NET 客戶端程式

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace CursorInCursorOut {
  class Class1 {
    [STAThread]
    static void Main() {
      string constr = "User Id=hr;Password=hr;Data Source=oramag;Pooling=false";
      using (OracleConnection con = new OracleConnection(constr)) {
        con.Open();

        // 1. 取得 Ref Cursor (輸出參數)
        OracleCommand cmd = con.CreateCommand();
        cmd.CommandText = "BEGIN OPEN :1 FOR SELECT * FROM EMPLOYEES WHERE MANAGER_ID=101; END;";
        OracleParameter pRc = cmd.Parameters.Add("P_RC", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
        cmd.ExecuteNonQuery();

        // 2. 將 Ref Cursor 作為輸入參數傳給存儲程序
        cmd.Parameters.Clear();
        cmd.CommandText = "CURSOR_IN_OUT.PROCESS_CURSOR";
        cmd.CommandType = CommandType.StoredProcedure;
        OracleParameter pInput = cmd.Parameters.Add("P_INPUT", OracleDbType.RefCursor, pRc.Value, ParameterDirection.Input);
        cmd.ExecuteNonQuery();

        // 清理
        pInput.Dispose();
        pRc.Dispose();
        cmd.Dispose();
      }
    }
  }
}

驗證結果

SQL> SELECT * FROM PROCESSING_RESULT;
STATUS
----------------------------------------
Processed employee #108: Nancy Greenberg
Processed employee #200: Jennifer Whalen
Processed employee #203: Susan Mavris
Processed employee #204: Hermann Baer
Processed employee #205: Shelley Higgins

應用價值

允許在中介層或批次程式中先開啟游標、再交由資料庫端邏輯處理，減少資料傳輸、提升彈性與效能。