C# 呼叫 Oracle 預存程序與函數：反射游標與標量回傳值完整實作

前言

Oracle 資料庫開發中，預存程序回傳游標 (SYS_REFCURSOR) 與函數回傳標量值是常見模式。本文示範使用 Oracle.ManagedDataAccess 套件呼叫 Package 內的 Procedure 與 Function，涵蓋參數綁定、游標處理、資料型別對應與錯誤處理。

Oracle 端 Package 定義

-- Package 規格
CREATE OR REPLACE PACKAGE SCOTT.PKG_DEMO AS
    TYPE RefCursor IS REF CURSOR;
    
    -- 回傳游標的預存程序
    PROCEDURE GetEmployees(p_deptno IN NUMBER, p_cursor OUT RefCursor);
    
    -- 回傳標量值的函數
    FUNCTION GetDeptName(p_deptno IN NUMBER) RETURN VARCHAR2;
    
    -- 多參數預存程序
    PROCEDURE UpdateSalary(p_empno IN NUMBER, p_new_sal IN NUMBER, p_result OUT VARCHAR2);
END PKG_DEMO;
/

-- Package 主體
CREATE OR REPLACE PACKAGE BODY SCOTT.PKG_DEMO AS
    PROCEDURE GetEmployees(p_deptno IN NUMBER, p_cursor OUT RefCursor) IS
    BEGIN
        OPEN p_cursor FOR
            SELECT empno, ename, job, sal, hiredate
            FROM emp
            WHERE deptno = p_deptno
            ORDER BY sal DESC;
    END;
    
    FUNCTION GetDeptName(p_deptno IN NUMBER) RETURN VARCHAR2 IS
        v_name VARCHAR2(50);
    BEGIN
        SELECT dname INTO v_name FROM dept WHERE deptno = p_deptno;
        RETURN v_name;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN 'UNKNOWN';
    END;
    
    PROCEDURE UpdateSalary(p_empno IN NUMBER, p_new_sal IN NUMBER, p_result OUT VARCHAR2) IS
    BEGIN
        UPDATE emp SET sal = p_new_sal WHERE empno = p_empno;
        IF SQL%ROWCOUNT > 0 THEN
            p_result := 'SUCCESS';
            COMMIT;
        ELSE
            p_result := 'NOT_FOUND';
            ROLLBACK;
        END IF;
    END;
END PKG_DEMO;
/

C# 端資料存取層實作

using System;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;

public class OracleRepository
{
    private readonly string _connectionString;

    public OracleRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    private OracleConnection CreateConnection()
    {
        var conn = new OracleConnection(_connectionString);
        conn.Open();
        return conn;
    }

    /// 

