摘要: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