C# - 使用 SYS_REFCURSOR 來回傳資料集

摘要:C# - 使用 SYS_REFCURSOR 來回傳資料集

在學習 Oracle 的這段時間中,大多都是利用一些工具來下下語法,或者寫個 Procedure 做一些簡單的傳入參數與簡易的傳出參數(指的是一些 ID 或者 INT 的回傳),所以沒有甚麼太過複雜的玩法。

這篇的目的主要是記錄有關於,使用 Procedure 來回傳一個資料集,在 MS SQL 的 Procedure 來說,要做到這點,是在基本不過的事,因為我只要寫好一個 Procedure,如下:

MS SQL:

CREATE PROCEDURE [dbo].[usp_SelectData]
 @p_Year char(4)
AS
BEGIN
 SELECT *
 FROM Table_A
 WHERE Column_Year = @p_Year
 ORDER BY Column_Year;
END

然後再搭配程式,就可以將資料集呈現在UI上了

程式:

 

dataGridView1.DataSource = null;

DataSet ds = new DataSet("dsDataList");

using (SqlConnection sc = new SqlConnection(Ms SQL Connection String))
{
 sc.Open();

 SqlTransaction st = sc.BeginTransaction("SampleTransaction");

 try
 {
  using (SqlCommand cmd1 = new SqlCommand("usp_SelectData", sc, st))
  {
   cmd1.CommandType = CommandType.StoredProcedure;

   cmd1.Parameters.Add(new SqlParameter("@p_Year", SqlDbType.Char)).Value = txtYear.Text;

   SqlDataAdapter da = new SqlDataAdapter(cmd1);

   da.Fill(ds, "dtDataList");

   dataGridView1.DataSource = ds.Tables["dtDataList"];
  }

  using (SqlCommand cmd2 = new SqlCommand())
  {
   cmd2.Connection = sc;
   cmd2.Transaction = st;
   cmd2.CommandType = CommandType.Text;                       

   cmd2.CommandText = "INSERT INTO Table_A (...) VALUES(...)";
   cmd2.ExecuteNonQuery();

   cmd2.CommandText = "INSERT INTO Table_A (...) VALUES(...)";
   cmd2.ExecuteNonQuery();

   st.Commit();
  }
 }
 catch (Exception ex)
 {
  st.Rollback("SampleTransaction");
  MessageBox.Show(ex.ToString());
 }
}

滿簡單且基礎的寫法,也算是基本功了。但我的困惱是,那 Oracle 呢!? 在 Oracle 中,似乎不是這樣寫就可以讓你把資料集給傳給前端了。所以就稍微花一點時間,找了一些相關資料,就完成了,以下就來看看嚕...

步驟一:建立一個 Procedure

PL/SQL:

 

CREATE OR REPLACE PROCEDURE USP_SELECTDATA(P_PARAM IN VARCHAR2, P_COLLECTION OUT SYS_REFCURSOR) AS

 

BEGIN

 

OPEN P_COLLECTION FOR
SELECT *
FROM TABLE_A
WHERE COLUMN_A = P_PARAM
ORDER BY COLUMN_A;
 
END USP_SELECTDATA;

※紅色的部份就是重點啦...

步驟二:撰寫前端程式

程式:

 

dataGridView1.DataSource = null;

DataSet ds = new DataSet("dsDataList");

using (OracleConnection sc = new OracleConnection(strOracle))
{
 sc.Open();

 try
 {
  using (OracleCommand cmd1 = new OracleCommand("dcms.usp_SelectData", sc))
  {
   cmd1.CommandType = CommandType.StoredProcedure;

   cmd1.Parameters.Add(new OracleParameter("p_Param_Code", OracleType.NVarChar)).Value = "AS400_UPLOAD_PROGRAM";

   OracleParameter param1 = cmd1.Parameters.Add("p_Collection", OracleType.Cursor);
   
   param1.Direction = ParameterDirection.Output;

   OracleDataAdapter da = new OracleDataAdapter(cmd1);

   da.Fill(ds, "dtDataList");

   dataGridView1.DataSource = ds.Tables["dtDataList"];
  }
 }
 catch (Exception ex)
 {
  MessageBox.Show(ex.ToString());
 }
}

參考:
cursor 與refcursor及sys_refcursor的區別
How to call an Oracle Stored Procedure that returns one or more REF CURSORS, using ADO from C++
Using REFCURSOR Bind Variables