VBGood网站全文搜索 Google

搜索VBGood全站网页(全文搜索)
首页 - 经验之谈 - 如何使用ADO访问Oracle数据库存储过程
发表评论(0)作者:陈林生, 平台:VB6.0+Win98, 阅读:12443, 日期:2001-02-26
如何使用ADO访问Oracle数据库存储过程
北京科技大学管理学院
陈林生
---- 一、关于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存储过程。