熱點推薦：
您现在的位置： 電腦知識網 >> 編程 >> .NET編程 >> 正文

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

2022-06-13   來源: .NET編程 

　　對於 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 過程與 MicrosoftSQL Server 存儲過程之間的主要區別是Oracle 過程必須將值作為輸出參數返回並且必須使用輸出參數將結果集作為 REF CURSOR 對象返回給調用程序


From:http://tw.wingwit.com/Article/program/net/201311/11377.html
    《ADO.NET訪問Oracle 9i存儲過程(下)》本文是由.NET編程免費提供，內容來源於互聯網,本文歸原作者所有。
  • 上一篇文章：

  • 下一篇文章：
    • 相關文章
    主板 CPU 硬盤 內存 顯卡 聲卡 顯示器 光驅 死機
    推薦文章
    Copyright © 2005-2022 電腦知識網 Computer Knowledge   All rights reserved.