vba command call procedure

simple sample

▲连接Oracle数据库

    Set cnn = CreateObject("ADODB.Connection")
     cnn.Open "Provider=msdaora;Data Source=dl580;User Id=emssxjk;Password=emssxjk;"


    dl580是tnsnames.ora中配置的服务名。

▲创建记录集recordset

    Set rst = CreateObject("ADODB.Recordset")
     sqls = "select * from tb_city"
     Set rst = cnn.Execute(sqls)

    这儿的SQL语句除了查询语句select以外，也可以用insert、update、delete等命令，也可以用truncate table这样的命令。这些SQL语句也可以用command对象完成。command对象还可以进行存储过程的调用并传递参数。


▲记录集结果的引用

Excel VBA 通过ADO取到数据后，有如下引用方式（假定记录集名字为rst）：

1、字段数量：rst.fields.count；

2、字段名称：rst.fields(0).name，0表示第一个字段，其它字段分别是1,2,...rst.fields.count-1；

3、字段值：rst(0)或者rst(0).Value或者rst.fields(0).value，0表示第一个字段，也可以用字段名代替，如：rst("city")；

4、整个记录集： Range("a2").CopyFromRecordset rst命令将记录集保存到A2单元格开始的工作表中；

5、记录移动：取数后记录定位在第一条，rst.movenext可以移动到下一条，结尾标志：rst.eof = true，Excel VBA只支持向前移动(rst.movenext)；

6、记录数：rst.RecordCount是反馈记录数的，但在VBA中却总是反馈-1，不过可以根据rst.eof判断有无查询结果，如果rst.eof= true就表示查询结果为空。

▲调用存储过程

1、连接数据库，如前；

2、定义存储过程

     Set cmd = CreateObject("ADODB.Command")

    Set cmd.ActiveConnection = cnn
     cmd.CommandText = "zfqf_bag2mail"   '存储过程名称，有两个参数
     cmd.CommandType = adCmdStoredProc

3、参数赋值

     cmd.Parameters(1).Value = Cells(row1, pos_acc)
      cmd.Parameters(2).Value = Cells(row1, pos_lab)
4、执行存储过程

    cmd.Execute

这个存储过程的结果保存在表中，通过查询语句得到结果，没有通过变了返回。



1，不带参数的，直接在连接或Recordset 中用“EXEC 过程名”调用即可。
Private Sub cmdPrepareNewMinSalary_Click()
     Dim conEmployees As ADODB.Connection
     Dim strProcedure As String
    
     Set conEmployees = Application.CurrentProject.Connection
     strProcedure = "CREATE PROCEDURE SetNewMinSalary " & _
                    "AS " & _
                    "Update Employees " & _
                    "SET HourlySalary = 12.50;"
    
     conEmployees.Execute strProcedure
     conEmployees.Close
End Sub
调用程序如下：
Private Sub cmdNewMinSalary_Click()
     Dim conEmployees As ADODB.Connection
     Dim strProcedure As String
    
     Set conEmployees = Application.CurrentProject.Connection
     strProcedure = "EXECUTE SetNewMinSalary;"
    
     conEmployees.Execute strProcedure
     conEmployees.Close
End Sub

2，带一个参数的，也可以直接调用，如下例子
Private Sub cmdCreateProcedure_Click()
     Dim conEmployees As ADODB.Connection
     Dim strProcedure As String
    
     Set conEmployees = Application.CurrentProject.Connection
     strProcedure = "CREATE PROCEDURE SetNewMinSalary " & _
                    "(NewMinSalary Currency) " & _
                    "AS " & _
                    "Update Employees " & _
                    "SET HourlySalary = NewMinSalary " & _
                    "WHERE HourlySalary < NewMinSalary;"
                   
     conEmployees.Execute strProcedure
    
     MsgBox "The new stored procedure has been created."
     conEmployees.Close
End Sub

