vba+调用oracle+存储过程

Public Function callPr_restore(ByVal conn As Connection, pr_restore_name As String)
Dim CNN_cmd As https://www.360docs.net/doc/346138127.html,mand
Set CNN_cmd = New https://www.360docs.net/doc/346138127.html,mand
Set CNN_cmd.ActiveConnection = conn

CNN_https://www.360docs.net/doc/346138127.html,mandText = pr_restore_name
CNN_https://www.360docs.net/doc/346138127.html,mandType = adCmdStoredProc

'------------------------有参存储过程加以下代码----------------------------------

With CNN_cmd

' 两种方式给参数赋值

' 第一种：CNN_cmd.P阿rameters（参数索引）.value = 参数值

' 第二种：.CNN_cmd.Parameters.Append .CreateParameter(参数名，adVarChar, adParamInput, 10,

参数值）

.Parameters(1).Value = "20030611"
.Parameters.Append .CreateParameter("stunum", adVarChar, adParamInput, 10, "20030610")
.Parameters.Append .CreateParameter("sPrefix", adVarChar, adParamInput, 4, "2004")
.Parameters.Append .CreateParameter("iLength", adInteger, adParamInput, , 5)
.Parameters.Append .CreateParameter("sSequenceNumber", adVarChar, adParamOutput, 7,

sSequenceNumber)
End With

'---------------------------------------END--------------------------------------------
CNN_cmd.Execute
End Function

其中，参数conn：数据库连接、pr_restore_name：存储过程



工具-->引用-->Microsoft ActiveX Data Objects 2.8勾上,确定(此处2.8为最高的一项)

示例代码如下:

Sub test()

Dim i As Integer
Dim connStr As String
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

i = 0
connStr = "Provider=MSDAORA.1;Data Source=dbname"
Set conn = New ADODB.Connection
conn.Open connStr, "username", "password"
Set rs = New ADODB.Recordset
rs.Open "select * from tablename", conn

If Not rs.EOF Then
MsgBox rs.Fields.Count '输出列数
End If

rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

End Sub