| 導購 | 订阅 | 在线投稿
分享
 
 
 

如何使用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存儲過程。   
󰈣󰈤
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
王朝網路微信公眾號
微信掃碼關註本站公眾號 wangchaonetcn
 
  免責聲明:本文僅代表作者個人觀點,與王朝網絡無關。王朝網絡登載此文出於傳遞更多信息之目的,並不意味著贊同其觀點或證實其描述,其原創性以及文中陳述文字和內容未經本站證實,對本文以及其中全部或者部分內容、文字的真實性、完整性、及時性本站不作任何保證或承諾,請讀者僅作參考,並請自行核實相關內容。
 
© 2005- 王朝網路 版權所有