使用ADO.NET訪問Oracle 9i存儲過程(下)

對於 HR 架構的默認安裝，控制台輸出顯示了員工 101 的兩個記錄中每個記錄的字段（用分號分隔）：

101;9/21/1989 12:00:00 AM;10/27/1993 12:00:00 AM;AC_ACCOUNT;110;

101;10/28/1993 12:00:00 AM;3/15/1997 12:00:00 AM;AC_MGR;110;

上述代碼顯示，包中的過程是使用包名稱 (ELECT_JOB_HISTORY) 和過程的名稱（在此情況下為 GetJobHistoryByEmployeeId）指定的，二者之間用句點分隔。

代碼還說明了如何定義結果集的 REF CURSOR 參數。請注意，數據類型為 OracleType.Cursor，方向為 ParameterDirection.Output。

還請注意，在訪問 REF CURSOR 中的結果集的整個過程中，連接都保持打開狀態。

如果包返回多個游標，則 DataReader 會按照您向參數集合中添加它們的順序來訪問這些游標，而不是按照它們在過程中出現的順序來訪問。可使用 DataReader 的 NextResult() 方法前進到下一個游標。

返回單個值的存儲過程

OracleCommand 類的 ExecuteOracleScalar() 方法用於執行將單個值作為 OracleType 數據類型返回的 SQL 語句或存儲過程。如果命令返回一個結果集，則該方法會返回第一行第一列的值。如果返回了 REF CURSOR，而不是返回了 REF CURSOR 所指向的第一行第一列的值，則該方法會返回一個空引用。OracleCommand 類的 ExecuteScalar() 方法類似於 ExecuteOracleScalar() 方法，只不過它將值作為 .Net 框架數據類型返回。

盡管如此，在使用 Oracle 存儲過程時，這兩個方法都沒有用。Oracle 存儲過程不能將值作為 RETURN 語句的一部分返回，而只能將其作為 OUT 參數返回。有關信息，請參閱不返回數據的存儲過程一節。同時，除了通過 REF CURSOR 輸出參數以外，您不能返回結果集。下一節將對此進行討論。

您只能使用 RETURN 參數檢索 Oracle 函數的返回值（如上一節所述），而不能使用 ExecuteScalar 方法之一進行檢索。

序列

Oracle 使用序列 來生成唯一編號，而不是使用 SQL Server 所用的數據類型 uniqueidentifier。無論是哪種情況，主要用途都是為主鍵列生成一系列唯一編號。與 uniqueidentifIEr 數據類型不同，序列是與將其用於主鍵值的一個或多個表無關的數據庫對象。

Oracle 序列是原子對象，並且是一致的。也就是說，一旦您訪問一個序列號，Oracle 將在處理下一個請求之前自動遞增下一個編號，從而確保不會出現重復值。

可以使用 CREATE SEQUENCE 命令創建 Oracle 序列。該命令所帶參數包括增量、起始值、最大值、循環和緩存。可使用 NEXTVAL 和 CURRVAL 關鍵字訪問序列值。NEXTVAL 返回序列中的下一個編號，而 CURRVAL 提供對當前值的訪問。HR 架構中的序列 LOCATIONS_SEQ 按如下方式定義：

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

大多數序列代碼是不言自明的。NOCYCLE 表示序列在達到最小值或最大值後將不再生成其他值。NOCACHE 表示序列值在被請求之前不會進行分配；可使用預分配機制來改善性能。NOORDER 表示在生成編號時，不能保證按照請求編號的順序返回這些編號。

下面的代碼顯示了一個存儲過程，該過程請求一個序列值，在向 LOCATIONS 表中插入記錄時使用它設置主鍵值，然後在 OUT 參數中返回該主鍵值。

CREATE OR new PROCEDURE ADD_LOCATION (
　　p_location_id OUT NUMBER,
　　p_street_address IN VARCHAR2,
　　p_postal_code IN VARCHAR2,
　　p_city IN VARCHAR2,
　　p_state_province IN VARCHAR2,
　　p_country_id IN CHAR
)
AS
BEGIN
　　INSERT INTO LOCATIONS (
　　　　LOCATION_ID,
　　　　STREET_ADDRESS,
　　　　POSTAL_CODE,
　　　　CITY,
　　　　STATE_PROVINCE,
　　　　COUNTRY_ID)
　　VALUES (
　　　　LOCATIONS_SEQ.NEXTVAL,
　　　　p_street_address,
　　　　p_postal_code,
　　　　p_city,
　　　　p_state_province,
　　　　p_country_id
　　);
　
　　SELECT LOCATIONS_SEQ.CURRVAL INTO p_location_id FROM DUAL;
END ADD_LOCATION;

