如何高效批量更新10000条Excel数据至Oracle数据库？

针对10000行数据更新慢的问题，核心是减少与数据库的交互次数，以下两种方案可以实现类似Java中updateBatch的批量执行效果：

方案一：ADODB参数化批量执行 #

利用ADODB的预编译和批量提交机制，替代逐行执行的方式，减少数据库交互次数：

Sub ScnStepsSave_Batch()
On Error GoTo ErrorHandler

SetSheetProperties

Dim scnId1 As String
scnId1 = tbl.ListColumns("SCN_ID").DataBodyRange.Cells(1).Value

' 构建SQL更新语句
Dim strSQL As String
strSQL = "UPDATE " & dbTblName & _
         " SET DATA = ?, DESC = ?, TYPE = ? " & _
         "WHERE SCN_ID = ? AND FUNC_NAME = ? AND STEP_ID = ? AND ITERATION = ?"

' 创建Command对象并配置参数
Dim cmd As Object
Set cmd = CreateObject("ADODB.Command")
With cmd
    .ActiveConnection = conn
    .CommandType = adCmdText
    .CommandText = strSQL
    .Prepared = True ' 开启预编译，提升重复执行效率
    ' 添加参数（类型和长度需与数据库字段匹配）
    .Parameters.Append .CreateParameter("DATA_PARAM", adVarChar, adParamInput, 1000)
    .Parameters.Append .CreateParameter("DESC_PARAM", adVarChar, adParamInput, 1)
    .Parameters.Append .CreateParameter("TYPE_PARAM", adVarChar, adParamInput, 1)
    .Parameters.Append .CreateParameter("SCN_ID_PARAM", adVarChar, adParamInput, 255)
    .Parameters.Append .CreateParameter("FUNC_NAME_PARAM", adVarChar, adParamInput, 255)
    .Parameters.Append .CreateParameter("STEP_ID_PARAM", adInteger, adParamInput)
    .Parameters.Append .CreateParameter("ITERATION_PARAM", adVarChar, adParamInput, 255)
End With

checkConnection ("ScnStepsSave")
conn.BeginTrans

Dim dataArr As Variant
dataArr = tbl.DataBodyRange.Value
Dim batchSize As Long
batchSize = 1000 ' 每批次提交1000条，可根据数据库性能调整
Dim i As Long
Dim batchCount As Long

' 按批次处理数据
For i = 1 To UBound(dataArr, 1)
    With cmd
        .Parameters("DATA_PARAM").Value = CStr(dataArr(i, tbl.ListColumns("DATA").Index))
        .Parameters("DESC_PARAM").Value = CStr(dataArr(i, tbl.ListColumns("DESC").Index))
        .Parameters("TYPE_PARAM").Value = CStr(dataArr(i, tbl.ListColumns("TYPE").Index))
        .Parameters("SCN_ID_PARAM").Value = CStr(dataArr(i, tbl.ListColumns("SCN_ID").Index))
        .Parameters("FUNC_NAME_PARAM").Value = CStr(dataArr(i, tbl.ListColumns("FUNC_NAME").Index))
        .Parameters("STEP_ID_PARAM").Value = CLng(dataArr(i, tbl.ListColumns("STEP_ID").Index))
        .Parameters("ITERATION_PARAM").Value = CStr(dataArr(i, tbl.ListColumns("ITERATION").Index))
        
        ' 执行当前参数的更新（不返回结果集）
        .Execute Options:=adExecuteNoRecords
        batchCount = batchCount + 1
        
        ' 达到批次大小提交一次事务
        If batchCount >= batchSize Then
            conn.CommitTrans
            conn.BeginTrans
            batchCount = 0
        End If
    End With
Next i

' 提交剩余未处理的批次
If batchCount > 0 Then
    conn.CommitTrans
Else
    conn.RollbackTrans ' 回滚空事务
End If

UnprotectSheet ws
Call ScnStepsfetchScnId(scnId1)
ProtectSheet ws

MsgBox "数据保存成功。", vbInformation, "成功"

ExitSub:
    Set cmd = Nothing
    Exit Sub

