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

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

拿到的原始脚本如

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

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

ABCDEF表名旧库脚本(原始)新库脚本(原始)插入触发器更新触发器删除触发器 CREATE TABLE "JCFX"."TEST_COPYTABLE"
   ( "TXT_NAME" VARCHAR2(40),
   "TXT_NAME2" VARCHAR2(40)
   )    

VBS代码如下

Option ExplicitSub 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 iEnd SubFunction 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 iendHandle:End FunctionFunction 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 = aCollectionEnd FunctionFunction 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 = fieldStrEnd FunctionFunction 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 = trgSQLEnd FunctionFunction 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 = trgSQLEnd FunctionFunction 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 = trgSQLEnd Function

ClassField类

Public fieldName As StringPublic fieldTypes As String

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