下面的代碼調用該存儲過程，以插入一個記錄並檢索返回的序列值。

// create the connection
OracleConnection conn = new OracleConnection("Data Source=Oracledb;
　　User Id=UserID;Password=PassWord;");
// create the command for the stored procedure
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "ADD_LOCATION";
cmd.CommandType = CommandType.StoredProcedure;
// add the parameters for the stored procedure including the LOCATION_ID
// sequence value that is returned in the output parameter p_location_id
cmd.Parameters.Add("p_location_id", OracleType.Number).Direction =
　　ParameterDirection.Output;
cmd.Parameters.Add("p_street_address", OracleType.VarChar).Value =
　　"123 Any Street";
cmd.Parameters.Add("p_postal_code", OracleType.VarChar).Value = "33040";
cmd.Parameters.Add("p_city", OracleType.VarChar).Value = "Key West";
cmd.Parameters.Add("p_state_province", OracleType.VarChar).Value = "FL";
cmd.Parameters.Add("p_country_id", OracleType.VarChar).Value = "US";
// execute the command to add the records　　
OracleString rowId;
conn.Open();
int rowsAffected = cmd.ExecuteOracleNonQuery(out rowId);
conn.Close();
// output the results
Console.WriteLine("Rows affected: " + rowsAffected);
Console.WriteLine("Location ID: " +
　　cmd.Parameters["p_location_id"].Value);

控制台顯示一個記錄被插入到該表中，同時還插入了該序列生成的主鍵值。

Rows affected: 1

Location ID: 3300

使用 DataAdapter 填充數據集

可使用 REF CURSOR 通過 DataAdapter 來填充 DataSet。下面的代碼利用了使用 DataReader 一節中定義的存儲過程 GetJobHistoryByEmployeeId，並用它在 REF CURSOR 輸出參數中返回的結果集來填充 DataSet。

以下是使用 DataAdapter 填充 DataSet 的代碼：

// create the connection
OracleConnection conn = new OracleConnection("Data Source=Oracledb;
　　User Id=UserID;Password=PassWord;");
// create the command for the stored procedure
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT_JOB_HISTORY.GetJobHistoryByEmployeeId";
cmd.CommandType = CommandType.StoredProcedure;
// add the parameters for the stored procedure including the REF CURSOR
// to retrIEve the result set
cmd.Parameters.Add("p_employee_id", OracleType.Number).Value = 101;
cmd.Parameters.Add("cur_JobHistory", OracleType.Cursor).Direction =
　　ParameterDirection.Output;
// createt the DataAdapter from the command and use it to fill the
// DataSet
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
// output the results.
Console.WriteLine(ds.Tables[0].Rows.Count);

對於 HR 架構的默認安裝，輸出表明員工 101 有兩個 JOB_HISTORY 記錄。

使用 DataAdapter 更新 Oracle

當您使用 REF CURSOR 參數填充 DataSet 時，不能簡單地使用 OracleDataAdapter 的 Update() 方法。這是因為在執行存儲過程時，Oracle 不能提供確定表名和列名所需的信息。要使用 DataAdapter 的 Update() 方法，您必須創建在基礎表中更新、插入和刪除記錄的過程。該方法類似於在 SQL Server 中使用的方法。

本節說明如何生成一個可以處理所需的創建、檢索、更新和刪除操作的包，以便能夠從 Oracle 數據庫中檢索 LOCATION 數據，也能夠將對 DataSet 數據所做的不連續更改重新更新到 Oracle 數據庫。包頭如下所示：

CREATE OR new 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,
　　p_postal_code IN VARCHAR2,
　　p_city IN VARCHAR2,
　　p_state_province IN VARCHAR2,
　　p_country_id IN CHAR);
PROCEDURE DeleteLocations (p_location_id IN NUMBER);
PROCEDURE InsertLocations (
　　p_location_id OUT NUMBER,
　　p_street_address IN VARCHAR2,
　　p_postal_code IN VARCHAR2,
　　p_city IN VARCHAR2,
　　p_state_province IN VARCHAR2,
　　p_country_id IN CHAR);
END CRUD_LOCATIONS;