本文討論了如何使用 ADO.Net 訪問 Oracle 存儲過程（稱為 SQL 編程塊）和函數（返回單個值的編程塊）。
您可以使用以下托管數據提供程序連接到 Oracle 數據庫：Microsoft .NET Oracle 提供程序、OLE DB .NET 提供程序、ODBC .NET 數據提供程序以及 Oracle 的 ODP.NET 提供程序。本文使用用於 Oracle 的 Microsoft?.NET 框架數據提供程序。使用 Oracle ODP.NET 數據提供程序或用於 OLE DB 的 Microsoft .Net 框架數據提供程序時可使用不同的功能。
Oracle .NET 數據提供程序隨 .NET 框架 1.1 一起提供。如果您使用的是 .NET 框架 1.0，您將需要下載 .Net Managed Provider for Oracle。無論是哪個版本，數據提供程序類都位於 System.Data.OracleClIEnt 命名空間中。
概述
PL/SQL 是 SQL 的 Oracle 實現。它與 Microsoft?SQL Server? 所使用的 T-SQL 類似，但也有一些不同之處，本文稍後對此進行了詳細討論。與 T-SQL 一樣，PL/SQL 擴展了標准 SQL。PL/SQL 用於定義命名編程塊，如存儲過程、函數和觸發器。
類
可使用 System.Data.OracleClIEnt 命名空間中類的子集來執行 Oracle 存儲過程和函數。下表對這些類進行了說明：
類 說明
OracleCommand
針對 Oracle 數據庫執行的存儲過程的 SQL 語句。
OracleConnection
打開的數據庫連接。
OracleParameter
OracleCommand 的參數，也可能是它到 DataColumn 的映射。
OracleParameterCollection
OracleParameter 對象的集合。
OracleType
Oracle 數據類型和結構的枚舉。
執行存儲過程
執行 Oracle 存儲過程與執行 SQL Server 存儲過程類似。下面的步驟說明了如何執行 Oracle 存儲過程和檢索它返回的結果。
1.
在 HR 架構中創建一個名為 COUNT_JOB_HISTORY 的存儲過程，以計算 JOB_HISTORY 表中的記錄數。
CREATE OR new PROCEDURE COUNT_JOB_HISTORY
(
reccount OUT NUMBER
)
IS
BEGIN
SELECT COUNT(*) INTO reccount
FROM JOB_HISTORY;
END COUNT_JOB_HISTORY;
HR 架構是默認 Oracle 安裝中包含的一個示例。
2.
將 System.Data.OracleClIEnt.dll（用於 Oracle 的 Microsoft .Net 框架數據提供程序）的引用添加到項目中。
3.
使用 using 指令導入 OracleClIEnt 類中的類型。
using System.Data.OracleClIEnt;
4.
創建一個 OracleConnection 對象。
OracleConnection conn = new OracleConnection("Data Source=Oracledb;
User Id=UserID;Password=PassWord;");
用您的值替換 Oracle 數據庫的名稱、用戶名和密碼。
5.
創建一個 OracleCommand 對象。將其 Connection 屬性設置為第 4 步中創建的連接。將其 CommandText 設置為存儲過程的名稱，並將其 CommandText 屬性設置為 CommandType.StoredProcedure。當您調用第 8 步中介紹的一個 Execute() 方法時，該命令對象將執行指定的存儲過程。
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "COUNT_JOB_HISTORY";
cmd.CommandType = CommandType.StoredProcedure;
如果您的存儲過程名稱含有特殊字符，您就必須使用轉義序列。您可以通過重置 CommandText 屬性來重用現有的 OracleCommand 對象。
6.
創建輸入、輸出和返回值的 OracleParameter 對象，並將其添加到 OracleCommand 對象的參數集合中。
cmd.Parameters.Add("reccount", OracleType.Number).Direction =
ParameterDirection.Output;
該行代碼是以下兩行代碼的簡寫形式：
cmd.Parameters.Add("reccount", OracleType.Number);
cmd.Parameters["reccount"].Direction = ParameterDirection.Output;
7.
如果您要檢索結果集，請創建 DataSet、DataTable 或 DataReader。在本示例中，我們只是獲取第 6 步中創建的輸出參數中的計數。
8.
使用 OracleCommand 對象的一個 Execute 方法打開連接並執行存儲過程，如下所示：
方法 說明
ExecuteReader
通過執行能夠返回結果集的存儲過程生成 OracleDataReader。
ExecuteNonQuery
執行不返回結果集的查詢或過程，返回受影響的行數。
ExecuteOracleNonQuery
執行查詢，返回受影響的行數。
該方法還使用 OracleString 參數來返回 UPDATE、INSERT 或 DELETE 查詢所修改的最後一行的行 ID。
ExecuteScalar
執行一個查詢或過程，並且返回查詢或過程的返回值，或者將結果集第一行第一列的值作為 .Net 框架數據類型返回。
ExecuteOracleScalar
執行一個查詢或過程，並且返回查詢或過程的返回值，或者將結果集第一行第一列的值作為 OracleType 數據類型返回。
使用完連接後，不要忘記將其關閉。
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
如果您要使用 DataAdapter 來填充 DataTable 或 DataSet，可以依靠 DataAdapter 來打開和關閉連接。
9.
處理結果。在我們的示例中，可在顯示到控制台的輸出參數中得到記錄數：
Console.WriteLine(cmd.Parameters["reccount"].Value);
下面是在本示例中開發的用於執行存儲過程和檢索結果的代碼：
OracleConnection conn = new OracleConnection("Data Source=Oracledb;
User Id=UserID;Password=PassWord;");
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "COUNT_JOB_HISTORY";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("reccount", OracleType.Number).Direction =
ParameterDirection.Output;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
Console.WriteLine(cmd.Parameters["reccount"].Value);
不返回數據的存儲過程
OracleCommand 類的 ExecuteOracleNonQuery() 方法用於執行不返回任何行的 SQL 語句或存儲過程。該方法返回一個 int 值，表示受 UPDATE、INSERT 和 DELETE 命令影響的行數；如果沒有任何行受到影響，則返回 -1。如果您所執行的 INSERT、DELETE 或 UPDATE 語句恰好影響一行，則該方法具有單個參數 OracleString out rowid，該參數唯一標識 Oracle 數據庫中受影響的行。可以使用該值來優化後續相關查詢。
還可以使用 OracleCommand 類的 ExecuteNonQuery() 方法來執行不返回數據的存儲過程，但您將無法獲得上面介紹的唯一行標識符。
盡管上述命令都不會返回任何數據，但映射到參數的輸出參數和返回值仍然使用數據進行填充。這使您可以使用上述任一命令從存儲過程返回一個或多個標量值。
以下 Oracle 存儲過程刪除了由單個輸入參數指定的員工的所有工作經歷，並且不返回任何數據。
CREATE OR new PROCEDURE DELETE_JOB_HISTORY
(
p_employee_id NUMBER
)
IS
BEGIN
DELETE FROM job_history
WHERE employee_id = p_employee_id;
END DELETE_JOB_HISTORY;
以下代碼運行了該存儲過程。
// 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 = "COUNT_JOB_HISTORY";
cmd.CommandType = CommandType.StoredProcedure;
// add the parameter specifying the employee for whom to delete records
cmd.Parameters.Add("p_employee_id", OracleType.Number).Value = 102;
OracleString rowId;
// execute the stored procedure
conn.Open();
int rowsAffected = cmd.ExecuteNonQuery();
conn.Close();
Console.WriteLine("Rows affected: " + rowsAffected);
如果您尚未修改默認的 HR 安裝，則 JOB_HISTORY 表中員工 102 的記錄被刪除，並且向控制台輸出以下內容：
Rows affected: 1
訪問返回值
RETURN 語句立即將控制從存儲過程返回到調用程序。Oracle 存儲過程中的 RETURN 語句無法像在 T-SQL 中那樣返回值。
Oracle 函數是計算並返回單個值的子程序。它們的結構類似於存儲過程，不同之處在於它們總是具有必須返回值的 RETURN 子句。
下面是一個返回指定員工的電子郵件的函數：
CREATE OR new FUNCTION GET_EMPLOYEE_EMAIL (
p_employee_id NUMBER
)
RETURN VARCHAR2
IS p_email VARCHAR2(25);
BEGIN
SELECT EMAIL INTO p_email FROM EMPLOYEES
WHERE EMPLOYEE_ID = p_employee_id;
RETURN p_email;
END GET_EMPLOYEE_EMAIL;
執行函數的方式與執行存儲過程的方式相同。可使用 ParameterDirection.ReturnValue 參數獲得由函數返回的結果。以下代碼顯示了使用方法：
// create the connection
OracleConnection conn = new OracleConnection("Data Source=Oracledb;
User Id=UserID;Password=PassWord;");
// create the command for the function
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "GET_EMPLOYEE_EMAIL";
cmd.CommandType = CommandType.StoredProcedure;
// add the parameters, including the return parameter to retrIEve
// the return value
cmd.Parameters.Add("p_employee_id", OracleType.Number).Value = 101;
cmd.Parameters.Add("p_email", OracleType.VarChar, 25).Direction =
ParameterDirection.ReturnValue;
// execute the function
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
// output the result
Console.WriteLine("Email address is: " + cmd.Parameters["p_email"].Value);
控制台輸出顯示了員工 101 的電子郵件地址。
Email address is: NKOCHHAR
結果集與 REF CURSOR
可使用 REF CURSOR 數據類型來處理 Oracle 結果集。REF CURSOR 是一個指向 PL/SQL 查詢所返回的結果集的指針。與普通的游標不同，REF CURSOR 是一個變量，它是對游標的引用，可以在執行時將其設置為指向不同的結果集。使用 REF CURSOR 輸出參數可以將 Oracle 結構化程序的結果集傳遞回調用應用程序。通過在調用應用程序中定義 OracleType.Cursor 數據類型的輸出參數，可以訪問 REF CURSOR 所指向的結果集。在使用 REF CURSOR 的過程中，OracleConnection 必須保持打開狀態。
包
PL/SQL 和 T-SQL 中的存儲過程之間的一個重大差異是 PL/SQL 所使用的 Oracle 包 結構。在 T-SQL 中沒有等效元素。包是在邏輯上相關的編程塊（如存儲過程和函數）的容器。它包含兩個部分：
• 規范：定義包的名稱，並為包中的每個存儲過程或函數提供方法簽名（原型）。規范頭還定義所有全局聲明。規范的樣式類似於 C 或 C++ 頭文件。
• 正文：包含包頭中定義的存儲過程和函數的代碼。
每個存儲過程或函數的參數都出現在括號內，並且用逗號分隔。每個參數還根據需要用以下三個標識符中的一個進行標記：
• IN：該值從調用應用程序傳遞到 PL/SQL 塊。如果未指定標識符，則 IN 為默認傳遞方向。
• OUT：該值由存儲過程生成，並傳遞回調用應用程序。
• INOUT：該值被傳遞到 PL/SQL 塊，可能在該塊內部進行修改，然後返回到調用應用程序。
每個參數也都被標記以指示數據類型。
以下包規范定義了四個過程，它們在 HR 架構的 LOCATIONS 表中創建、檢索、更新和刪除數據。
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;
以下代碼摘自上述包規范的包正文，說明了 GetLocations 包中的第一個過程的實現細節：
CREATE OR new PACKAGE BODY CRUD_LOCATIONS AS
PROCEDURE GetLocations (cur_Locations OUT T_CURSOR)
IS
BEGIN
OPEN cur_Locations FOR
SELECT * FROM LOCATIONS;
END GetLocations;
-- Implementation of other procedures ommitted.
END CRUD_LOCATIONS;
使用 DataReader
可以通過調用 OracleCommand 對象的 ExecuteReader() 方法來創建 OracleDataReader。本節說明如何使用 DataReader 來訪問由存儲過程 SELECT_JOB_HISTORY 返回的結果集。以下為包規范：
CREATE OR new PACKAGE SELECT_JOB_HISTORY AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetJobHistoryByEmployeeId
(
p_employee_id IN NUMBER,
cur_JobHistory OUT T_CURSOR
);
END SELECT_JOB_HISTORY;
包正文定義了一個過程，該過程檢索指定員工的工作經歷的結果集，並將其作為 REF CURSOR 輸出參數返回：
CREATE OR new PACKAGE BODY SELECT_JOB_HISTORY AS
PROCEDURE GetJobHistoryByEmployeeId
(
p_employee_id IN NUMBER,
cur_JobHistory OUT T_CURSOR
)
IS
BEGIN
OPEN cur_JobHistory FOR
SELECT * FROM JOB_HISTORY
WHERE employee_id = p_employee_id;
END GetJobHistoryByEmployeeId;
END SELECT_JOB_HISTORY;
以下代碼執行該過程，根據結果集創建 DataReader，並將 DataReader 的內容輸出到控制台。
// create 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;
// open the connection and create the DataReader
conn.Open();
OracleDataReader dr = cmd.ExecuteReader();
// output the results and close the connection.
while(dr.Read())
{
for(int i = 0; i < dr.FIEldCount; i++)
Console.Write(dr[i].ToString() + ";");
Console.WriteLine();
}
conn.Close();
