Excel中用宏批量整理SQL脚本，生成ORACLE触发器语句

Excel中用宏批量整理SQL脚本，生成ORACLE触发器语句

今天，因为一批表需要加响应的触发器，同步两个数据库之间的表，考虑到表比较多，而且一条记录的对照方式需要表的全字段，所以想着手工整理或者用文本方式整理比较麻烦，而且一旦有变动或者新的要求，也比较复杂，于是想着还是用Excel的vba解决。

拿到的原始脚本如

CREATE TABLE "JCFX"."TEST_COPYTABLE"
   ( "TXT_NAME" VARCHAR2(40),
   "TXT_NAME2" VARCHAR2(40)
   )

从这表结构中需要整理出表名称和各个字段名称及类型，然后生成insert,update,delete的触发器

( "TXT_NAME" VARCHAR2(40),    "TXT_NAME2" VARCHAR2(40)    )

VBS代码如下

Option Explicit
Sub makeSQL()
   Dim i As Integer
   Dim startRow As Integer
   Dim endRow As Integer
   Dim clsField As ClassField
   Dim clsFieldCollection As New Collection
   
   Dim fieldStr As String
   Dim tableName As String
   Dim destDb As String
   
   Dim insSQL As String
   Dim upSQL As String
   Dim delSQL As String
   
   startRow = 2
   endRow = 41
   destDb = "remotedbshbxp"
   
   Sheet4.Select
   For i = startRow To endRow
      Set clsFieldCollection = getFields(Range("B" + CStr(i)).Value)
      tableName = getTableName(Range("B" + CStr(i)).Value)
      fieldStr = printFields(clsFieldCollection)
      'Range("J" + CStr(i)).Value = fieldStr
      'Range("K" + CStr(i)).Value = tableName
      
      insSQL = getTrgInsert(clsFieldCollection, destDb, tableName)
      Range("D" + CStr(i)).Value = insSQL
      
      upSQL = getTrgUpdate(clsFieldCollection, destDb, tableName)
      Range("E" + CStr(i)).Value = upSQL
      
      delSQL = getTrgDelete(clsFieldCollection, destDb, tableName)
      Range("F" + CStr(i)).Value = delSQL
   Next i
