一关于ADO
在基于Client/Server结构的数据库环境中通过OLE DB接口可以存取数据但它定义的是低层COM接口不仅不易使用而且不能被VBVBAVBScript等高级编程工具访问而使用ADO则可以很容易地使VB等编程语言直接访问数据（通过OLE DB接口）ADO是基于面向对象方法的其对象模型如下图所示(略)
由上图可见ADO对象模型总共才包括六个对象相对于数据访问对象（DAO）来说简单得多因此实际中常常使用它来访问数据库
二ADO访问数据库实例
下面我们以Oracle为例使用VB来访问其数据库中的存储过程在此例中我们首先在Oracle数据库上创建有两个存储过程一个不带参数另一个带有参数然后使用ADO来访问这两个存储过程步骤如下
在Oracle服务器上运行以下DDL脚本:
DROP TABLE person;
CREATE TABLE person
(ssn NUMBER() PRIMARY KEY
fname VARCHAR()
lname VARCHAR());
INSERT INTO person VALUES(SamGoodwin);
INSERT INTO person VALUES(KentClark);
INSERT INTO person VALUES(JaneDoe);
COMMIT;
/
在Oracle服务器上创建包（package）:
CREATE OR REPLACE PACKAGE packperson
AS
TYPE tssn is TABLE of NUMBER()
INDEX BY BINARY_INTEGER;
TYPE tfname is TABLE of VARCHAR()
INDEX BY BINARY_INTEGER;
TYPE tlname is TABLE of VARCHAR()
INDEX BY BINARY_INTEGER;
PROCEDURE allperson
(ssn OUT tssn
fname OUT tfname
lname OUT tlname);
PROCEDURE oneperson
(onessn IN NUMBER
ssn OUT tssn
fname OUT tfname
lname OUT tlname);
END packperson;
/
在Oracle服务器上创建以下包体（package body）
CREATE OR REPLACE PACKAGE BODY packperson
AS
PROCEDURE allperson
(ssn OUT tssn
fname OUT tfname
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn fname lname
FROM person;
percount NUMBER DEFAULT ;
BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singlepersonssn;
fname(percount) := singlepersonfname;
lname(percount) := singlepersonlname;
percount := percount + ;
END LOOP;
END;
PROCEDURE oneperson
(onessn IN NUMBER
ssn OUT tssn
fname OUT tfname
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn fname lname
FROM person
WHERE ssn = onessn;
percount NUMBER DEFAULT ;
BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singlepersonssn;
fname(percount) := singlepersonfname;
lname(percount) := singlepersonlname;
percount := percount + ;
END LOOP;
END;
END;
/
在 VB 中打开一个新的工程缺省创建表单 Form
在表单上添加二个按钮cmdGetEveryone和cmdGetOne
在代码窗口中添加以下代码:
Option Explicit
Dim Cn As ADODBConnection
Dim CPw As ADODBCommand
Dim CPw As ADODBCommand
Dim Rs As ADODBRecordset
Dim Conn As String
Dim QSQL As String
Dim inputssn As Long
Private Sub cmdGetEveryone_Click()
Set RsSource = CPw
RsOpen
While Not RsEOF
MsgBox Person data: & Rs() &
& Rs() & & Rs()
RsMoveNext
Wend
RsClose
End Sub
Private Sub cmdGetOne_Click()
Set RsSource = CPw
inputssn = InputBox(
Enter the SSN you wish to retrieve:)
CPw() = inputssn
RsOpen
MsgBox Person data: & Rs() &
& Rs() & & Rs()
RsClose
End Sub
Private Sub Form_Load()
使用合适的值代替以下用户ID
口令（PWD）和服务器名称（SERVER）
Conn = UID=*****;PWD=*****;driver= _
& {Microsoft ODBC for
Oracle};SERVER=dseOracle;
Set Cn = New ADODBConnection
创建Connection对象
With Cn
ConnectionString = Conn
CursorLocation = adUseClient
Open
End With
QSQL = {call packpersonallperson(
{resultset ssnfname_
& lname})}
Set CPw = New ADODBCommand
创建Command对象
With CPw
Set ActiveConnection = Cn
CommandText = QSQL
CommandType = adCmdText
End With
QSQL ={call packpersononeperson(?
{resultset ssn _
& fnamelname})}
调用存储过程
Set CPw = New ADODBCommand
With CPw
Set ActiveConnection = Cn
CommandText = QSQL
CommandType = adCmdText
ParametersAppendCreateParameter(
adInteger _
adParamInput)
添加存储过程参数
End With
Set Rs = New ADODBRecordset
With Rs
CursorType = adOpenStatic
LockType = adLockReadOnly
End With
End Sub
Private Sub Form_Unload(Cancel As Integer)
CnClose
Set Cn = Nothing
Set CPw = Nothing
Set CPw = Nothing
Set Rs = Nothing
End Sub
运行程序当点下cmdGetEveryone按钮时程序调用Oracle数据库中不带参数的存储过程packpersonallperson点下cmdGetOne按钮时调用packpersononeperson存储过程