    /// 呼叫回傳游標的預存程序
    /// 
    public DataTable GetEmployees(int deptNo)
    {
        using (var conn = CreateConnection())
        using (var cmd = new OracleCommand("PKG_DEMO.GetEmployees", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            
            // 輸入參數
            cmd.Parameters.Add("p_deptno", OracleDbType.Int32, deptNo, ParameterDirection.Input);
            
            // 輸出游標參數
            var cursorParam = new OracleParameter("p_cursor", OracleDbType.RefCursor)
            {
                Direction = ParameterDirection.Output
            };
            cmd.Parameters.Add(cursorParam);
            
            using (var adapter = new OracleDataAdapter(cmd))
            {
                var dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
        }
    }

    /// 

    /// 呼叫回傳標量值的函數
    /// 
    public string GetDeptName(int deptNo)
    {
        using (var conn = CreateConnection())
        using (var cmd = new OracleCommand("PKG_DEMO.GetDeptName", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            
            // 函數回傳值參數 (必須第一個加入)
            var returnParam = new OracleParameter("return_value", OracleDbType.Varchar2, 100)
            {
                Direction = ParameterDirection.ReturnValue
            };
            cmd.Parameters.Add(returnParam);
            
            // 輸入參數
            cmd.Parameters.Add("p_deptno", OracleDbType.Int32, deptNo, ParameterDirection.Input);
            
            cmd.ExecuteNonQuery();
            
            return returnParam.Value?.ToString() ?? "";
        }
    }

    /// 

    /// 呼叫多參數預存程序 (含輸出參數)
    /// 
    public string UpdateSalary(int empNo, decimal newSalary)
    {
        using (var conn = CreateConnection())
        using (var cmd = new OracleCommand("PKG_DEMO.UpdateSalary", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            
            cmd.Parameters.Add("p_empno", OracleDbType.Int32, empNo, ParameterDirection.Input);
            cmd.Parameters.Add("p_new_sal", OracleDbType.Decimal, newSalary, ParameterDirection.Input);
            
            var resultParam = new OracleParameter("p_result", OracleDbType.Varchar2, 20)
            {
                Direction = ParameterDirection.Output
            };
            cmd.Parameters.Add(resultParam);
            
            cmd.ExecuteNonQuery();
            
            return resultParam.Value?.ToString() ?? "";
        }
    }

    /// 

    /// 批次執行 (效能優化)
    /// 
    public void BatchUpdateSalaries(Dictionary updates)
    {
        using (var conn = CreateConnection())
        using (var cmd = new OracleCommand("PKG_DEMO.UpdateSalary", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ArrayBindCount = updates.Count;
            
            cmd.Parameters.Add("p_empno", OracleDbType.Int32, 
                updates.Keys.ToArray(), ParameterDirection.Input);
            cmd.Parameters.Add("p_new_sal", OracleDbType.Decimal, 
                updates.Values.ToArray(), ParameterDirection.Input);
            
            var resultParam = cmd.Parameters.Add("p_result", OracleDbType.Varchar2, 20, 
                new string[updates.Count], ParameterDirection.Output);
            
            cmd.ExecuteNonQuery();
            
            // 檢查每筆結果
            string[] results = (string[])resultParam.Value;
            for (int i = 0; i < results.Length; i++)
            {
                if (results[i] != "SUCCESS")
                {
                    int empNo = updates.Keys.ElementAt(i);
                    throw new Exception($"員工 {empNo} 更新失敗: {results[i]}");
                }
            }
        }
    }
}

進階：Oracle UDT (使用者定義型別) 對應

-- Oracle 物件型別
CREATE OR REPLACE TYPE EMP_OBJ AS OBJECT (
    EMPNO NUMBER,
    ENAME VARCHAR2(50),
    SAL NUMBER
);
/
CREATE OR REPLACE TYPE EMP_TAB AS TABLE OF EMP_OBJ;
/

-- 回傳集合的函數
CREATE OR REPLACE FUNCTION GetEmpsByDept(p_deptno NUMBER) RETURN EMP_TAB PIPELINED IS
BEGIN
    FOR r IN (SELECT empno, ename, sal FROM emp WHERE deptno = p_deptno) LOOP
        PIPE ROW(EMP_OBJ(r.empno, r.ename, r.sal));
    END LOOP;
    RETURN;
END;
/

// C# 對應類別 (需實作 IOracleCustomType, INullable)
public class EmpObj : IOracleCustomType, INullable
{
    [OracleObjectMapping("EMPNO")]
    public int EmpNo { get; set; }
    
    [OracleObjectMapping("ENAME")]
    public string EName { get; set; }
    
    [OracleObjectMapping("SAL")]
    public decimal Sal { get; set; }
    
    public bool IsNull => false;
    
    public void FromCustomObject(OracleConnection con, IntPtr pUdt)
    {
        OracleUdt.SetValue(con, pUdt, "EMPNO", EmpNo);
        OracleUdt.SetValue(con, pUdt, "ENAME", EName);
        OracleUdt.SetValue(con, pUdt, "SAL", Sal);
    }
    
    public void ToCustomObject(OracleConnection con, IntPtr pUdt)
    {
        EmpNo = (int)OracleUdt.GetValue(con, pUdt, "EMPNO");
        EName = (string)OracleUdt.GetValue(con, pUdt, "ENAME");
        Sal = (decimal)OracleUdt.GetValue(con, pUdt, "SAL");
    }
}

// 類別對應註冊 (啟動時一次)
[OracleCustomTypeMapping("SCOTT.EMP_OBJ")]
public class EmpObjFactory : IOracleCustomTypeFactory
{
    public IOracleCustomType CreateObject() => new EmpObj();
}

// 使用
public List GetEmpsByDeptUdt(int deptNo)
{
    using (var conn = CreateConnection())
    using (var cmd = new OracleCommand("GetEmpsByDept", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("p_deptno", OracleDbType.Int32, deptNo, ParameterDirection.Input);
        
        var retParam = new OracleParameter("return_value", OracleDbType.Array)
        {
            Direction = ParameterDirection.ReturnValue,
            UdtTypeName = "SCOTT.EMP_TAB"
        };
        cmd.Parameters.Add(retParam);
        
        cmd.ExecuteNonQuery();
        
        return ((OracleArray)retParam.Value).Value as EmpObj[];
    }
}

常見參數型別對應表

效能優化與最佳實踐

1. 連線池：連線字串加入 Pooling=true;Min Pool Size=5;Max Pool Size=100
2. 批次處理：使用 ArrayBindCount 大量 DML
3. 命令快取：cmd.AddToStatementCache(true) 重複執行受益
4. FetchSize：大量查詢設定 cmd.FetchSize = 10000
5. 參數方向：ReturnValue 必須第一個加入
6. 資源釋放：using 區塊確保連線/命令/Reader 正確 Dispose

錯誤處理模式

public T ExecuteWithRetry(Func operation, int maxRetries = 3)
{
    for (int attempt = 1; ; attempt++)
    {
        try
        {
            return operation();
        }
        catch (OracleException ex) when (IsTransientError(ex) && attempt < maxRetries)
        {
            Thread.Sleep(TimeSpan.FromSeconds(Math.Pow(2, attempt))); // 指數退避
        }
    }
}

private bool IsTransientError(OracleException ex)
{
    // ORA-03113, ORA-03114, ORA-01033 等暫時性錯誤
    int[] transientCodes = { 3113, 3114, 1033, 1034, 1089 };
    return transientCodes.Contains(ex.Number);
}

現代化替代：Dapper + Oracle

// NuGet: Dapper, Oracle.ManagedDataAccess
using Dapper;

public class DapperOracleRepo
{
    private readonly string _connStr;

    public IEnumerable GetEmployees(int deptNo)
    {
        using (var conn = new OracleConnection(_connStr))
        {
            conn.Open();
            // Dapper 不直接支援 RefCursor 輸出，需包裝
            return conn.Query(
                "PKG_DEMO.GetEmployees",
                new { p_deptno = deptNo },
                commandType: CommandType.StoredProcedure);
        }
    }
}

總結

呼叫 Oracle 預存程序/函數關鍵在：CommandType.StoredProcedure、正確設定 ParameterDirection (特別是 ReturnValue 必須最先加入)、OracleDbType.RefCursor 對應游標、OracleDataAdapter.Fill 直接轉 DataTable。現代專案建議使用 Oracle.ManagedDataAccess (無 Client 安裝需求) 搭配 Dapper 簡化資料映射，複雜 UDT 場景再實作 IOracleCustomType。