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

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

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

　　本文討論了如何使用 ADONET訪問 Oracle存儲過程（稱為 SQL 編程塊）和函數（返回單個值的編程塊）

　　您可以使用以下托管數據提供程序連接到 Oracle 數據庫MicrosoftNET 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();


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

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