ODP.NET 練習 - 執行 PL/SQL 將結果寫入暫存資料表傳回
| | 2 | | ![]() |
有讀者問到 ODP.NET CommandText 跑多行指令的花式應用,包含在 Script Block DECLARE 宣告變數以及傳回暫存資料表結果。 透過多組 PL/SQL 邏輯組裝資料存進暫存資料表再一次傳回結果的做法,實務上還蠻常見的,但大多情況會寫成 Procedure。如果能讓 .NET 端決定 Script,應用彈性可更上層樓,感覺是很實用的技巧,決定來練習一下。
資安宣導:任何動態決定 SQL 指令的場合(包含在 Procedure 裡呼叫 EXECUTE IMMEDIATE、sp_executesql 也是),都請務必排除 SQL Injection 風險,此事非同小可,情節嚴重甚至可致身敗命裂家破人亡。
整理這項練習涉及的議題:
- 使用 CommandText 執行多行指令,關鍵要用 begin ... end; 把指令包起來,並且要留意不能更動 Schema (DDL),若一定要需改用 EXECUTE IMMEDIATE '...'。
- Script Block 要回傳 SELECT 結果,需使用 OracleDbType.RefCursor 型別參數,常見範例多應用在 Stored Procedure,用在 Script Block 概念差不多,宣告 OracleDbType.RefCursor 參數並設成 ParameterDirection.Output 即可。
- Oracle 的暫存資料表概念跟 MSSQL 很不一樣,18c 以前的暫存資料表必須事先宣告且為全域性,差在每個 Session 只看得到自己的資料,互不干擾。全域暂存表分為 ON COMMIT DELETE ROWS (Commit 時就清掉)、 ON COMMIT PRESERVE ROWS (同一 Session 可繼續存取資料直到 Session 結束)。18c+ 起支援私有暫存資料表,資料只保存在記憶體裡,Session 或 Transaction 結束時連同資料表定義一起丟棄 (ON COMMIT DROP DEFINITION)。不過,Oracle 的暫存資料表雖然可以用 EXECUTE IMMEDIATE '...' 動態建立,後續若想直接寫 INSERT INTO ora$ptt_XXX 卻會因不認得 Schema 無法編譯,通通得改寫成 EXECUTE IMMEDIATE,便利性輸 MSSQL 一大截,加上 Oracle 18+ 限定,我還是選擇用全域暫存資料表實作。
- 經實測 OracleCommand.ExecuteNonQuery() 預設會觸發 Commit,若暫存資料表設成 ON COMMIT DELETE ROWS,RefCursor 讀不到資料,會出現 ORA-08103: object no longer exists 錯誤;若設成 ON COMMIT PRESERVE ROWS 雖可讀到資料, 但由於 Connection Pool 共用連線物件會沿用同一 Session,導致第二次查詢看到前一次殘留資料。針對這個問題,我的解法是宣告 ON COMMIT DELETE ROWS,執行時啟用 Transaction,讀完 RefCursor 資料再 Commit。(如果大家若有其他更好做法,也請不吝分享。)
講完原理,來看程式碼。
測試前要預先建好 GLOBAL TEMPORARY TABLE TheSalmons,我在 .NET 裡寫了一段 Script,用 DECLARE 宣告 v_idx 練習在 Script Block 中使用變數,寫入兩筆固定資料到暫存資料表,第三筆姓名則由外部傳入 :my_salmon_name 參數指定,最後開啟名為 :salmons 的 RefCursor 供呼叫端讀取。
<%@Page Language="C#"%>
<%@ Import Namespace="Oracle.ManagedDataAccess.Client" %>
<script runat="server">
string cs = CnnStringManager.GetConnString("XE");
/*
-- 預先在 Oracle 建好 Global Temporary Table
CREATE GLOBAL TEMPORARY TABLE TheSalmons (
FishId NUMBER(4,0), FishName NVARCHAR2(64)
) ON COMMIT DELETE ROWS
*/
const string sql = @"
DECLARE
v_idx NUMBER(1);
BEGIN
v_idx := 1;
INSERT INTO TheSalmons VALUES (v_idx, N'張鮭魚之夢');
v_idx := v_idx + 1;
INSERT INTO TheSalmons VALUES (v_idx, N'許星光流連擊鮭魚');
v_idx := v_idx + 1;
INSERT INTO TheSalmons VALUES (v_idx, :my_salmon_name);
OPEN :salmons FOR SELECT * FROM TheSalmons;
END;
";
void Page_Load(object sender, EventArgs e)
{
using (var cn = new OracleConnection(cs))
{
cn.Open();
var trn = cn.BeginTransaction();
var cmd = cn.CreateCommand();
cmd.CommandText = sql;
cmd.Transaction = trn;
cmd.BindByName = true;
cmd.Parameters.Add("my_salmon_name", OracleDbType.NVarchar2).Value =
"童鮭魚靜";
var refCur = cmd.Parameters.Add("salmons", OracleDbType.RefCursor);
refCur.Direction = System.Data.ParameterDirection.Output;
cmd.ExecuteNonQuery();
var dr = ((Oracle.ManagedDataAccess.Types.OracleRefCursor)refCur.Value).GetDataReader();
Response.ContentType = "text/plain";
while (dr.Read())
{
Response.Write(dr["FishId"].ToString() + "." + dr["FishName"].ToString() + "\n");
}
trn.Commit();
}
}
</script>
成功!
Example of using PL/SQL script block to insert data into temporary table and read the results by reference cursor with ODP.NET.
Comments
# by JerryH
問一下,此Case 是用dataReader 取得資料,可以改用DataTable來取嗎? 因為dataReader 用完若沒用close 仍會殘留connection的樣子
# by Jeffrey
to JerryH, 小改一下即可 var dt = new DataTable(); dt.Load(dr);。 參考:ADO.NET 老程式拉皮技巧 - 使用 Dapper 產生 DataTable https://blog.darkthread.net/blog/dapper-return-datatable/