C# 讀取 dbms_output 效能強化版
1 |
前文介紹過使用 C# 讀取 dbms_output 寫入內容,範例留了一個小尾巴,跑迴圈連資料庫犯了效能大忌,應改成一次執行或查詢取回才上道。
dbms_output.get_lines() 允許一次取得多筆訊息,但傳回型別為 TYPE DBMSOUTPUT_LINESARRAY IS VARRAY(2147483647) OF VARCHAR2(32767); 讀取要費點手腳,Oracle 生手經過一番研究,試出四種不同做法,就當練功吧。
- 使用 ODP.NET OracleParameter 接收
OracleParameter 有個 CollectionType 屬性,將屬性型別設為 OracleDbType.Varchar2,Size 為陣列大小,再設定 CollectionType = OracleCollectionType.PLSQLAssociativeArray,ArrayBindSize 傳入 int[] 指定每筆訊息字串最大長度(12c 上限 32767,更早版本為 4000),可從 lines 參數取回字串陣列。
這個做法的缺點是寫法複雜且高度依賴 ODP.NET(不易改寫成 Dapper 版),然後每次遇到要預估接收空間都讓我焦慮,太大怕浪費、太小取不完要分多次,好阿雜。 - 跑 PL/SQL 組裝成單一字串傳回
寫一小段 PL/SQL 程式,呼叫 get_lines() 再跑迴圈將其串成單一字串傳回。優點是只需單一字串參數接收結果,缺點是可能卡到 4000 或 32767 的字串大小上限,感覺爆掉的機會不低,不甚實用。 - 自訂函式將結果轉成 Table 後以 SELECT 查詢讀取
在 Stackoverflow 看到這招妙計:
create or replace function get_dbms_output
return dbmsoutput_linesarray
as
l_output dbmsoutput_linesarray;
l_linecount number;
begin
dbms_output.get_lines(l_output, l_linecount);
if l_output.count > l_linecount then
-- Remove the final empty line above l_linecount
l_output.trim;
end if;
透過神奇的 get_dbms_output 自訂函數,跑 SELECT column_value FROM TABLE(get_dbms_output) 純查詢就能撈回 dbms_output 全部內容,算是最漂亮簡潔的解法,小缺點是需要資料庫部署自訂函數。 - 使用 Ref Cursor 讀取
受 get_dbms_output 函數的啟發,我想到不用部署 Procedure 或自訂函數也能用 Ref Cursor 一次取回所有訊息的做法(Dapper 可支援 Ref Cursor),比 SELECT 法複雜,但不必動到資料庫就能用,算是次佳解。
附上四種做法的範例。
static void Main(string[] args)
{
using (var cn = new OracleConnection(cs))
{
//**重要** 先開啟連線,確保後續執行在同一個Session
cn.Open();
//**重要** 記得要啟用dbms_output
cn.Execute("dbms_output.enable",
commandType: CommandType.StoredProcedure);
//方法1,使用PLSQLAssociativeArray接回陣列
cn.Execute("JeffDbJobTest1",
commandType: CommandType.StoredProcedure);
var cmd = cn.CreateCommand();
cmd.CommandText = "dbms_output.get_lines";
cmd.CommandType = CommandType.StoredProcedure;
var pLines = cmd.Parameters.Add("lines", OracleDbType.Varchar2,
ParameterDirection.Output);
pLines.Size = 2000; //可容納的訊息字串筆數
pLines.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
//指定每筆字串最大長度(Oracle 12可到32767)
pLines.ArrayBindSize = Enumerable.Repeat(4000, pLines.Size).ToArray();
//numlines為雙向參數,執行前傳入lines可容納筆數,執行後傳回實際讀得筆數
var pNumLines = cmd.Parameters.Add("numlines", OracleDbType.Int32,
ParameterDirection.InputOutput);
pNumLines.Value = pLines.Size;
cmd.ExecuteNonQuery();
var rawLines = (OracleString[]) pLines.Value;
string[] lines =
//依numlines判斷資料筆數
rawLines.Take(((OracleDecimal) pNumLines.Value).ToInt32())
.Select(o => o.ToString()).ToArray();
Console.WriteLine(string.Join("\n", lines));
//缺點: 程序較複雜,得先預估空間,若一次取不完還是得跑迴圈(但應很罕見)
//方法二,用PL/SQL指令組成字串一次傳回
//呼叫Stored Procedure
cn.Execute("JeffDbJobTest1",
commandType: CommandType.StoredProcedure);
var p = new DynamicParameters();
p.Add("result", dbType: DbType.AnsiString, size: 32767,
direction: ParameterDirection.Output);
cn.Execute(@"
DECLARE
lines dbmsoutput_linesarray;
numlines INTEGER;
i INTEGER;
msg VARCHAR2(32767);
BEGIN
numlines := 32767;
dbms_output.get_lines(lines, numlines);
i := 1;
WHILE i <= numlines
LOOP
IF i = 1 THEN
msg := lines(i);
ELSE
msg := msg || CHR(10) || lines(i);
END IF;
i := i + 1;
END LOOP;
:result := msg;
END;
", p);
Console.WriteLine(p.Get<string>("result"));
//缺點: 取回字串有長度限制(12c 32767,更早版本只有4000)
//方法3,使用自訂函數轉成Table
cn.Execute("JeffDbJobTest1",
commandType: CommandType.StoredProcedure);
lines = cn.Query<string>(
"SELECT column_value from table(get_dbms_output)").ToArray();
foreach (var line in lines)
Console.WriteLine(line);
//缺點: 需在資料庫部署自訂函數
//方法4,用RefCursor
cn.Execute("JeffDbJobTest1",
commandType: CommandType.StoredProcedure);
//使用 Dapper 接收 Oracle Ref Cursor
//http://blog.darkthread.net/post-2017-04-17-dapper-ref-cursor.aspx
var op = new OracleDynamicParameters();
op.Add("res", dbType: OracleDbType.RefCursor,
direction: ParameterDirection.Output);
var m = cn.QueryMultiple(@"
DECLARE
lines dbmsoutput_linesarray;
numlines INTEGER;
BEGIN
dbms_output.get_lines(lines, numlines);
IF lines.COUNT > numlines THEN
lines.TRIM;
END IF;
OPEN :res FOR SELECT column_value FROM TABLE(lines);
END;
", op);
var data = m.Read();
lines = data.Select(o => (string) o.COLUMN_VALUE).ToArray();
foreach (var line in lines)
Console.WriteLine(line);
Console.Read();
}
}
Comments
# by 阿翰
真的很不喜歡 Oracle... 要這樣才能從sp中select T-SQL真的人性化多