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

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

2013-11-13 10:28:51  來源: .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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.