VBA | DB操作 – 第4回：Excel から MERGE を実行

Template_ver1.x.x.xlsm
├標準モジュール
|   ├modCmnGlbConst
|   ├modSql   
|
クラスモジュール
    ├Configurator
    ├DBManager

' ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
' brief : D.各MERGE文を連続実行
' note  : Oracle対応
' ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
Public Sub executeMergeSqlsOracle()
 
    Dim sheet As Worksheet          ' SQLを生成する情報が格納されているシート
    Dim tableName As String         ' 操作する対象のテーブル
 
    Set sheet = ThisWorkbook.Worksheets(MERGE_SHEET_NAME)
    tableName = sheet.Cells(TABLE_NAME_ROW, TABLE_NAME_COL)
 
    executeDmlSqlsOracle "MERGE", tableName, sheet, TYPE_DIFINED_ROW, DB_COL_NAME_DIFINED_ROW, DATA_START_ROW, DATA_START_COL
 
End Sub
 
' ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
' brief : 各INSERT, UPDATE, DELETE, MERGE文を連続実行
' note  : Oracle対応
' ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
Private Sub executeDmlSqlsOracle(dmlType As String, tableName As String, sheet As Worksheet _
    , typeDifinedRow As Long, dbColNameDifinedRow As Long, dataStartRow As Long, dataStartCol As Long)
 
    Dim servicename As String, username As String, password As String
 
    ' Config 設定の読み込み
    Set config = New Configurator
    config.setData ThisWorkbook.Worksheets(GLB_CONFIG_SHEET), GLB_CONFIG_KEY_COL, GLB_CONFIG_ITEM_COL, GLB_CONFIG_START_ROW
 
    servicename = config.getItem("SERVICE_NAME")
    username = config.getItem("USERNAME")
    password = config.getItem("PASSWORD")
 
    ' DB接続
    Set dbManagerOracle = New DBManager
    dbManagerOracle.openOracle servicename, username, password
 
    ' トランザクション開始
    dbManagerOracle.begintrans
 
    On Error GoTo err
 
    ' SQL実行
    dbManagerOracle.createAndExcuteOracleSqls tableName, sheet, typeDifinedRow, dbColNameDifinedRow, dataStartRow, dataStartCol
 
    ' コミット
    dbManagerOracle.committrans
 
    ' DB切断
    dbManagerOracle.closeConnection
 
    Select Case dmlType
 
        Case "INSERT"
            Application.StatusBar = Now & "SQL INSERT実行完了"
 
        Case "UPDATE"
            Application.StatusBar = Now & "SQL UPDATE実行完了"
 
        Case "DELETE"
            Application.StatusBar = Now & "SQL DELETE実行完了"
 
        Case "MERGE"
            Application.StatusBar = Now & "SQL MERGE実行完了"
 
    End Select
 
    Exit Sub
 
err:
    ' エラー発生時はロールバック
    Debug.Print "エラー番号:" & err.Number & Chr(13) & "エラー内容:" & err.Description
    MsgBox "エラー番号:" & err.Number & Chr(13) & "エラー内容:" & err.Description
 
    MsgBox "ロールバックを実行し、プログラムを終了します。"
    dbManagerOracle.rollbacktrans
 
    End
 
End Sub