| 订阅 | 在线投稿
分享
 
 
 

如何使用ADO访问Oracle数据库存储过程

来源:互联网网民  宽屏版  评论
2008-05-19 08:58:34

---- 一、关于ADO

---- 在基于Client/Server结构的数据库环境中,通过OLE DB接口可以存取数据,但它定义的是低层COM接口,不仅不易使用,而且不能被VB,VBA,VBScript等高级编程工具访问。而使用ADO则可以很容易地使VB等编程语言直接访问数据(通过OLE DB接口)。ADO是基于面向对象方法的,其对象模型如下图所示(略)

---- 由上图可见,ADO对象模型总共才包括六个对象,相对于数据访问对象(DAO)来说简单得多。因此实际中常常使用它来访问数据库。

---- 二、ADO访问数据库实例

---- 下面,我们以Oracle为例,使用VB6.0来访问其数据库中的存储过程。在此例中,我们首先在Oracle数据库上创建有两个存储过程,一个不带参数,另一个带有参数。然后,使用ADO来访问这两个存储过程。步骤如下:

---- 1. 在Oracle服务器上运行以下DDL脚本:

DROP TABLE person;

CREATE TABLE person

(ssn NUMBER(9) PRIMARY KEY,

fname VARCHAR2(15),

lname VARCHAR2(20));

INSERT INTO person VALUES(555662222,'Sam','Goodwin');

INSERT INTO person VALUES(555882222,'Kent','Clark');

INSERT INTO person VALUES(666223333,'Jane','Doe');

COMMIT;

/

---- 2. 在Oracle服务器上创建包(package):

CREATE OR REPLACE PACKAGE packperson

AS

TYPE tssn is TABLE of NUMBER(10)

INDEX BY BINARY_INTEGER;

TYPE tfname is TABLE of VARCHAR2(15)

INDEX BY BINARY_INTEGER;

TYPE tlname is TABLE of VARCHAR2(20)

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;

/

---- 3. 在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 1;

BEGIN

FOR singleperson IN person_cur

LOOP

ssn(percount) := singleperson.ssn;

fname(percount) := singleperson.fname;

lname(percount) := singleperson.lname;

percount := percount + 1;

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 1;

BEGIN

FOR singleperson IN person_cur

LOOP

ssn(percount) := singleperson.ssn;

fname(percount) := singleperson.fname;

lname(percount) := singleperson.lname;

percount := percount + 1;

END LOOP;

END;

END;

/

---- 4. 在 VB 6.0 中打开一个新的工程,缺省创建表单 Form1。

---- 5. 在表单上添加二个按钮,cmdGetEveryone和cmdGetOne。

---- 6. 在代码窗口中添加以下代码:

Option Explicit

Dim Cn As ADODB.Connection

Dim CPw1 As ADODB.Command

Dim CPw2 As ADODB.Command

Dim Rs As ADODB.Recordset

Dim Conn As String

Dim QSQL As String

Dim inputssn As Long

Private Sub cmdGetEveryone_Click()

Set Rs.Source = CPw1

Rs.Open

While Not Rs.EOF

MsgBox "Person data: " & Rs(0) & ",

" & Rs(1) & ", " & Rs(2)

Rs.MoveNext

Wend

Rs.Close

End Sub

Private Sub cmdGetOne_Click()

Set Rs.Source = CPw2

inputssn = InputBox(

"Enter the SSN you wish to retrieve:")

CPw2(0) = inputssn

Rs.Open

MsgBox "Person data: " & Rs(0) & "

, " & Rs(1) & ", " & Rs(2)

Rs.Close

End Sub

Private Sub Form_Load()

'使用合适的值代替以下用户ID,

口令(PWD)和服务器名称(SERVER)

Conn = "UID=*****;PWD=*****;driver=" _

& "{Microsoft ODBC for

Oracle};SERVER=dseOracle;"

Set Cn = New ADODB.Connection

'创建Connection对象

With Cn

.ConnectionString = Conn

.CursorLocation = adUseClient

.Open

End With

QSQL = "{call packperson.allperson(

