WEB开发网
开发学院数据库Oracle 如何使用ADO访问Oracle数据库存储过程 阅读

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

 2007-05-13 12:30:37 来源:WEB开发网   
核心提示: CREATE OR REPLACE PACKAGE BODY packperson AS PROCEDURE allperson (ssn OUT tssn, fname OUT tfname, lname OUT tlname) IS CURSOR person_cur IS SELEC
  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存储过程。

上一页  1 2 

Tags:如何 使用 ADO

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接