ADO.NET 存取 Oracle 9i 存儲過程進階篇：REF CURSOR、DataAdapter、序列、CRUD 封裝與多結果集

REF CURSOR 與 DataReader

Oracle 存儲過程**不支援 RETURN 值**，結果集**必須用 REF CURSOR OUT 參數**返回。ADO.NET 對應 OracleType.Cursor + ParameterDirection.Output。

OracleCommand cmd = new OracleCommand("PKG.GET_EMPS", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("p_deptno", OracleType.Number).Value = 10;
cmd.Parameters.Add("p_rc", OracleType.Cursor).Direction = ParameterDirection.Output;

conn.Open();
OracleDataReader dr = cmd.ExecuteReader();  // 連線必須保持開啟
while (dr.Read()) { ... }
dr.Close(); conn.Close();

• 多 REF CURSOR 依 **Parameters 加入順序** 讀取，非定義順序；用 dr.NextResult() 切換。

ExecuteOracleScalar / ExecuteScalar 差異

• ExecuteOracleScalar：回傳 OracleType 物件。
• ExecuteScalar：回傳 .NET 基礎型別。
• **存儲過程無法用這兩者取回傳值**，只能用 OUT 參數。

Oracle 序列 實現自動遞增主鍵

CREATE SEQUENCE LOCATIONS_SEQ
INCREMENT BY 100 START WITH 1 MAXVALUE 9900 NOCYCLE NOCACHE NOORDER;

存儲過程中插入並回傳新主鍵：

INSERT INTO LOCATIONS (LOCATION_ID, ...) VALUES (LOCATIONS_SEQ.NEXTVAL, ...);
SELECT LOCATIONS_SEQ.CURRVAL INTO p_location_id FROM DUAL;  -- OUT 參數

ADO.NET 呼叫：

cmd.Parameters.Add("p_location_id", OracleType.Number).Direction = ParameterDirection.Output;
int rows = cmd.ExecuteNonQuery();
long newId = (long)cmd.Parameters["p_location_id"].Value;

DataAdapter + DataSet 雙向同步 (CRUD 封裝)

因 REF CURSOR 無中繼資料，DataAdapter 無法自動產生 UPDATE/INSERT/DELETE 命令，**必須手動指定四個 Command 皆為存儲過程**。

Oracle 端 CRUD 套件

CREATE OR REPLACE PACKAGE CRUD_LOCATIONS AS
    TYPE T_CURSOR IS REF CURSOR;
    PROCEDURE GetLocations    (cur_Locations OUT T_CURSOR);
    PROCEDURE UpdateLocations (p_location_id IN NUMBER, p_street_address IN VARCHAR2, ...);
    PROCEDURE DeleteLocations (p_location_id IN NUMBER);
    PROCEDURE InsertLocations (p_location_id OUT NUMBER, p_street_address IN VARCHAR2, ...);
END;

ADO.NET 組裝 DataAdapter

OracleDataAdapter da = new OracleDataAdapter();

// SelectCommand
OracleCommand sel = new OracleCommand("CRUD_LOCATIONS.GetLocations", conn);
sel.CommandType = CommandType.StoredProcedure;
sel.Parameters.Add("cur_Locations", OracleType.Cursor).Direction = ParameterDirection.Output;
da.SelectCommand = sel;

// UpdateCommand
OracleCommand upd = new OracleCommand("CRUD_LOCATIONS.UpdateLocations", conn);
upd.CommandType = CommandType.StoredProcedure;
upd.Parameters.Add("p_location_id", OracleType.Number, 4, "LOCATION_ID");
upd.Parameters.Add("p_street_address", OracleType.VarChar, 40, "STREET_ADDRESS");
... // 其他欄位對應 SourceColumn
da.UpdateCommand = upd;

// DeleteCommand / InsertCommand 同理...
da.DeleteCommand = delCmd;
da.InsertCommand = insCmd;

// 使用
DataTable dt = new DataTable();
da.Fill(dt);
// 修改 dt 資料...
da.Update(dt);  // 批次回寫

關鍵：Update/Insert/Delete 參數的 SourceColumn 屬性對應 DataTable 欄位名，ADO.NET 自動取值傳遞。

多結果集填充 DataSet 並建立關聯

OracleCommand cmd = new OracleCommand("PKG.GET_EMPS_JOBS", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("cur_Emps", OracleType.Cursor).Direction = ParameterDirection.Output;
cmd.Parameters.Add("cur_Jobs", OracleType.Cursor).Direction = ParameterDirection.Output;

OracleDataAdapter da = new OracleDataAdapter(cmd);
da.TableMappings.Add("Table", "EMPLOYEES");
da.TableMappings.Add("Table1", "JOBS");

DataSet ds = new DataSet();
da.Fill(ds);

// 建立 DataRelation
ds.Relations.Add("EMP_JOB",
    ds.Tables["JOBS"].Columns["JOB_ID"],
    ds.Tables["EMPLOYEES"].Columns["JOB_ID"]);

// 導航關聯
DataRow emp = ds.Tables["EMPLOYEES"].Rows[1];
string jobTitle = emp.GetParentRow("EMP_JOB")["JOB_TITLE"].ToString();

核心差異總結：Oracle vs SQL Server 存儲過程

結語

掌握 REF CURSOR、CRUD 套件封裝、DataAdapter 手工命令綁定，即可在 .NET 中高效、安全地操作 Oracle 9i/10g 存儲過程。