您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

使用 OLE DB 與 ADO 呼叫回傳記錄集的 Oracle 預存程序

編輯：Oracle 資料庫基礎

前言

在以 Oracle 資料庫為基礎的 ADO 應用程式開發中，為了提升執行效能並降低網路流量，常需在應用程式中呼叫 Oracle 資料庫伺服器端的預存程序。部分預存程序需要回傳多行記錄集，這在應用程式中呼叫時存在一定難度。本文將詳細介紹如何使用 OLE DB 和 ADO 呼叫回傳記錄集的 Oracle 預存程序。

OLE DB 與 ADO 的關係

OLE DB 簡介

OLE DB 建立在 ODBC 成功的基礎上，是一種開放規範，為存取和操作不同類型資料提供開放標準。OLE DB 定義了一組 COM 介面，封裝了各種資料庫管理系統服務，這些介面允許建立實現這些服務的軟體元件。OLE DB 元件包含資料提供者（儲存並發佈資料）、資料消費者（使用資料）和服務元件（處理並傳輸資料）。

OLE DB 的設計以消費者和提供者概念為核心。消費者代表傳統的客戶端，提供者以表格形式將資料傳遞給消費者。OLE DB 的 Oracle 提供者 (OraOLEDB) 可讓 OLE DB 消費者高效存取 Oracle 資料來源。透過標準介面，OLE DB 消費者能夠存取來自提供者的資料，且因採用 COM 元件，消費者可使用任何支援 COM 的程式語言存取資料。

ADO 物件模型

在 ADO 物件模型中，Connection、Command 和 Recordset 物件是三個主要物件：

Connection 物件：代表對遠端資料的連線，可與 Command 或 Recordset 物件關聯。
Command 物件：定義對資料來源執行的指定命令，可用於執行命令、參數化 SQL 陳述式、SQL 查詢等。包含 Parameters 集合，每個 Parameter 物件代表一個參數。
Recordset 物件：代表來自基底表或命令執行結果的完整記錄集，允許查詢和修改資料，包含 Fields 集合，每個 Field 物件代表一個資料欄。

ADO 與 OLE DB 的關係

OLE DB 是系統層級程式介面，ADO 是應用軟體層級程式介面。ADO 是 OLE DB 的一個消費者，提供對 OLE DB 資料來源應用層級的存取功能，為 OLE DB 提供易用的應用層介面。主要優點包括易用性、高速度、低記憶體佔用和較少的磁碟空間佔用。

Oracle 中回傳記錄集的預存程序定義

在 Oracle 預存程序中，透過定義型別為 REF CURSOR（游標指標）的輸出參數，可以回傳記錄集。OLE DB 允許消費者執行帶 REF CURSOR 型別參數的 PL/SQL 預存程序，或執行回傳 REF CURSOR 的預存函數。回傳記錄集的預存程序或函數必須定義在套件中。套件是 Oracle 資料庫的一種物件，可將資料型別、預存程序、函數、變數和常數封裝在一起，結構上分為套件規格和套件主體兩部分。

使用套件定義回傳記錄集的預存程序時，需在套件規格中預先定義自己的 REF CURSOR 型別。在套件主體中，預存程序或函數須使用預先定義的游標型別。此外，一個預存程序或函數中可定義多個 REF CURSOR 型別參數以回傳多個記錄集。

範例：職工表 emp 的預存程序與函數

CREATE TABLE emp(
  empno NUMBER(4) NOT NULL, -- 職工編號
  ename VARCHAR2(10),       -- 職工姓名
  hiredate DATE,            -- 受僱日期
  sal NUMBER(7,2),          -- 薪資
  deptno NUMBER(2)          -- 所屬部門編號
);

-- 套件規格
CREATE OR REPLACE PACKAGE Employees AS
  TYPE empcur IS REF CURSOR;  -- 自訂 REF CURSOR 型別
  PROCEDURE GetEmpRecords(p_cursor OUT empcur,
                          q_cursor OUT empcur,
                          indeptno IN NUMBER,
                          p_errorcode OUT NUMBER);
  FUNCTION GetDept(inempno IN NUMBER,
                   p_errorcode OUT NUMBER) RETURN empcur;
END Employees;

-- 套件主體
CREATE OR REPLACE PACKAGE BODY Employees AS
  PROCEDURE GetEmpRecords(p_cursor OUT empcur, q_cursor OUT empcur,
                          indeptno IN NUMBER, p_errorcode OUT NUMBER) IS
  BEGIN
    p_errorcode := 0;
    OPEN p_cursor FOR
      SELECT * FROM emp WHERE deptno = indeptno ORDER BY empno;
    OPEN q_cursor FOR
      SELECT empno FROM emp WHERE deptno = indeptno ORDER BY empno;
  EXCEPTION
    WHEN OTHERS THEN p_errorcode := SQLCODE;
  END GetEmpRecords;

  FUNCTION GetDept(inempno IN NUMBER, p_errorcode OUT NUMBER) RETURN empcur IS
    p_cursor empcur;
  BEGIN
    p_errorcode := 0;
    OPEN p_cursor FOR SELECT deptno FROM emp WHERE empno = inempno;
    RETURN p_cursor;
  EXCEPTION
    WHEN OTHERS THEN p_errorcode := SQLCODE;
  END GetDept;
