使用 ADO.NET 存取 Oracle 9i 預存程序 (上)

概述

本文討論如何使用 ADO.NET 存取 Oracle 預存程序（稱為 SQL 程式區塊）和函數（回傳單一值的程式區塊）。可使用以下受控資料提供者連接 Oracle 資料庫：Microsoft .NET Oracle 提供者、OLE DB .NET 提供者、ODBC .NET 資料提供者以及 Oracle 的 ODP.NET 提供者。本文使用適用於 Oracle 的 Microsoft .NET Framework 資料提供者。使用 Oracle ODP.NET 或適用於 OLE DB 的 Microsoft .NET Framework 資料提供者時，可使用不同功能。

Oracle .NET 資料提供者隨 .NET Framework 1.1 一起提供。若使用 .NET Framework 1.0，需下載 .NET Managed Provider for Oracle。無論哪個版本，資料提供者類別都位於 System.Data.OracleClient 命名空間中。

PL/SQL 簡介

PL/SQL 是 SQL 的 Oracle 實作，類似 Microsoft SQL Server 使用的 T-SQL，但也有差異。與 T-SQL 一樣，PL/SQL 擴充了標準 SQL，用於定義命名程式區塊，如預存程序、函數和觸發器。

核心類別

可使用 System.Data.OracleClient 命名空間中類別的子集來執行 Oracle 預存程序和函數：

執行預存程序步驟

1. 建立預存程序

CREATE OR REPLACE 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 參考加入專案，並使用 using System.Data.OracleClient; 匯入類型。

3. 建立連線與命令物件

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;

若預存程序名稱含特殊字元，需使用逸出序列。可透過重設 CommandText 重複使用現有 OracleCommand 物件。

4. 建立參數

cmd.Parameters.Add("reccount", OracleType.Number).Direction = 
  ParameterDirection.Output;

此為以下兩行程式碼的簡寫：

cmd.Parameters.Add("reccount", OracleType.Number);
cmd.Parameters["reccount"].Direction = ParameterDirection.Output;

5. 執行與處理結果

若需擷取結果集，請建立 DataSet、DataTable 或 DataReader。本範例僅取得輸出參數中的計數。

conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
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 資料庫中受影響的列，可用於優化後續相關查詢。

也可使用 ExecuteNonQuery() 執行不傳回資料的預存程序，但無法取得上述唯一列識別碼。雖然這些命令不傳回資料，但對應到參數的輸出參數和傳回值仍會以資料填充，可用於從預存程序傳回一或多個純量值。

範例：刪除員工工作經歷

CREATE OR REPLACE PROCEDURE DELETE_JOB_HISTORY(
  p_employee_id NUMBER
) IS
BEGIN
  DELETE FROM job_history WHERE employee_id = p_employee_id;
END DELETE_JOB_HISTORY;

OracleConnection conn = new OracleConnection(
  "Data Source=Oracledb;User Id=UserID;Password=Password;");
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "DELETE_JOB_HISTORY";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("p_employee_id", OracleType.Number).Value = 102;
conn.Open();
int rowsAffected = cmd.ExecuteNonQuery();
conn.Close();
Console.WriteLine("Rows affected: " + rowsAffected);

輸出：Rows affected: 1

存取傳回值

Oracle 預存程序中的 RETURN 陳述式會立即將控制權從預存程序傳回呼叫程式，但無法像 T-SQL 那樣傳回值。Oracle 函數是計算並傳回單一值的子程序，結構類似預存程序，不同之處在於它們總是具有必須傳回值的 RETURN 子句。

範例：取得員工電子郵件

CREATE OR REPLACE 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 參數取得函數傳回的結果：

OracleConnection conn = new OracleConnection(
  "Data Source=Oracledb;User Id=UserID;Password=Password;");
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "GET_EMPLOYEE_EMAIL";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("p_employee_id", OracleType.Number).Value = 101;
cmd.Parameters.Add("p_email", OracleType.VarChar, 25).Direction = 
  ParameterDirection.ReturnValue;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
Console.WriteLine("Email address is: " + cmd.Parameters["p_email"].Value);

輸出：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 套件結構。套件是邏輯上相關程式區塊（如預存程序和函數）的容器，包含兩部分：

• 規格：定義套件名稱，為套件中每個預存程序或函數提供方法簽章（原型）。規格標頭也定義所有全域宣告，風格類似 C/C++ 標頭檔。
• 主體：包含套件規格中定義的預存程序和函數程式碼。

每個預存程序或函數的參數都出現在括號內，以逗號分隔。每個參數依需求用以下三個識別碼之一標記：

• IN：值從呼叫應用程式傳遞到 PL/SQL 區塊。若未指定識別碼，IN 為預設傳遞方向。
• OUT：值由預存程序產生，傳回呼叫應用程式。
• INOUT：值傳遞到 PL/SQL 區塊，可能在內部修改，再傳回呼叫應用程式。

套件範例：CRUD_LOCATIONS

CREATE OR REPLACE 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 REPLACE PACKAGE BODY CRUD_LOCATIONS AS
  PROCEDURE GetLocations (cur_Locations OUT T_CURSOR) IS
  BEGIN
    OPEN cur_Locations FOR SELECT * FROM LOCATIONS;
  END GetLocations;
  -- 其他程序實作省略
END CRUD_LOCATIONS;

使用 DataReader

可透過呼叫 OracleCommand 物件的 ExecuteReader() 方法建立 OracleDataReader。以下示範如何使用 DataReader 存取由預存程序 SELECT_JOB_HISTORY.GetJobHistoryByEmployeeId 傳回的結果集。

套件規格

CREATE OR REPLACE 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;

套件主體

CREATE OR REPLACE 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;

執行程式碼

OracleConnection conn = new OracleConnection(
  "Data Source=Oracledb;User Id=UserID;Password=Password;");
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT_JOB_HISTORY.GetJobHistoryByEmployeeId";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("p_employee_id", OracleType.Number).Value = 101;
cmd.Parameters.Add("cur_JobHistory", OracleType.Cursor).Direction = 
  ParameterDirection.Output;
conn.Open();
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read()) {
  for (int i = 0; i < dr.FieldCount; i++)
    Console.Write(dr[i].ToString() + ";");
  Console.WriteLine();
}
conn.Close();