Oracle 存儲過程返回結果集關鍵在於 REF CURSOR (游標變量)。與靜態游標綁定單一查詢不同,REF CURSOR 可為任何兼容結構的查詢動態打開,極大增強靈活性。
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)
);
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;
/
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;
/
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 與弱類型對比)