END Employees;

利用 ADO 呼叫回傳記錄集的 Oracle 預存程序

使用 OLE DB 和 ADO 呼叫回傳記錄集的 Oracle 預存程序時，在定義 ADO 物件和設定參數時需要特定設定。

4.1 設定資料庫連接字串

在 ADO 應用程式中存取回傳記錄集的 Oracle 預存程序，需使用 OraOLEDB 的資料庫連接字串指定格式。連接字串中的 PLSQLRSet 屬性指示 OraOLEDB 是否需從 PL/SQL 預存程序回傳記錄集：需回傳時設為 1，否則為 0（預設）。Command 命令執行後，該屬性需設回 0。

4.2 參數設定

可使用 Command 物件的 CreateParameter 方法建立 Parameter 物件，並用 Append 方法加入 Parameters 集合。呼叫預存程序時，Parameters 集合將參數傳遞給預存程序。因 OraOLEDB 採用位置傳遞參數格式，呼叫參數位置須與預存程序參數位置對應。ADO 中 Append 方法新增參數的順序應與預存程序參數順序一致。

OLE DB 標準中無 REF CURSOR 預定義資料型別，故呼叫預存程序時無法建立 REF CURSOR 型別參數。ADO 呼叫回傳記錄集的預存程序時，OLE DB 會自動為預存程序中 REF CURSOR 型別的輸出參數回傳記錄集，該記錄集可指派給 Recordset 物件。若 PL/SQL 預存程序有一或多個 REF CURSOR 型別輸出參數，OLE DB 將這些參數的記錄集整合，並透過第一個 REF CURSOR 輸出參數回傳，同樣可指派給一個 Recordset 物件。

4.3 呼叫預存程序的格式

呼叫回傳記錄集預存程序前，應將 Command 物件的 CommandType 屬性指定 SQL 型別以優化查詢效能，即設為 adCmdText。使用 CommandText 屬性定義呼叫預存程序的命令，執行 Oracle 回傳記錄集預存程序時，必須使用 ODBC 逸出符號呼叫格式，例如：{call credit_account(?,?)}。此格式中，輸入和輸出參數均用問號「?」表示。

4.4 回傳記錄集

可使用 Command 物件的 Execute 方法呼叫 Oracle 回傳記錄集的預存程序，並在適當時機將結果記錄集回傳給 Recordset 物件。該物件可儲存一或多個結果記錄集。儲存多個記錄集時，可用 Recordset 物件的 NextRecordset 屬性取得下一個記錄集。

ASP 範例程式

<%@ Language=VBScript %>
<!--#include file="adovbs.inc"-->
<%
set Con = Server.CreateObject("ADODB.Connection")
set Rst1 = Server.CreateObject("ADODB.Recordset")
set Rst2 = Server.CreateObject("ADODB.Recordset")
set Cmd = Server.CreateObject("ADODB.Command")
set Prm1 = Server.CreateObject("ADODB.Parameter")
set Prm2 = Server.CreateObject("ADODB.Parameter")

Con.Provider = "OraOLEDB.Oracle"
Con.ConnectionString = "Data Source=TEST;" & _
  "User ID=scott;Password=tiger;PLSQLRSet=1"
Con.Open

Cmd.ActiveConnection = Con
Cmd.CommandType = adCmdText

' 雖然 Employees.GetEmpRecords() 有四個參數，但只需設定兩個
' 因為 REF CURSOR 參數由 OLE DB 提供者自動設定
Set Prm1 = Cmd.CreateParameter("Prm1", adSmallInt, adParamInput, , 30)
Cmd.Parameters.Append Prm1
Set Prm2 = Cmd.CreateParameter("Prm2", adSmallInt, adParamOutput)
Cmd.Parameters.Append Prm2

' 必須使用 ODBC 逸出符號呼叫格式
Cmd.CommandText = "{CALL Employees.GetEmpRecords(?, ?)}"

' 取得第一個記錄集
Set Rst1 = Cmd.Execute
%>
<TABLE border="1">
<%
Do While Not Rst1.EOF
%>
  <tr>
  <% For Index = 0 To Rst1.Fields.Count - 1 %>
    <TD VAlign="top"><%= Rst1(Index) %></TD>
  <% Next %>
  </tr>
<%
  Rst1.MoveNext
Loop
%>
</TABLE>
<%
' 取得第二個記錄集
Set Rst2 = Rst1.NextRecordset
' 此處可加入顯示第二個記錄集的程式碼

' 呼叫預存函數
Prm1.Value = 7369
Prm2.Value = 0
Cmd.CommandText = "{CALL Employees.GetDept(?, ?)}"
Set Rst2 = Cmd.Execute

Rst1.Close
Rst2.Close
Set Rst1 = Nothing
Set Rst2 = Nothing
Con.Close
Set Con = Nothing
%>