熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

如何使用ADO訪問Oracle數據庫存儲過程

2013-11-13 15:26:44  來源: Oracle 

  關於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存儲過程

From:http://tw.wingwit.com/Article/program/Oracle/201311/16721.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.