{resultset 9,ssn,fname,"_

& "lname})}"

Set CPw1 = New ADODB.Command

'创建Command对象

With CPw1

Set .ActiveConnection = Cn

.CommandText = QSQL

.CommandType = adCmdText

End With

QSQL ="{call packperson.oneperson(?,

{resultset 2,ssn, "_

& " fname,lname})}"

'调用存储过程

Set CPw2 = New ADODB.Command

With CPw2

Set .ActiveConnection = Cn

.CommandText = QSQL

.CommandType = adCmdText

.Parameters.Append.CreateParameter(

,adInteger, _

adParamInput)

'添加存储过程参数

End With

Set Rs = New ADODB.Recordset

With Rs

.CursorType = adOpenStatic

.LockType = adLockReadOnly

End With

End Sub

Private Sub Form_Unload(Cancel As Integer)

Cn.Close

Set Cn = Nothing

Set CPw1 = Nothing

Set CPw2 = Nothing

Set Rs = Nothing

End Sub

---- 7. 运行程序。当点下cmdGetEveryone按钮时,程序调用Oracle数据库中不带参数的存储过程packperson.allperson,点下cmdGetOne按钮时调用packperson.oneperson存储过程。

 
特别声明:以上内容(如有图片或视频亦包括在内)为网络用户发布,本站仅提供信息存储服务。
 
  ---- 一、关于ADO   ---- 在基于Client/Server结构的数据库环境中,通过OLE DB接口可以存取数据,但它定义的是低层COM接口,不仅不易使用,而且不能被VB,VBA,VBScript等高级编程工具访问。而使用ADO则可以很容易地使VB等编程语言直接访问数据(通过OLE DB接口)。ADO是基于面向对象方法的,其对象模型如下图所示(略)   ---- 由上图可见,ADO对象模型总共才包括六个对象,相对于数据访问对象(DAO)来说简单得多。因此实际中常常使用它来访问数据库。   ---- 二、ADO访问数据库实例   ---- 下面,我们以Oracle为例,使用VB6.0来访问其数据库中的存储过程。在此例中,我们首先在Oracle数据库上创建有两个存储过程,一个不带参数,另一个带有参数。然后,使用ADO来访问这两个存储过程。步骤如下:   ---- 1. 在Oracle服务器上运行以下DDL脚本:   DROP TABLE person;   CREATE TABLE person   (ssn NUMBER(9) PRIMARY KEY,   fname VARCHAR2(15),   lname VARCHAR2(20));   INSERT INTO person VALUES(555662222,'Sam','Goodwin');   INSERT INTO person VALUES(555882222,'Kent','Clark');   INSERT INTO person VALUES(666223333,'Jane','Doe');   COMMIT;   /   ---- 2. 在Oracle服务器上创建包(package):   CREATE OR REPLACE PACKAGE packperson   AS   TYPE tssn is TABLE of NUMBER(10)   INDEX BY BINARY_INTEGER;   TYPE tfname is TABLE of VARCHAR2(15)   INDEX BY BINARY_INTEGER;   TYPE tlname is TABLE of VARCHAR2(20)   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;   /   ---- 3. 在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 1;   BEGIN   FOR singleperson IN person_cur   LOOP   ssn(percount) := singleperson.ssn;   fname(percount) := singleperson.fname;   lname(percount) := singleperson.lname;   percount := percount + 1;   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 1;   BEGIN   FOR singleperson IN person_cur   LOOP   ssn(percount) := singleperson.ssn;   fname(percount) := singleperson.fname;   lname(percount) := singleperson.lname;   percount := percount + 1;   END LOOP;   END;   END;   /   ---- 4. 在 VB 6.0 中打开一个新的工程,缺省创建表单 Form1。   ---- 5. 在表单上添加二个按钮,cmdGetEveryone和cmdGetOne。   ---- 6. 在代码窗口中添加以下代码:   Option Explicit   Dim Cn As ADODB.Connection   Dim CPw1 As ADODB.Command   Dim CPw2 As ADODB.Command   Dim Rs As ADODB.Recordset   Dim Conn As String   Dim QSQL As String   Dim inputssn As Long   Private Sub cmdGetEveryone_Click()   Set Rs.Source = CPw1   Rs.Open   While Not Rs.EOF   MsgBox "Person data: " & Rs(0) & ",   " & Rs(1) & ", " & Rs(2)   Rs.MoveNext   Wend   Rs.Close   End Sub   Private Sub cmdGetOne_Click()   Set Rs.Source = CPw2   inputssn = InputBox(   "Enter the SSN you wish to retrieve:")   CPw2(0) = inputssn   Rs.Open   MsgBox "Person data: " & Rs(0) & "   , " & Rs(1) & ", " & Rs(2)   Rs.Close   End Sub   Private Sub Form_Load()   '使用合适的值代替以下用户ID,   口令(PWD)和服务器名称(SERVER)   Conn = "UID=*****;PWD=*****;driver=" _   & "{Microsoft ODBC for   Oracle};SERVER=dseOracle;"   Set Cn = New ADODB.Connection   '创建Connection对象   With Cn   .ConnectionString = Conn   .CursorLocation = adUseClient   .Open   End With   QSQL = "{call packperson.allperson(   {resultset 9,ssn,fname,"_   & "lname})}"   Set CPw1 = New ADODB.Command   '创建Command对象   With CPw1   Set .ActiveConnection = Cn   .CommandText = QSQL   .CommandType = adCmdText   End With   QSQL ="{call packperson.oneperson(?,   {resultset 2,ssn, "_   & " fname,lname})}"   '调用存储过程   Set CPw2 = New ADODB.Command   With CPw2   Set .ActiveConnection = Cn   .CommandText = QSQL   .CommandType = adCmdText   .Parameters.Append.CreateParameter(   ,adInteger, _   adParamInput)   '添加存储过程参数   End With   Set Rs = New ADODB.Recordset   With Rs   .CursorType = adOpenStatic   .LockType = adLockReadOnly   End With   End Sub   Private Sub Form_Unload(Cancel As Integer)   Cn.Close   Set Cn = Nothing   Set CPw1 = Nothing   Set CPw2 = Nothing   Set Rs = Nothing   End Sub   ---- 7. 运行程序。当点下cmdGetEveryone按钮时,程序调用Oracle数据库中不带参数的存储过程packperson.allperson,点下cmdGetOne按钮时调用packperson.oneperson存储过程。   
󰈣󰈤
 
 
 
>>返回首页<<
 
 热帖排行
 
 
 
静静地坐在废墟上,四周的荒凉一望无际,忽然觉得,凄凉也很美
©2005- 王朝网络 版权所有