.NET 生態下存取 Oracle，**ODP.NET (Oracle.ManagedDataAccess)** 是官方推薦、效能最優、功能最全的資料提供者。本文以完整可運行的 C# 程式碼，示範如何呼叫 Oracle Package 中的 **Function (標量返回)** 與 **Procedure (Ref Cursor 結果集)**，涵蓋參數綁定、連線管理、資料繫結與異常處理的企業級最佳實踐。
-- 1. Package 規範 (介面定義)
CREATE OR REPLACE PACKAGE scott.pkg_demo AS
TYPE ref_cur IS REF CURSOR; -- 定義弱類型游標
FUNCTION f_get_prefix(p_str IN VARCHAR2) RETURN VARCHAR2; -- 純標量函數
PROCEDURE p_get_employees(p_deptno IN NUMBER, p_cursor OUT ref_cur); -- 回傳結果集
END pkg_demo;
/
-- 2. Package 實作
CREATE OR REPLACE PACKAGE BODY scott.pkg_demo AS
FUNCTION f_get_prefix(p_str IN VARCHAR2) RETURN VARCHAR2 AS
v_result VARCHAR2(100) := 'PREFIX_';
BEGIN
RETURN v_result || p_str;
END;
PROCEDURE p_get_employees(p_deptno IN NUMBER, p_cursor OUT ref_cur) AS
BEGIN
OPEN p_cursor FOR
SELECT empno, ename, sal, hiredate
FROM emp
WHERE deptno = p_deptno
ORDER BY sal DESC;
END;
END pkg_demo;
/
Install-Package Oracle.ManagedDataAccess
// 或 .NET Core / .NET 5+:
dotnet add package Oracle.ManagedDataAccess.Core
{
"ConnectionStrings": {
"OracleDb": "User Id=scott;Password=tiger;Data Source=//host:1521/orclpdb;Pooling=true;Min Pool Size=5;Max Pool Size=100;"
}
}
using System;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
public class OracleFunctionDemo
{
private readonly string _connStr;
public OracleFunctionDemo(string connStr) => _connStr = connStr;
public string CallGetPrefix(string input)
{
const string sql = "pkg_demo.f_get_prefix"; // Package.Function 名稱
using (var conn = new OracleConnection(_connStr))
using (var cmd = new OracleCommand(sql, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
// 1. 輸入參數
var pInput = new OracleParameter("p_str", OracleDbType.Varchar2, 100)
{
Direction = ParameterDirection.Input,
Value = input
};
cmd.Parameters.Add(pInput);
// 2. 返回值參數 (關鍵: ParameterDirection.ReturnValue)
var pReturn = new OracleParameter("return_value", OracleDbType.Varchar2, 200)
{
Direction = ParameterDirection.ReturnValue
};
cmd.Parameters.Add(pReturn);
conn.Open();
cmd.ExecuteNonQuery(); // Function 使用 ExecuteNonQuery
return pReturn.Value?.ToString() ?? string.Empty;
}
}
}
public class OracleProcedureDemo
{
private readonly string _connStr;
public OracleProcedureDemo(string connStr) => _connStr = connStr;
public DataTable GetEmployeesByDept(int deptNo)
{
const string sql = "pkg_demo.p_get_employees";
var dt = new DataTable();
using (var conn = new OracleConnection(_connStr))
using (var cmd = new OracleCommand(sql, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
// 輸入參數
cmd.Parameters.Add(new OracleParameter("p_deptno", OracleDbType.Int32)
{
Direction = ParameterDirection.Input,
Value = deptNo
});
// 輸出 Ref Cursor (關鍵: OracleDbType.RefCursor + ParameterDirection.Output)
cmd.Parameters.Add(new OracleParameter("p_cursor", OracleDbType.RefCursor)
{
Direction = ParameterDirection.Output
});
// 使用 OracleDataAdapter 填充 DataTable (自動處理游標讀取)
using (var da = new OracleDataAdapter(cmd))
{
conn.Open();
da.Fill(dt); // 內部會讀取 Ref Cursor 並關閉游標
}
}
return dt;
}
// 進階：使用 OracleDataReader 逐行處理 (記憶體友善)
public IEnumerable StreamEmployees(int deptNo)
{
const string sql = "pkg_demo.p_get_employees";
using (var conn = new OracleConnection(_connStr))
using (var cmd = new OracleCommand(sql, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("p_deptno", OracleDbType.Int32)
{ Direction = ParameterDirection.Input, Value = deptNo });
cmd.Parameters.Add(new OracleParameter("p_cursor", OracleDbType.RefCursor)
{ Direction = ParameterDirection.Output });
conn.Open();
using (var reader = cmd.ExecuteReader()) // 返回 OracleDataReader
{
while (reader.Read())
{
yield return new Employee
{
EmpNo = reader.GetInt32(0),
EName = reader.GetString(1),
Sal = reader.GetDecimal(2),
HireDate = reader.GetDateTime(3)
};
}
}
}
}
}
public class Employee
{
public int EmpNo { get; set; }
public string EName { get; set; }
public decimal Sal { get; set; }
public DateTime HireDate { get; set; }
}
| 場景 | OracleDbType | ParameterDirection | 關鍵點 |
|---|---|---|---|
| Function 返回值 | 對應返回類型 (Varchar2, Int32, Decimal...) | ReturnValue | 參數名慣用 return_value 或 RETURN，必須加在 Parameters 集合中 |
| IN 參數 | 對應類型 | Input | Value 屬性賦值 |
| OUT 參數 (標量) | 對應類型 | Output | 需指定 Size (Varchar2 等)，執行後讀取 Value |
| IN OUT 參數 | 對應類型 | InputOutput | 執行前給初值，執行後取新值 |
| Ref Cursor | RefCursor | Output | 搭配 OracleDataAdapter.Fill() 或 ExecuteReader() 讀取 |
Pooling=true，設定合理 Min/Max Pool Size，避免連線風暴。OracleParameter。await cmd.ExecuteNonQueryAsync()、await reader.ReadAsync() 提升吞吐量。NUMBER → C# decimal (避免精度損失)；DATE/TIMESTAMP → DateTime / DateTimeOffset。OracleException，依 ErrorCode (如 12154, 12541, 01017) 分類處理、記錄完整堆疊與 SQL 文本。OracleDataReader + yield return 避免一次性載入巨大 DataTable 造成 OOM。conn.Open()。OPEN cursor FOR ... 正確執行，且 C# 端 OracleDbType.RefCursor 拼寫正確。掌握 ODP.NET 的 OracleParameter 機制與 RefCursor 處理模式，讓 C# 應用與 Oracle 預存程序無縫整合，是構建高效能、強類型、可維護企業級資料存取層的核心技能。
