Excel中用宏批量整理SQL脚本，生成ORACLE触发器语句 - 数据库编程

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

拿到的原始脚本如

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

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

表名

旧库脚本(原始)

新库脚本(原始)

插入触发器

更新触发器

删除触发器

CREATE TABLE "JCFX"."TEST_COPYTABLE"
( "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.Ite

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