VB6如何调用ORACLE的存储过程?

dxk01 2005-10-31 08:45:20
如题
...全文
198 4 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
daisy8675 2005-11-03
  • 打赏
  • 举报
回复
那我帖一下给你:

如何使用ADO访问Oracle数据库存储过程
http://www.xnovo.com/doc/html/aebbbg/bgbbchaaxxzgietotb.html

  ---- 一、关于ADO
  ---- 在基于Client/Server结构的数据库环境中,通过OLE DB接口可以存取数据,但它定
义的是低层COM接口,不仅不易使用,而且不能被VB,VBA,VBScript等高级编程工具访问。
而使用ADO则可以很容易地使VB等编程语言直接访问数据(通过OLE DB接口)。ADO是基于面
  ---- 由上图可见,ADO对象模型总共才包括六个对象,相对于数据访问对象(DAO)来说
  ---- 二、ADO访问数据库实例
  ---- 下面,我们以Oracle为例,使用VB6.0来访问其数据库中的存储过程。在此例中,我
们首先在Oracle数据库上创建有两个存储过程,一个不带参数,另一个带有参数。然后,使用
  ---- 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存储过程

wang5431320 2005-11-02
  • 打赏
  • 举报
回复
用rdo,下面是我的程序片断:

Dim Oracle_connectStr As String
Dim ProcStr As String

Dim Oracle_CN As rdoConnection
Dim Oracle_QY As rdoQuery

'连接到Oracle
With rdoEngine
.rdoDefaultCursorDriver = rdUseClientBatch
.rdoDefaultUser = "User_name"
.rdoDefaultPassword = "User_pass"
.rdoDefaultLoginTimeout = 1
End With
Oracle_connectStr = "SERVER=" & Oracle_db_name & ";" & _
"DRIVER={Microsoft ODBC for Oracle};"
Set Oracle_CN = rdoEngine.rdoEnvironments(0).OpenConnection _
("", rdDriverNoPrompt, False, Oracle_connectStr)
OracleSQL = "{call write_km2(?,?,?,?,?,?,?,?,?,?,?,?)}"
Set Oracle_QY = Oracle_CN.CreateQuery("", OracleSQL)

Oracle_QY(0) = Trim(Str(.Fields("考生证号")))
Oracle_QY(1) = .Fields("身份证号")
Oracle_QY(2) = .Fields("准驾车型")
Oracle_QY(3) = Ksrq
Oracle_QY(4) = Kscj
Oracle_QY(5) = Kscs
Oracle_QY(6) = .Fields("主考人")
Oracle_QY(7) = .Fields("监考人")
Oracle_QY(8) = .Fields("经办人")
Oracle_QY(9) = Zt
'Oracle_QY (10) = Res
'Oracle_QY (11) = errMessage

Oracle_QY.Execute
If Oracle_QY(10) <> 0 Then
MsgBox "操作失败,返回值Res=" & Str(Oracle_QY(10)) & vbCrLf & Oracle_QY(11)
Exit Sub
End If
AccessData.Recordset.MoveNext
Loop
dxk01 2005-11-02
  • 打赏
  • 举报
回复
楼上兄弟,那个地址上不去呀,自己顶。
daisy8675 2005-11-01
  • 打赏
  • 举报
回复
没有安装oracle,仅给予参考:http://www.xnovo.com/doc/html/aebbbgbgbbchaaxxzgietotb.html

1,217

社区成员

发帖
与我相关
我的任务
社区描述
VB 数据库(包含打印,安装,报表)
社区管理员
  • 数据库(包含打印,安装,报表)社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