调用时直接输入一个参数。
Private Sub cmdExecuteProcedure_Click()
     Dim conEmployees As ADODB.Connection
     Dim strProcedure As String
    
     Set conEmployees = Application.CurrentProject.Connection
     strProcedure = "EXECUTE SetNewMinSalary 14.50;"
    
     conEmployees.Execute strProcedure
    
     MsgBox "The minimum hourly salary has been set to 14.50/hr"
     conEmployees.Close
End Sub

3：最麻烦的是带有多参数的，如下面的例子带有两个参数。
直接从SQL server复制过程如下：
CREATE PROCEDURE [dbo].[Max_2ExcelDB]
(
         @Territory varchar(10)='All',
         @Country varchar(10)='CN'
)
AS
         set nocount on
         if @Territory='All'
                 BEGIN
                         select * from CustomerDB
                         set nocount off
                 END
         else
                 begin
                         select a.* from CustomerDB a left join DistributorDB b
                         on a.Distributor=b.Distributor
                         where b.Territory in (@Territory,'Open') and a.Country=@Country
                 end
         set nocount off
调用程序如下，这时候就要请出ADODB.Command对象了：
Sub CallmyProc()
     Dim conn As ADODB.Connection
     Dim MyProc As ADODB.Command
    
     Dim sSql As String
     Dim SrcArr

     Set conn = New ADODB.Connection
     Set MyProc = New ADODB.Command
     conn.ConnectionString = connStr(1)
     conn.Open
    
     If conn.State = adStateOpen Then
         With MyProc
             .ActiveConnection = conn
             .Prepared = True
             .CommandText = "Max_2ExcelDB"
             .CommandType = adCmdStoredProc
             .Parameters.Append .CreateParameter("@Territory", adChar, adParamInput, 10, "South")
             .Parameters.Append .CreateParameter("@Country", adChar, adParamInput, 10, "CN")
             ThisWorkbook.Sheets("sheet4").Cells(1, 1).CopyFromRecordset .Execute
         End With
     Else
         MsgBox " 无法打开数据库"
     End If

     conn.Close
     Set conn = Nothing
     Set MyProc = Nothing
End Sub

4，更麻烦的是带有多参数，还有返回参数的，也是用Adodb.command对象解决。下面代码来自网络http://www.officefans.net/cdb/viewthread.php?tid=91224：
Set 连接 = CreateObject("ADODB.Connection")
Set 过程 = CreateObject("ADODB.Command")
连接.Open "Driver={SQL Server};Server=192.168.1.2;Database=数据库名_A;uid=共用登录;pwd=123456"
起始日期="2007-12-1"
截止日期="2008-3-1"
With 过程
.ActiveConnection = 连接
.Prepared = True
.CommandText = "vba_分数统计"
.CommandType = 4
.Parameters.Append .CreateParameter("@起始日期", 200, 1, 20, 起始日期)  ‘append 与后面的点. 需要加空格，即将createparameter返回值传给append方法。
.Parameters.Append .CreateParameter("@截止日期", 200, 1, 20, 截止日期)
.Parameters.Append .CreateParameter("@姓名", 200, 1, 50, Worksheets(2).Cells(2, 1).Value)
.Parameters.Append .CreateParameter("@返回分数", 2, 2)
.Execute
Worksheets(1).Cells(1,1).Value = .Parameters("@返回分数")
.Close
Set 连接 = Nothing
end with

create PROCEDURE pro1 
       @id int=10,
       @count int output
AS
BEGIN
	select  @count=COUNT(*) from test1 where id>@id;
end

既能返回数据集又能输出参数。

With command
    .ActiveConnection = con
    .CommandType = adCmdStoredProc
    .CommandText = "pro1"
    .Parameters.Append .CreateParameter(Name:="@id", Value:=0, Type:=adInteger)
    .Parameters.Append .CreateParameter(Name:="@count", Type:=adInteger, Direction:=adParamOutput)
    Set rs = .Execute()
    MsgBox .Parameters("@count").Value
End With

-----------------------------