最近对 pandas, Power Query 和 SQL 的数据处理和加工进行了一些类似的处理,希望通过比较看到各种处理方式的优点。之前笔者用的比较多的是 VBA + ADO,VBA + ADO 的方式其实离不开 Sql 语句,这种方式也有非常方便:前台有 Excel,后台有数据库就能实现。
由于 VBA 对多行字符串的支持不好,代码中表达 sql 语句极不方便,所利用数据库的存储过程,将sql 语句放在存储过程中。这样,代码中只关心存储过程的名称和参数,代码大大简化,并且利用了数据库的计算能力,性能得到提高。本文介绍在 VBA 中利用 ADO 如何操作存储过程的要点。
调用 SQL Server 存储过程
连接字符串
ADO 连接到数据库,需要连接字符串。在 Windows 上连接到数据库,可以利用数据库连接向导,帮助构建连接字符串,不用死记。方法如下:在任意位置新建一个扩展名为 udl 的文件,然后双击 udl 文件,启动数据库连接向导。假设需要连接到 SQL Server 数据库,首先选择数据库驱动:
点击下一步按钮,输入数据库服务器名称和登录的方式:
点击测试连接按钮,测试是否连接成功。没有问题,点击确定按钮完成。然后用可以打开文本文件的编辑器打开 udl 文件,文件保存的就是我们需要的连接字符串。
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;
Persist Security Info=False;
Initial Catalog=stonetest;
Data Source=STONEWM-PC\SQLEXPRESS
在 sql server 中创建存储过程
本例设计的场景是在存储过程中计算物料在某个月份的进出存数据。sql 语句可以用子查询或者 Common Table Expression。完成后的存储过程代码如下:
CREATE PROCEDURE [dbo].[spGetStockBalances]
@y int,
@m int
AS
BEGIN
WITH merged AS (
SELECT
d.DocNo,
d.MaterialNo,
d.Qty,
h.MovementType,
h.DocDate,
t.InOutSign,
YEAR(h.DocDate) AS TxYear,
MONTH(h.DocDate) AS TxMonth,
CASE InOutSign WHEN '+' THEN ISNULL(d.Qty, 0) else -1 * ISNULL(d.Qty, 0) END AS ActualQty
FROM
stock_movement_details d
INNER JOIN stock_movement_headers h ON d.DocNo = h.DocNo
LEFT OUTER JOIN movement_types t ON h.MovementType = t.MovementTypeID
),
calculated AS (
SELECT
MaterialNo,
InOutSign,
TxYear,
TxMonth,
ActualQty,
CASE WHEN TxYear < @y
OR (
TxYear = @y
AND TxMonth < @m
) THEN ActualQty ELSE 0 END AS BeginQty,
CASE WHEN TxYear = @y
AND TxMonth = @m
AND InOutSign = '+' THEN ActualQty ELSE 0 END AS StockIn,
CASE WHEN TxYear = @y
AND TxMonth = @m
AND InOutSign = '-' THEN ActualQty ELSE 0 END AS StockOut
FROM
merged
)
SELECT
MaterialNo,
SUM(BeginQty) AS BeginQty,
SUM(StockIn) AS StockIn,
SUM(StockOut) AS StockOut,
SUM(BeginQty+StockIn+StockOut) AS EndQty
FROM
calculated
GROUP BY
MaterialNo;
END;
存储过程中有两个变量 @y
表示年度,@m
表示月份。
VBA 调用 SQL Server 存储过程
在 VBE 环境中,添加对 Microsoft ActiveX Data Objects 对象的引用,然后编写如下代码:
Option Explicit
Public Sub GetStockBalance()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
conn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=stonetest;Data Source=STONEWM-PC\SQLEXPRESS"
conn.Open
With cmd
.ActiveConnection = conn
.CommandType = adCmdStoredProc
.CommandText = "dbo.spGetStockBalances"
.Parameters.Append .CreateParameter("@y", adInteger, adParamInput, , 2008)
.Parameters.Append .CreateParameter("@m", adInteger, adParamInput, , 4)
End With
Dim rs As New ADODB.Recordset
Set rs = cmd.Execute
' headers
Dim col As Integer
Dim startCell As Range
col = 0
Set startCell = Sheet1.Range("A1")
For col = 0 To rs.Fields.Count - 1
startCell.Offset(0, col).Value = rs.Fields(col).Name
Next
' lines
startCell.Offset(1, 0).CopyFromRecordset rs
rs.Close
conn.Close
Set rs = Nothing
Set cmd = Nothing
Set conn = Nothing
End Sub
调用 MySQL 存储过程
配置 ODBC 数据源
ADO 连接 MySQL 数据库,使用 ODBC 数据源。在 Windows 的 Run 窗口中输入 odbcad32 命令,打开配置连接界面。确保已经下载和安装 ODBC for MySQL 驱动程序:
然后在界面中配置数据源:
编写存储过程
MySQL 的存储过程与 T-SQL 语法有一些区别,包括不支持 CTE,所以需要用子查询来解决嵌套调用。
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `spStockBalances`(
y int,
m int
)
BEGIN
SELECT
MaterialNo,
SUM(BeginQty) AS BeginQty,
SUM(StockIn) AS StockIn,
SUM(StockOut) AS StockOut,
SUM(BeginQty+StockIn+StockOut) AS EndQty
FROM
(
SELECT
MaterialNo,
TxYear,
TxMonth,
InOutSign,
ActualQty,
CASE WHEN (TxYear < y)
OR (
TxYear = y
AND TxMonth < m
) THEN ActualQty ELSE 0 END AS BeginQty,
CASE WHEN TxYear = y
AND TxMonth = m
AND InOutSign = '+' THEN ActualQty ELSE 0 END AS StockIn,
CASE WHEN TxYear = y
AND TxMonth = m
AND InOutSign = '-' THEN ActualQty ELSE 0 END AS StockOut
FROM
(
SELECT
d.MaterialNo,
d.Qty,
h.MovementType,
h.DocDate,
t.InOutSign,
YEAR(h.DocDate) AS TxYear,
MONTH(h.DocDate) AS TxMonth,
CASE WHEN t.InOutSign = '+' THEN ifnull(d.Qty, 0) ELSE -1 * ifnull(d.Qty,0) END AS ActualQty
FROM
stock_movement_details AS d
INNER JOIN stock_movement_headers AS h ON d.DocNo = h.DocNo
LEFT JOIN movement_types AS t ON h.MovementType = t.MovementTypeID
) AS joined
) AS calculated
GROUP BY
MaterialNo
ORDER BY
MaterialNo;
END$$
DELIMITER ;
调用 MySQL 存储过程
根据测试,用 Command 命令创建参数并调用存储过程的结果不正确,所以直接用 Recordset Open 方法能返回正确的结果。
Option Explicit
Public Sub GetStockBalance()
Dim conn As New ADODB.Connection
Dim connStr As String
connStr = "DRIVER={MySQL ODBC 8.0 Unicode Driver}; " & _
"SERVER=localhost; PORT=3306; DATABASE=stonetest; USER=root; PASSWORD=pwd;OPTIONS=3"
conn.ConnectionString = connStr
conn.Open
Dim rs As New ADODB.Recordset
rs.Open "call spStockBalances(2008,4)", conn, adOpenStatic, adLockReadOnly
' headers
Dim col As Integer
Dim startCell As Range
col = 0
Set startCell = Sheet1.Range("A1")
For col = 0 To rs.Fields.Count - 1
startCell.Offset(0, col).Value = rs.Fields(col).Name
Next
' lines
startCell.Offset(1, 0).CopyFromRecordset rs
' clean up
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub