本文討論了如何使用 ADONET 訪問 Oracle 存儲過程(稱為 SQL 編程塊)和函數(返回單個值的編程塊)
您可以使用以下托管數據提供程序連接到 Oracle 數據庫Microsoft NET Oracle 提供程序OLE DB NET 提供程序ODBC NET 數據提供程序以及 Oracle 的 ODPNET 提供程序本文使用用於 Oracle 的 Microsoft?NET 框架數據提供程序使用 Oracle ODPNET 數據提供程序或用於 OLE DB 的 Microsoft NET 框架數據提供程序時可使用不同的功能
Oracle NET 數據提供程序隨 NET 框架 一起提供如果您使用的是 NET 框架 您將需要下載 NET Managed Provider for Oracle無論是哪個版本數據提供程序類都位於 SystemDataOracleClient 命名空間中
概述
PL/SQL 是 SQL 的 Oracle 實現它與 Microsoft?SQL Server? 所使用的 TSQL 類似但也有一些不同之處本文稍後對此進行了詳細討論與 TSQL 一樣PL/SQL 擴展了標准 SQLPL/SQL 用於定義命名編程塊如存儲過程函數和觸發器
類
可使用 SystemDataOracleClient 命名空間中類的子集來執行 Oracle 存儲過程和函數下表對這些類進行了說明
類
說明
OracleCommand
針對 Oracle 數據庫執行的存儲過程的 SQL 語句
OracleConnection
打開的數據庫連接
OracleParameter
OracleCommand 的參數也可能是它到 DataColumn 的映射
OracleParameterCollection
OracleParameter 對象的集合
OracleType
Oracle 數據類型和結構的枚舉
執行存儲過程
執行 Oracle 存儲過程與執行 SQL Server 存儲過程類似下面的步驟說明了如何執行 Oracle 存儲過程和檢索它返回的結果
在 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 安裝中包含的一個示例
將 SystemDataOracleClientdll(用於 Oracle 的 Microsoft NET 框架數據提供程序)的引用添加到項目中
使用 using 指令導入 OracleClient 類中的類型
using SystemDataOracleClient;
創建一個 OracleConnection 對象
OracleConnection conn = new OracleConnection(Data Source=oracledb;
User Id=UserID;Password=Password;);
用您的值替換 Oracle 數據庫的名稱用戶名和密碼
創建一個 OracleCommand 對象將其 Connection 屬性設置為第 步中創建的連接將其 CommandText 設置為存儲過程的名稱並將其 CommandText 屬性設置為 CommandTypeStoredProcedure當您調用第 步中介紹的一個 Execute() 方法時該命令對象將執行指定的存儲過程
OracleCommand cmd = new OracleCommand();
cmdConnection = conn;
cmdCommandText = COUNT_JOB_HISTORY;
cmdCommandType = CommandTypeStoredProcedure;
如果您的存儲過程名稱含有特殊字符您就必須使用轉義序列您可以通過重置 CommandText 屬性來重用現有的 OracleCommand 對象
創建輸入輸出和返回值的 OracleParameter 對象並將其添加到 OracleCommand 對象的參數集合中
cmdParametersAdd(reccount OracleTypeNumber)Direction =
ParameterDirectionOutput;
該行代碼是以下兩行代碼的簡寫形式
cmdParametersAdd(reccount OracleTypeNumber);
cmdParameters[reccount]Direction = ParameterDirectionOutput;
如果您要檢索結果集請創建 DataSetDataTable 或 DataReader在本示例中我們只是獲取第 步中創建的輸出參數中的計數
使用 OracleCommand 對象的一個 Execute 方法打開連接並執行存儲過程如下所示
方法
說明
ExecuteReader
通過執行能夠返回結果集的存儲過程生成 OracleDataReader
ExecuteNonQuery
執行不返回結果集的查詢或過程返回受影響的行數
ExecuteOracleNonQuery
執行查詢返回受影響的行數
該方法還使用 OracleString 參數來返回 UPDATEINSERT 或 DELETE 查詢所修改的最後一行的行 ID
ExecuteScalar
執行一個查詢或過程並且返回查詢或過程的返回值或者將結果集第一行第一列的值作為 NET 框架數據類型返回
ExecuteOracleScalar
執行一個查詢或過程並且返回查詢或過程的返回值或者將結果集第一行第一列的值作為 OracleType 數據類型返回
使用完連接後不要忘記將其關閉
connOpen();
cmdExecuteNonQuery();
connClose();
如果您要使用 DataAdapter 來填充 DataTable 或 DataSet可以依靠 DataAdapter 來打開和關閉連接
處理結果在我們的示例中可在顯示到控制台的輸出參數中得到記錄數
ConsoleWriteLine(cmdParameters[reccount]Value);
下面是在本示例中開發的用於執行存儲過程和檢索結果的代碼
OracleConnection conn = new OracleConnection(Data Source=oracledb;
User Id=UserID;Password=Password;);
OracleCommand cmd = new OracleCommand();
cmdConnection = conn;
cmdCommandText = COUNT_JOB_HISTORY;
cmdCommandType = CommandTypeStoredProcedure;
cmdParametersAdd(reccount OracleTypeNumber)Direction =
ParameterDirectionOutput;
connOpen();
cmdExecuteNonQuery();
connClose();
ConsoleWriteLine(cmdParameters[reccount]Value);
不返回數據的存儲過程
OracleCommand 類的 ExecuteOracleNonQuery() 方法用於執行不返回任何行的 SQL 語句或存儲過程該方法返回一個 int 值表示受 UPDATEINSERT 和 DELETE 命令影響的行數如果沒有任何行受到影響則返回 如果您所執行的 INSERTDELETE 或 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();
cmdConnection = conn;
cmdCommandText = COUNT_JOB_HISTORY;
cmdCommandType = CommandTypeStoredProcedure;
// add the parameter specifying the employee for whom to delete records
cmdParametersAdd(p_employee_id OracleTypeNumber)Value = ;
OracleString rowId;
// execute the stored procedure
connOpen();
int rowsAffected = cmdExecuteNonQuery();
connClose();
ConsoleWriteLine(Rows affected: + rowsAffected);
如果您尚未修改默認的 HR 安裝則 JOB_HISTORY 表中員工 的記錄被刪除並且向控制台輸出以下內容
Rows affected:
訪問返回值
RETURN 語句立即將控制從存儲過程返回到調用程序Oracle 存儲過程中的 RETURN 語句無法像在 TSQL 中那樣返回值
Oracle 函數是計算並返回單個值的子程序它們的結構類似於存儲過程不同之處在於它們總是具有必須返回值的 RETURN 子句
下面是一個返回指定員工的電子郵件的函數
CREATE OR new FUNCTION GET_EMPLOYEE_EMAIL (
p_employee_id NUMBER
)
RETURN VARCHAR
IS p_email VARCHAR();
BEGIN
SELECT EMAIL INTO p_email FROM EMPLOYEES
WHERE EMPLOYEE_ID = p_employee_id;
RETURN p_email;
END GET_EMPLOYEE_EMAIL;
執行函數的方式與執行存儲過程的方式相同可使用 ParameterDirectionReturnValue 參數獲得由函數返回的結果以下代碼顯示了使用方法
// 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();
cmdConnection = conn;
cmdCommandText = GET_EMPLOYEE_EMAIL;
cmdCommandType = CommandTypeStoredProcedure;
// add the parameters including the return parameter to retrieve
// the return value
cmdParametersAdd(p_employee_id OracleTypeNumber)Value = ;
cmdParametersAdd(p_email OracleTypeVarChar )Direction =
ParameterDirectionReturnValue;
// execute the function
connOpen();
cmdExecuteNonQuery();
connClose();
// output the result
ConsoleWriteLine(Email address is: + cmdParameters[p_email]Value);
控制台輸出顯示了員工 的電子郵件地址
Email address is: NKOCHHAR
結果集與 REF CURSOR
可使用 REF CURSOR 數據類型來處理 Oracle 結果集REF CURSOR 是一個指向 PL/SQL 查詢所返回的結果集的指針與普通的游標不同REF CURSOR 是一個變量它是對游標的引用可以在執行時將其設置為指向不同的結果集使用 REF CURSOR 輸出參數可以將 Oracle 結構化程序的結果集傳遞回調用應用程序通過在調用應用程序中定義 OracleTypeCursor 數據類型的輸出參數可以訪問 REF CURSOR 所指向的結果集在使用 REF CURSOR 的過程中OracleConnection 必須保持打開狀態
包
PL/SQL 和 TSQL 中的存儲過程之間的一個重大差異是 PL/SQL 所使用的 Oracle 包 結構在 TSQL 中沒有等效元素包是在邏輯上相關的編程塊(如存儲過程和函數)的容器它包含兩個部分
;
規范定義包的名稱並為包中的每個存儲過程或函數提供方法簽名(原型)規范頭還定義所有全局聲明規范的樣式類似於 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 VARCHAR
p_postal_code IN VARCHAR
p_city IN VARCHAR
p_state_province IN VARCHAR
p_country_id IN CHAR);
PROCEDURE DeleteLocations (p_location_id IN NUMBER);
PROCEDURE InsertLocations (p_location_id OUT NUMBER
p_street_address IN VARCHAR
p_postal_code IN VARCHAR
p_city IN VARCHAR
p_state_province IN VARCHAR
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();
cmdConnection = conn;
cmdCommandText = SELECT_JOB_HISTORYGetJobHistoryByEmployeeId;
cmdCommandType = CommandTypeStoredProcedure;
// add the parameters for the stored procedure including the REF CURSOR
// to retrieve the result set
cmdParametersAdd(p_employee_id OracleTypeNumber)Value = ;
cmdParametersAdd(cur_JobHistory OracleTypeCursor)Direction =
ParameterDirectionOutput;
// open the connection and create the DataReader
connOpen();
OracleDataReader dr = cmdExecuteReader();
// output the results and close the connection
while(drRead())
{
for(int i = ; i < drFieldCount; i++)
ConsoleWrite(dr[i]ToString() + ;);
ConsoleWriteLine();
}
connClose();
對於 HR 架構的默認安裝控制台輸出顯示了員工 的兩個記錄中每個記錄的字段(用分號分隔)
;// :: AM;// :: AM;AC_ACCOUNT;;
;// :: AM;// :: AM;AC_MGR;;
上述代碼顯示包中的過程是使用包名稱 (ELECT_JOB_HISTORY) 和過程的名稱(在此情況下為 GetJobHistoryByEmployeeId)指定的二者之間用句點分隔
代碼還說明了如何定義結果集的 REF CURSOR 參數請注意數據類型為 OracleTypeCursor方向為 ParameterDirectionOutput
還請注意在訪問 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
START WITH
MAXVALUE
MINVALUE
NOCYCLE
NOCACHE
NOORDER
大多數序列代碼是不言自明的NOCYCLE 表示序列在達到最小值或最大值後將不再生成其他值NOCACHE 表示序列值在被請求之前不會進行分配可使用預分配機制來改善性能NOORDER 表示在生成編號時不能保證按照請求編號的順序返回這些編號
下面的代碼顯示了一個存儲過程該過程請求一個序列值在向 LOCATIONS 表中插入記錄時使用它設置主鍵值然後在 OUT 參數中返回該主鍵值
CREATE OR new PROCEDURE ADD_LOCATION (
p_location_id OUT NUMBER
p_street_address IN VARCHAR
p_postal_code IN VARCHAR
p_city IN VARCHAR
p_state_province IN VARCHAR
p_country_id IN CHAR
)
AS
BEGIN
INSERT INTO LOCATIONS (
LOCATION_ID
STREET_ADDRESS
POSTAL_CODE
CITY
STATE_PROVINCE
COUNTRY_ID)
VALUES (
LOCATIONS_SEQNEXTVAL
p_street_address
p_postal_code
p_city
p_state_province
p_country_id
);
SELECT LOCATIONS_SEQCURRVAL 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();
cmdConnection = conn;
cmdCommandText = ADD_LOCATION;
cmdCommandType = CommandTypeStoredProcedure;
// add the parameters for the stored procedure including the LOCATION_ID
// sequence value that is returned in the output parameter p_location_id
cmdParametersAdd(p_location_id OracleTypeNumber)Direction =
ParameterDirectionOutput;
cmdParametersAdd(p_street_address OracleTypeVarChar)Value =
Any Street;
cmdParametersAdd(p_postal_code OracleTypeVarChar)Value = ;
cmdParametersAdd(p_city OracleTypeVarChar)Value = Key West;
cmdParametersAdd(p_state_province OracleTypeVarChar)Value = FL;
cmdParametersAdd(p_country_id OracleTypeVarChar)Value = US;
// execute the command to add the records
OracleString rowId;
connOpen();
int rowsAffected = cmdExecuteOracleNonQuery(out rowId);
connClose();
// output the results
ConsoleWriteLine(Rows affected: + rowsAffected);
ConsoleWriteLine(Location ID: +
cmdParameters[p_location_id]Value);
控制台顯示一個記錄被插入到該表中同時還插入了該序列生成的主鍵值
Rows affected:
Location ID:
使用 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();
cmdConnection = conn;
cmdCommandText = SELECT_JOB_HISTORYGetJobHistoryByEmployeeId;
cmdCommandType = CommandTypeStoredProcedure;
// add the parameters for the stored procedure including the REF CURSOR
// to retrieve the result set
cmdParametersAdd(p_employee_id OracleTypeNumber)Value = ;
cmdParametersAdd(cur_JobHistory OracleTypeCursor)Direction =
ParameterDirectionOutput;
// createt the DataAdapter from the command and use it to fill the
// DataSet
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
daFill(ds);
// output the results
ConsoleWriteLine(dsTables[]RowsCount);
對於 HR 架構的默認安裝輸出表明員工 有兩個 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 VARCHAR
p_postal_code IN VARCHAR
p_city IN VARCHAR
p_state_province IN VARCHAR
p_country_id IN CHAR);
PROCEDURE DeleteLocations (p_location_id IN NUMBER);
PROCEDURE InsertLocations (
p_location_id OUT NUMBER
p_street_address IN VARCHAR
p_postal_code IN VARCHAR
p_city IN VARCHAR
p_state_province IN VARCHAR
p_country_id IN CHAR);
END CRUD_LOCATIONS;
包正文如下所示
CREATE OR new PACKAGE BODY CRUD_LOCATIONS AS
retrieve all LOCATION records
PROCEDURE GetLocations (cur_Locations OUT T_CURSOR)
IS
BEGIN
OPEN cur_Locations FOR
SELECT * FROM LOCATIONS;
END GetLocations;
update a LOCATION record
PROCEDURE UpdateLocations (
p_location_id IN NUMBER
p_street_address IN VARCHAR
p_postal_code IN VARCHAR
p_city IN VARCHAR
p_state_province IN VARCHAR
p_country_id IN CHAR)
IS
BEGIN
UPDATE LOCATIONS
SET
STREET_ADDRESS = p_street_address
POSTAL_CODE = p_postal_code
CITY = p_city
STATE_PROVINCE = p_state_province
COUNTRY_ID = p_country_id
WHERE
LOCATION_ID = p_location_id;
END UpdateLocations;
delete a LOCATION record
PROCEDURE DeleteLocations (p_location_id IN NUMBER)
IS
BEGIN
DELETE FROM LOCATIONS
WHERE LOCATION_ID = p_location_id;
END DeleteLocations;
insert a LOCATION record
PROCEDURE InsertLocations
(
p_location_id OUT NUMBER
p_street_address IN VARCHAR
p_postal_code IN VARCHAR
p_city IN VARCHAR
p_state_province IN VARCHAR
p_country_id IN CHAR
)
AS
BEGIN
INSERT INTO LOCATIONS (
LOCATION_ID
STREET_ADDRESS
POSTAL_CODE
CITY
STATE_PROVINCE
COUNTRY_ID)
VALUES (
LOCATIONS_SEQNEXTVAL
p_street_address
p_postal_code
p_city
p_state_province
p_country_id
);
SELECT LOCATIONS_SEQCURRVAL INTO p_location_id FROM DUAL;
END InsertLocations;
END CRUD_LOCATIONS;
下面的代碼定義了一個 DataAdapter從而使用上述包中定義的過程來創建檢索更新和刪除支持 DataAdapter 的數據DataAdapter 既可用來將數據檢索到 DataSet 中也可用來將對 DataSet 所做的更改更新到 Oracle 數據庫中
// define the connection string
String connString = Data Source=oracledb;User Id=UserID;Password=Password;;
// create the data adapter
OracleDataAdapter da = new OracleDataAdapter();
// define the select command for the data adapter
OracleCommand selectCommand =
new OracleCommand(CRUD_LOCATIONSGetLocations
new OracleConnection(connString));
selectCommandCommandType = CommandTypeStoredProcedure;
selectCommandParametersAdd(cur_Locations
OracleTypeCursor)Direction = ParameterDirectionOutput;
daSelectCommand = selectCommand;
// define the udpate command for the data adapter
OracleCommand updateCommand =
new OracleCommand(CRUD_LOCATIONSUpdateLocations
new OracleConnection(connString));
updateCommandCommandType = CommandTypeStoredProcedure;
updateCommandParametersAdd(p_location_id OracleTypeNumber
LOCATION_ID);
updateCommandParametersAdd(p_street_address OracleTypeVarChar
STREET_ADDRESS);
updateCommandParametersAdd(p_postal_code OracleTypeVarChar
POSTAL_CODE);
updateCommandParametersAdd(p_city OracleTypeVarChar CITY);
updateCommandParametersAdd(p_state_province OracleTypeVarChar
STATE_PROVINCE);
updateCommandParametersAdd(p_country_id OracleTypeChar
COUNTRY_ID);
daUpdateCommand = updateCommand;
// define the delete command for the data adapter
OracleCommand deleteCommand =
new OracleCommand(CRUD_LOCATIONSDeleteLocations
new OracleConnection(connString));
deleteCommandCommandType = CommandTypeStoredProcedure;
deleteCommandParametersAdd(p_location_id OracleTypeNumber
LOCATION_ID);
daDeleteCommand = deleteCommand;
OracleCommand insertCommand =
new OracleCommand(CRUD_LOCATIONSInsertLocations
new OracleConnection(connString));
insertCommandCommandType = CommandTypeStoredProcedure;
insertCommandParametersAdd(p_location_id OracleTypeNumber
LOCATION_ID);
insertCommandParametersAdd(p_street_address OracleTypeVarChar
STREET_ADDRESS);
insertCommandParametersAdd(p_postal_code OracleTypeVarChar
POSTAL_CODE);
insertCommandParametersAdd(p_city OracleTypeVarChar CITY);
insertCommandParametersAdd(p_state_province OracleTypeVarChar
STATE_PROVINCE);
insertCommandParametersAdd(p_country_id OracleTypeChar
COUNTRY_ID);
daInsertCommand = insertCommand;
// define a DataTable and fill it using the data adapter
DataTable dt = new DataTable();
daFill(dt);
// do work that adds edits updates or deletes records in the table
// call the Update() method of the data adapter to update the Oracle
// database with changes made to the data
daUpdate(dt);
使用多個結果集
Oracle 不支持批量查詢因此無法從一個命令返回多個結果集使用存儲過程時返回多個結果集類似於返回單個結果集必須使用 REF CURSOR 輸出參數要返回多個結果集請使用多個 REF CURSOR 輸出參數
以下是返回兩個結果集(全部 EMPLOYEES 和 JOBS 記錄)的包規范
CREATE OR new PACKAGE SELECT_EMPLOYEES_JOBS AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetEmployeesAndJobs (
cur_Employees OUT T_CURSOR
cur_Jobs OUT T_CURSOR
);
END SELECT_EMPLOYEES_JOBS;
包正文如下所示
CREATE OR new PACKAGE BODY SELECT_EMPLOYEES_JOBS AS
PROCEDURE GetEmployeesAndJobs
(
cur_Employees OUT T_CURSOR
cur_Jobs OUT T_CURSOR
)
IS
BEGIN
return all EMPLOYEES records
OPEN cur_Employees FOR
SELECT * FROM Employees;
return all JOBS records
OPEN cur_Jobs FOR
SELECT * FROM Jobs;
END GetEmployeesAndJobs;
END SELECT_EMPLOYEES_JOBS;
以下代碼顯示了如何使用從上述包中返回的兩個結果集來填充 DataSet 中的兩個相關表
// create the connection
OracleConnection conn = new OracleConnection(Data Source=oracledb;
User Id=UserID;Password=Password;);
// define the command for the stored procedure
OracleCommand cmd = new OracleCommand();
cmdConnection = conn;
cmdCommandText = SELECT_EMPLOYEES_JOBSGetEmployeesAndJobs;
// add the parameters including the two REF CURSOR types to retrieve
// the two result sets
cmdParametersAdd(cur_Employees OracleTypeCursor)Direction =
ParameterDirectionOutput;
cmdParametersAdd(cur_Jobs OracleTypeCursor)Direction =
ParameterDirectionOutput;
cmdCommandType = CommandTypeStoredProcedure;
// create the DataAdapter and map tables
OracleDataAdapter da = new OracleDataAdapter(cmd);
daTableMappingsAdd(Table EMPLOYEES);
daTableMappingsAdd(Table JOBS);
// create and fill the DataSet
DataSet ds = new DataSet();
daFill(ds);
// create a relation
dsRelationsAdd(EMPLOYEES_JOBS_RELATION
dsTables[JOBS]Columns[JOB_ID]
dsTables[EMPLOYEES]Columns[JOB_ID]);
// output the second employee (zerobased array) and job title
// based on the relation
ConsoleWriteLine(Employee ID: +
dsTables[EMPLOYEES]Rows[][EMPLOYEE_ID] +
; Job Title: +
dsTables[EMPLOYEES]Rows[]GetParentRow(
EMPLOYEES_JOBS_RELATION)[JOB_TITLE]);
控制台輸出顯示了第二個員工的職務
Employee ID: ; Job Title: Administration Vice President
小結
通過 Oracle NET 數據提供程序可以方便地執行存儲過程以及訪問返回值(無論返回值是一個還是多個標量值或結果集)可以將 Oracle 過程與 OracleDataAdapter 結合使用從而填充 DataSet處理不連續的數據以及以後將更改更新到 Oracle 數據庫
Oracle 過程與 Microsoft SQL Server 存儲過程之間的主要區別是Oracle 過程必須將值作為輸出參數返回並且必須使用輸出參數將結果集作為 REF CURSOR 對象返回給調用程序
From:http://tw.wingwit.com/Article/program/Oracle/201311/18678.html