萬盛學電腦網

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

.NET 調用 Oracle 存儲過程返回結果集:REF CURSOR 實戰指南

Oracle 存儲過程返回結果集關鍵在於 REF CURSOR (游標變量)。與靜態游標綁定單一查詢不同,REF CURSOR 可為任何兼容結構的查詢動態打開,極大增強靈活性。

1. 建表示例

CREATE TABLE LIHUAN.BILL_POINTS (
  POINTS_ID     NUMBER(10) NOT NULL,
  CUSTOMER_ID   NUMBER(10) NOT NULL,
  BILL_POINT_NO NUMBER(2) DEFAULT 1 NOT NULL,
  CONSTRAINT PK_BILL_POINTS PRIMARY KEY (POINTS_ID)
);

2. 定義 Package Spec (公開接口)

CREATE OR REPLACE PACKAGE LIHUAN.YY_PKG_BILL_POINT_NO IS
  TYPE T_CURSOR IS REF CURSOR;  -- 弱類型 REF CURSOR
  PROCEDURE BILL_POINT_NO (
    P_CUSTOMER_ID IN  BILL_POINTS.CUSTOMER_ID%TYPE,
    RE_CURSOR     OUT T_CURSOR
  );
END;
/

3. 實現 Package Body

CREATE OR REPLACE PACKAGE BODY LIHUAN.YY_PKG_BILL_POINT_NO IS
  PROCEDURE BILL_POINT_NO (
    P_CUSTOMER_ID IN  BILL_POINTS.CUSTOMER_ID%TYPE,
    RE_CURSOR     OUT T_CURSOR
  ) IS
    V_CURSOR T_CURSOR;
  BEGIN
    OPEN V_CURSOR FOR
      SELECT BILL_POINT_NO
      FROM   BILL_POINTS
      WHERE  CUSTOMER_ID = P_CUSTOMER_ID;
    RE_CURSOR := V_CURSOR;
  END;
END;
/

4. .NET (C#) 調用代碼片段

using (OracleConnection conn = new OracleConnection(connStr))
using (OracleCommand cmd = new OracleCommand("YY_PKG_BILL_POINT_NO.BILL_POINT_NO", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("P_CUSTOMER_ID", OracleDbType.Int32).Value = custId;
    cmd.Parameters.Add("RE_CURSOR", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

    conn.Open();
    using (OracleDataReader dr = cmd.ExecuteReader())
    {
        while (dr.Read())
            Console.WriteLine(dr["BILL_POINT_NO"]);
    }
}

關鍵點:參數方向 Output、類型 RefCursor,ExecuteReader 直接獲取結果集流。

(本文共 2 頁,含強類型 REF CURSOR 與弱類型對比)

copyright © 萬盛學電腦網 all rights reserved