End Sub
Function getTableName(ByVal createSQL As String) As String
    Dim i As Integer
    Dim restFieldsSql As String
    Dim startPos As Integer
    Dim arr1 As Variant
    Dim arr2 As Variant
    
    createSQL = Replace(createSQL, vbCr, "")
    createSQL = Replace(createSQL, vbLf, "")
    createSQL = Replace(createSQL, vbCrLf, "")
    createSQL = Trim(createSQL)
    
    startPos = InStr(1, createSQL, "(")
    
    restFieldsSql = Left(createSQL, startPos + 1)
    arr1 = Split(restFieldsSql, " ")
    For i = 0 To UBound(arr1)
        If InStr(1, arr1(i), ".") > 0 Then
            arr2 = Split(arr1(i), ".")
            getTableName = Trim(Replace(arr2(1), """", ""))
            GoTo endHandle
        End If
    Next i
endHandle:
End Function
Function getFields(ByVal createSQL As String) As Collection
    Dim i As Integer
    Dim clsField As ClassField
    Dim startPos As Integer
    Dim restFieldsSql As String
    Dim fieldArr As Variant
    Dim aCollection As New Collection
    Dim filedNameAndTypeArr As Variant
    
    createSQL = Replace(createSQL, vbCr, "")
    createSQL = Replace(createSQL, vbLf, "")
    createSQL = Replace(createSQL, vbCrLf, "")
    createSQL = Trim(createSQL)
    
    startPos = InStr(1, createSQL, "(")
    restFieldsSql = Mid(createSQL, startPos + 1, Len(createSQL))
    
    restFieldsSql = restFieldsSql + "222"
    restFieldsSql = Replace(restFieldsSql, ")222", "")
    
    fieldArr = Split(restFieldsSql, ",")
    For i = 0 To UBound(fieldArr)
       filedNameAndTypeArr = Split(Trim(fieldArr(i)), " ")
       If UBound(filedNameAndTypeArr) = 1 Then
          Set clsField = New ClassField
          clsField.fieldName = Trim(Replace(filedNameAndTypeArr(0), """", ""))
          clsField.fieldTypes = Trim(filedNameAndTypeArr(1))
          aCollection.Add clsField
       End If
    Next i
    Set getFields = aCollection
End Function
Function printFields(ByRef aCollection As Collection) As String
    Dim i As Integer
    Dim clsField As ClassField
    Dim fieldStr As String
    
    For i = 1 To aCollection.Count
      Set clsField = aCollection.Item(i)
      fieldStr = fieldStr + vbCrLf + clsField.fieldName
    Next i
    printFields = fieldStr
End Function

Function getTrgInsert(ByRef aCollection As Collection, ByVal destDb As String, ByVal tableName As String) As String
    Dim i As Integer
    Dim clsField As ClassField
    Dim trgSQL As String
    Dim trgWhere As String
    Dim fieldSQl As String
    Dim valuesSQL As String
    
    Dim sufExt As String
    sufExt = ":NEW"
    trgWhere = ""
    
    trgSQL = "CREATE OR REPLACE TRIGGER TRG_" & tableName & "_INS" & vbCrLf & _
             " AFTER Insert " & vbCrLf & _
             " ON " & tableName & vbCrLf & _
             " FOR EACH ROW " & vbCrLf & _
             " DECLARE v_count NUMBER;" & vbCrLf & _
             " BEGIN "
             
    For i = 1 To aCollection.Count
      Set clsField = aCollection.Item(i)
      If trgWhere = "" Then
         trgWhere = clsField.fieldName + "=:NEW." + clsField.fieldName
         fieldSQl = clsField.fieldName
         valuesSQL = sufExt + "." + clsField.fieldName
      Else
         trgWhere = trgWhere + " and " + clsField.fieldName + "=" + sufExt + "." + clsField.fieldName
         fieldSQl = fieldSQl + "," + clsField.fieldName
         valuesSQL = valuesSQL + "," + sufExt + "." + clsField.fieldName
      End If
    Next i
    trgSQL = trgSQL & vbCrLf & " SELECT COUNT(*) INTO v_count FROM " + tableName + "@" + destDb + " WHERE " + trgWhere + ";"
    trgSQL = trgSQL & vbCrLf & " IF v_count = 0 THEN "
    trgSQL = trgSQL & vbCrLf & " insert into " + tableName + "@" + destDb + "(" + fieldSQl + ") values (" + valuesSQL + ");"
    trgSQL = trgSQL & vbCrLf & " end if;"
    trgSQL = trgSQL & vbCrLf & " END; "
    getTrgInsert = trgSQL
End Function
Function getTrgUpdate(ByRef aCollection As Collection, ByVal destDb As String, ByVal tableName As String) As String
    Dim i As Integer
    Dim clsField As ClassField
    Dim trgSQL As String
    Dim trgWhere As String
    Dim fieldSQl As String
    Dim valuesSQL As String
    
    Dim sufExt As String
    Dim sufExtOld As String
    
    sufExt = ":NEW"
    sufExtOld = ":OLD"
    trgWhere = ""
    
    trgSQL = "CREATE OR REPLACE TRIGGER TRG_" & tableName & "_UPT" & vbCrLf & _
             " AFTER update " & vbCrLf & _
             " ON " & tableName & vbCrLf & _
             " FOR EACH ROW " & vbCrLf & _
             " DECLARE v_count NUMBER;" & vbCrLf & _
             " BEGIN "
             
    For i = 1 To aCollection.Count
      Set clsField = aCollection.Item(i)
      If trgWhere = "" Then
         trgWhere = clsField.fieldName + "=" + sufExtOld + "." + clsField.fieldName
         fieldSQl = clsField.fieldName + "=" + sufExt + "." + clsField.fieldName
      Else
         trgWhere = trgWhere + " and " + clsField.fieldName + "=" + sufExtOld + "." + clsField.fieldName
         fieldSQl = fieldSQl + "," + clsField.fieldName + "=" + sufExt + "." + clsField.fieldName
      End If
    Next i
    trgSQL = trgSQL & vbCrLf & " SELECT COUNT(*) INTO v_count FROM " + tableName + "@" + destDb + " WHERE " + trgWhere + ";"
    trgSQL = trgSQL & vbCrLf & " IF v_count > 0 THEN "
    trgSQL = trgSQL & vbCrLf & " update " + tableName + "@" + destDb + " set " + fieldSQl + " WHERE " + trgWhere + ";"
    trgSQL = trgSQL & vbCrLf & " end if;"
    trgSQL = trgSQL & vbCrLf & " END; "
    getTrgUpdate = trgSQL
End Function
Function getTrgDelete(ByRef aCollection As Collection, ByVal destDb As String, ByVal tableName As String) As String
    Dim i As Integer
    Dim clsField As ClassField
    Dim trgSQL As String
    Dim trgWhere As String
    Dim fieldSQl As String
    Dim valuesSQL As String
    
    Dim sufExt As String
    Dim sufExtOld As String
    
    sufExt = ":NEW"
    sufExtOld = ":OLD"
    trgWhere = ""
    
    trgSQL = "CREATE OR REPLACE TRIGGER TRG_" & tableName & "_DEL" & vbCrLf & _
             " AFTER delete " & vbCrLf & _
             " ON " & tableName & vbCrLf & _
             " FOR EACH ROW " & vbCrLf & _
             " DECLARE v_count NUMBER;" & vbCrLf & _
             " BEGIN "
             
    For i = 1 To aCollection.Count
      Set clsField = aCollection.Item(i)
      If trgWhere = "" Then
         trgWhere = clsField.fieldName + "=" + sufExtOld + "." + clsField.fieldName
         fieldSQl = clsField.fieldName + "=" + sufExt + "." + clsField.fieldName
      Else
         trgWhere = trgWhere + " and " + clsField.fieldName + "=" + sufExtOld + "." + clsField.fieldName
         fieldSQl = fieldSQl + "," + clsField.fieldName + "=" + sufExt + "." + clsField.fieldName
      End If
    Next i
    trgSQL = trgSQL & vbCrLf & " SELECT COUNT(*) INTO v_count FROM " + tableName + "@" + destDb + " WHERE " + trgWhere + ";"
    trgSQL = trgSQL & vbCrLf & " IF v_count > 0 THEN "
    trgSQL = trgSQL & vbCrLf & " delete from " + tableName + "@" + destDb + " WHERE " + trgWhere + ";"
    trgSQL = trgSQL & vbCrLf & " end if;"
    trgSQL = trgSQL & vbCrLf & " END; "
    getTrgDelete = trgSQL
End Function

ClassField类

Public fieldName As String
Public fieldTypes As String

后发现两个库的同一张表，表结构有差异，于是又增加了表结构的对比，但万恶的xlsx呀，发现保存不上VBA，于是丢了。