摘要 OLE DB是建立在ODBC成功基礎上的一種開放規范,它為訪問和操縱不同類型數據提供開放的標准。ADO是OLD DB的一個消費者，它提供了對OLE DB數據源應用級的訪問功能。在應用程序中使用OLE DB和ADO，可以高效地調用返回記錄集的Oracle存儲過程。
關鍵字 OLE DB ADO 存儲過程 記錄集
1 前言
在基於Oracle數據庫的ADO應用程序的開發過程中，為了提高執行速度和降低網絡流量，往往要在應用程序中調用Oracle數據庫服務器端的存儲過程。有的存儲過程需要返回多行的記錄集。這種情況下，在應用程序中調用返回記錄集的存儲過程存在一定的難度。本文介紹如何使用OLE DB和ADO調用返回記錄集的Oracle存儲過程。
2 OLE DB和ADO的關系
2.1 OLE DB簡介
OLE DB建立在ODBC成功基礎上的開放的規范,它為訪問和操縱不同類型數據提供開放的標准。OLE DB定義了一個COM接口集合，它封裝了各種數據庫管理系統服務。這些接口允許創建實現這些服務的軟件組件。OLE DB組件包括數據提供者（存儲和發布數據）、數據消費者（使用數據）和服務組件（處理和傳輸數據）。
OLE DB的設計是以消費者和提供者概念為中心。OLE DB消費者表示傳統的客戶方，提供者將數據以表格形式傳遞給消費者。OLE DB的Oracle提供者(OraOLEDB)可使OLE DB消費者高效地訪問Oracle數據源。圖1給出OLE DB的系統圖示。OLE DB數據提供者將數據從數據源傳遞給消費者。在標准接口的基礎上，OLE DB消費者能夠訪問來自提供者的數據。因為有COM組件，消費者可以用任何支持COM的編程語言訪問數據。
2.2 ADO對象模在ADO對象模型中，Connection、Command和Recordset對象是三個主要的對象。Connection對象表示對遠程數據的連接。Connection對象可與Command對象或Recordset對象關聯。Command對象定義了對數據源執行的指定命令。Command對象可以用來執行命令和參數化的SQL語句，可以用於SQL語句和返回記錄集的SQL查詢。Command對象既可以使用一個活動的Connection對象，也可以創建它自己到目標數據源的連接。Command對象包含一個Parameters集合，在該集合中每一個Parameter對象表示Command對象使用的參數。Command對象執行參數化的SQL語句時，每一個Parameter對象表示SQL語句中的一個參數。Recordset對象表示的來自基本表或命令執行結果的記錄全集。Recordset對象既可以使用一個活動的Connection對象，也可以創建它自己到目標數據源的連接。Recordset對象允許查詢和修改數據。每一個Recordset包含一個Field對象集合，其中，每個FIEld對象表示Recordset中一個數據列。
2.3 ADO和OLE DB的關系
OLE DB是系統級編程接口，ADO是應用軟件級編程接口。ADO是OLE DB的一個消費者，它提供了對OLE DB數據源應用級的訪問功能。ADO為OLE DB提供了一個易於使用的應用級接口。ADO允許用戶編寫通過OLE DB提供者對數據庫服務器中的數據進行訪問和操作的應用程序。其主要的優點是易於使用、高速度、低內存和占用較少的磁盤空間。ADO和OLE DB的關系如圖2所示：
3 Oracle中返回記錄集的存儲過程的定義
在Oracle存儲過程中，通過定義類型為REF CURSOR（游標指針）的傳出參數，可以返回記錄集。OLE DB允許消費者執行帶REF CURSOR類型參數的PL/SQL存儲過程，或者執行返回REF CURSOR的存儲函數。返回記錄集的存儲過程或函數必須定義在程序包裡。包是Oracle數據庫的一種對象，它可以將數據類型、存儲過程、函數、變量和常量封裝在一起。包在結構上分為包頭和包體兩部分，這兩部分要分別建立。包頭用來定義可被外部引用的元素；包體定義實際代碼。使用程序包定義返回記錄集的存儲過程時，需要在包頭中預定義自己的REF CURSOR類型。在程序包體中，存儲過程或函數須使用預定義的游標類型。另外，在一個存儲過程或函數中，可以定義多個REF CURSOR類型的參數以返回多個記錄集。
以下以職工表emp為例，介紹如何定義返回記錄集的Oracle存儲過程，emp表的定義如下：
CREATE TABLE emp(
empno NUMBER(4) NOT NULL, --職工編號
ename VARCHAR2(10), --職工姓名
hiredate DATE, --雇傭日期
sal NUMBER(7,2), --工資
deptno NUMBER(2))； --所屬部門編號
下面給出返回記錄集的Oracle存儲過程和存儲函數的示例程序包頭和包體，其中存儲過程GetEmpRecords含有兩個REF CURSOR類型的傳出參數，返回兩個記錄集，存儲函數GetDept的返回值類型為REF CURSOR，返回一個記錄集。
--定義一個名為Employees的程序包頭
CREATE OR REPLACE PACKAGE Employees AS
TYPE empcur IS REF CURSOR;
--empcur為自定義REF CURSOR類型
--聲明一個存儲過程GetEmpRecords
PROCEDURE GetEmpRecords(p_cursor OUT empcur,
q_cursor OUT empcur,
indeptno IN NUMBER,
p_errorcode OUT NUMBER);
--聲明一個存儲函數GetDept
FUNCTION GetDept(inempno IN NUMBER,
p_errorcode OUT NUMBER)
RETURN empcur;
END Employees; ---程序包頭定義結束
--定義Employees的程序包體
--定義存儲過程GetEmpRecords
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; -- p_errorcode為錯誤編號
--打開游標變量p_cursor，查詢所屬部門編號
--為indeptno的所有職工的信息
OPEN p_cursor FOR
SELECT * FROM emp
WHERE deptno = indeptno ORDER BY empno;
--打開游標變量q_cursor，查詢所屬部門編號
--為indeptno的所有職工的編號信息
OPEN q_cursor FOR
SELECT empno FROM emp
WHERE deptno = indeptno ORDER BY empno;
EXCEPTION --例外處理
WHEN OTHERS THEN
p_errorcode:= SQLCODE;
END GetEmpRecords; --存儲過程GetEmpRecords定義結束
--定義存儲函數GetDept
FUNCTION GetDept(inempno IN NUMBER,
p_errorcode OUT NUMBER)
RETURN empcur IS p_cursor empcur; --返回值
BEGIN
p_errorcode := 0;
--查詢職工編號為inempno的職工的所屬部門編號
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; --程序包體定義結束
4 利用ADO調用返回記錄集的Oracle存儲過程
使用OLE DB和ADO調用返回記錄集的Oracle存儲過程，在定義ADO對象和設置參數時，需要特定的設置。
4．1 設定數據庫連接字符串
在ADO應用程序中，如果要訪問返回記錄集的Oracle存儲過程，需要使用OraOLE DB的數據庫連接字符串的指定格式。在連接字符串中，PLSQLRSet屬性表明OraOLE DB是否需要從PL/SQL存儲過程返回記錄集。如果需要返回記錄集，PLSQLRSet設為1，否則設置為0，默認為 0。在Command命令執行後，該屬性需要設置為0。
4．2 參數的設置
在ADO應用程序中，可以定義調用存儲過程的參數。可使用Command對象的CreateParameter 方法創建Parameter 對象，並使用 Append 方法將它們添加到 Parameters 集合，在調用存儲過程時，Parameters 集合將參數傳遞給存儲過程。由於OraOLE DB采用定位傳遞參數的格式，即Command對象在調用存儲過程時，調用參數的位置應和存儲過程的參數的位置相對應。根據調用的存儲過程的參數屬性、名稱和位置，在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類型以優化查詢性能，即CommandType 屬性值設為AdCmdText。
使用 Command對象的CommandText 屬性定義調用存儲過程的命令。當使用Command對象執行Oracle返回記錄集存儲過程時，須以odbc轉義符調用格式來調用存儲過程,例如：{call credit_account(？,？)}。在這種格式中，傳入和傳出參數都用問號“？”來表示。
4．4返回記錄集
可使用Command對象的 Execute 方法調用Oracle返回記錄集的存儲過程，並在適當的時候將結果記錄集返回給 Recordset對象。該對象可以存儲一個或多個結果記錄集。當存儲多個記錄集時，可以用Recordset對象的NextRecordSet屬性獲得下一個記錄集。
下面給出在ASP中調用返回存儲過程或函數的示例程序：
<%@ Language=VBScript %>
<!--#include file="adovbs.inc"-->
<%
'Connection 對象代表了打開的、與數據源的連接。
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" '設置OLE DB提供者
'設置數據庫連接字符串，test是數據庫網絡服務名
Con.ConnectionString = "Data Source=TEST;" & _
"User ID=scott;PassWord=tiger;PLSQLRSet=1"
'調用返回記錄集的存儲過程，PLSQLRSet指定為1
Con.Open
'創建Command對象到目標數據源的連接
Cmd.ActiveConnection = Con
cmd.CommandType=adcmdtext
'雖然Employees.GetEmpRecords()有四個參數，
'但只有需要設定兩個參數
'因為REF CURSOR參數由OLE DB連接提供者自動設定。
Set Prm1 = Cmd.CreateParameter("Prm1", adSmallInt, adParamInput, , 30) '創建一個輸入參數，對應於indeptno
Cmd.Parameters.Append Prm1 '將參數添加到參數集合中
Set Prm2 = Cmd.CreateParameter("Prm2", adSmallInt, adParamOutput) '創建一個輸出參數，對應於p_errorcode
Cmd.Parameters.Append Prm2
'調用返回記錄集的存儲過程必須使用odbc的轉義符調用格式。
Cmd.CommandText = "{CALL Employees.GetEmpRecords(?, ?)}"
'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>
<%'獲得GetEmpRecords存儲過程返回的第二個記錄集
Set Rst2 = Rst1.NextRecordset
'在此可加入顯示第二個記錄集的代碼
Prm1.Value = 7369 '該參數對應於存儲函數的inempno
Prm2.Value = 0 '該參數對應於存儲函數的p_errorcode
'調用返回記錄集的存儲函數
Cmd.CommandText = "{CALL Employees.GetDept(?, ?)}"
'獲得GetDept存儲函數返回的記錄集
Set Rst2 = Cmd.Execute
Rst1.Close
Rst2.Close
set rst1=nothing
set rst2=nothing
con.close
set con=nothing
%>
<P> </P>
</BODY>
</Html>
5 小結
在應用程序中，使用OLE DB和ADO訪問返回記錄集的存儲過程，OLE DB服務者可以高效地將數據庫端的多行記錄集返回給ADO應用程序。在ADO應用程序中，使用Command對象可以傳遞輸入或輸出參數給存儲過程，並調用存儲過程以獲得多行記錄集。