ErrorHandler:
    MsgBox "发生错误：" & Err.Description, vbExclamation, "错误"
    conn.RollbackTrans
    Debug.Print Err.Description
    Resume ExitSub
End Sub

方案二：Oracle临时表+MERGE语句（推荐，速度更快） #

通过将Excel数据批量导入Oracle临时表，再用MERGE语句一次性更新目标表，仅需两次数据库交互：

Sub ScnStepsSave_Merge()
On Error GoTo ErrorHandler

SetSheetProperties

Dim scnId1 As String
scnId1 = tbl.ListColumns("SCN_ID").DataBodyRange.Cells(1).Value

checkConnection ("ScnStepsSave")
conn.BeginTrans

' 1. 创建会话级临时表（会话结束自动销毁）
Dim createTmpSql As String
createTmpSql = "CREATE GLOBAL TEMPORARY TABLE TMP_SCN_STEPS (" & _
               "DATA VARCHAR2(1000), " & _
               "DESC_COL VARCHAR2(1), " & _
               "TYPE_COL VARCHAR2(1), " & _
               "SCN_ID VARCHAR2(255), " & _
               "FUNC_NAME VARCHAR2(255), " & _
               "STEP_ID NUMBER, " & _
               "ITERATION VARCHAR2(255)) " & _
               "ON COMMIT PRESERVE ROWS"
conn.Execute createTmpSql, , adExecuteNoRecords

' 2. 将Excel数据批量写入临时表
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")
rs.Open "TMP_SCN_STEPS", conn, adOpenKeyset, adLockOptimistic

Dim dataArr As Variant
dataArr = tbl.DataBodyRange.Value
Dim i As Long
For i = 1 To UBound(dataArr, 1)
    rs.AddNew
    rs("DATA").Value = CStr(dataArr(i, tbl.ListColumns("DATA").Index))
    rs("DESC_COL").Value = CStr(dataArr(i, tbl.ListColumns("DESC").Index))
    rs("TYPE_COL").Value = CStr(dataArr(i, tbl.ListColumns("TYPE").Index))
    rs("SCN_ID").Value = CStr(dataArr(i, tbl.ListColumns("SCN_ID").Index))
    rs("FUNC_NAME").Value = CStr(dataArr(i, tbl.ListColumns("FUNC_NAME").Index))
    rs("STEP_ID").Value = CLng(dataArr(i, tbl.ListColumns("STEP_ID").Index))
    rs("ITERATION").Value = CStr(dataArr(i, tbl.ListColumns("ITERATION").Index))
Next i
rs.UpdateBatch ' 批量提交到临时表
rs.Close

' 3. 使用MERGE语句批量更新目标表
Dim mergeSql As String
mergeSql = "MERGE INTO " & dbTblName & " T " & _
           "USING TMP_SCN_STEPS S " & _
           "ON (T.SCN_ID = S.SCN_ID AND T.FUNC_NAME = S.FUNC_NAME AND " & _
           "    T.STEP_ID = S.STEP_ID AND T.ITERATION = S.ITERATION) " & _
           "WHEN MATCHED THEN UPDATE SET " & _
           "T.DATA = S.DATA, T.DESC = S.DESC_COL, T.TYPE = S.TYPE_COL"
conn.Execute mergeSql, , adExecuteNoRecords

' 提交事务
conn.CommitTrans

UnprotectSheet ws
Call ScnStepsfetchScnId(scnId1)
ProtectSheet ws

MsgBox "数据保存成功。", vbInformation, "成功"

ExitSub:
    If Not rs Is Nothing Then
        If rs.State = adStateOpen Then rs.Close
        Set rs = Nothing
    End If
    Exit Sub

ErrorHandler:
    MsgBox "发生错误：" & Err.Description, vbExclamation, "错误"
    conn.RollbackTrans
    Debug.Print Err.Description
    Resume ExitSub
End Sub

注意事项 #

• 方案二中，DESC是Oracle关键字，临时表中用DESC_COL替代以避免语法错误。
• 方案一的batchSize可根据数据库性能调整，推荐范围500-2000条。
• 确保Oracle用户拥有创建临时表的权限。

内容的提问来源于stack exchange，提问作者Hiruthere