根据库表表格生成DML语句

由 Qiongpan Ke 于 2024-07-03 最后修改

VBA 脚本

' 获取文本内容作为一个值出现在 DML 语句中的拼写形式，入参支持多个单元格。
Function dbval(textOrCells As Variant) As String
    If (TypeOf textOrCells Is Range) Then
        dbval = ""
        For Each c In textOrCells.Cells
            If (Len(dbval) > 0) Then
                dbval = dbval & ", "
            End If
            dbval = dbval & dbval(c.Text)
        Next
    Else
        dbval = Trim("" & textOrCells)

        If (Len(dbval) <= 0) Then
            dbval = "null"
            Exit Function
        End If

        dbval = Replace(dbval, "'", "' || chr(" & Asc("'") & ") || '")
        dbval = Replace(dbval, """", "' || chr(" & Asc("""") & ") || '")
        dbval = Replace(dbval, "\", "' || chr(" & Asc("\") & ") || '")
        dbval = Replace(dbval, "`", "' || chr(" & Asc("`") & ") || '")
        dbval = Replace(dbval, "!", "' || chr(" & Asc("!") & ") || '")
        dbval = Replace(dbval, "@", "' || chr(" & Asc("@") & ") || '")
        dbval = Replace(dbval, "#", "' || chr(" & Asc("#") & ") || '")
        dbval = Replace(dbval, "$", "' || chr(" & Asc("$") & ") || '")
        dbval = Replace(dbval, "%", "' || chr(" & Asc("%") & ") || '")
        dbval = Replace(dbval, "&", "' || chr(" & Asc("&") & ") || '")
        dbval = Replace(dbval, ";", "' || chr(" & Asc(";") & ") || '")
        dbval = Replace(dbval, vbTab, "' || chr(" & Asc(vbTab) & ") || '")
        dbval = Replace(dbval, vbCr, "' || chr(" & Asc(vbCr) & ") || '")
        dbval = Replace(dbval, vbLf, "' || chr(" & Asc(vbLf) & ") || '")

        dbval = "'" & dbval & "'"
        dbval = Replace(dbval, "'' || ", "")
        dbval = Replace(dbval, " || ''", "")
    End If
End Function

' 获取数据行对应的 DML 插入语句。
Function dbins(tableName As String, titleCells As Range, valueCells As Range) As String
    dbins = "insert into " & tableName & " (" & Replace(dbval(titleCells), "'", "") & ") values (" & dbval(valueCells) & ");"
End Function

' 获取数据行对应的 DML 删除语句。
Function dbdel(tableName As String, primaryKey As String, primaryValue As String) As String
    dbdel = dbval(primaryValue)
    dbdel = "delete from " & tableName & " where " & primaryKey & IIf(dbdel = "null", " is ", " = ") & dbdel & ";"
End Function

使用示例

详细内容见附件《USER_INFO.xlsm》

表名:	USER_INFO
序号:	USER_ID	ACCOUNT	USERNAME	TELNUM	EMAIL	STATUS	REMARK
1	1001	admin	Administrator			1	系统管理员
2	1002	stduser	Standard User		stduser@example.org	1	标准用户
3	1003	demo	Demo	010-888888	demo@example.org		演示用户

DELETE_STATEMENT

delete from USER_INFO where USER_ID = '1001';

delete from USER_INFO where USER_ID = '1002';

delete from USER_INFO where USER_ID = '1003';

INSERT_STATEMENT

insert into USER_INFO (USER_ID, ACCOUNT, USERNAME, TELNUM, EMAIL, STATUS, REMARK) values ('1001', 'admin', 'Administrator', null, null, '1', '系统管理员');

insert into USER_INFO (USER_ID, ACCOUNT, USERNAME, TELNUM, EMAIL, STATUS, REMARK) values ('1002', 'stduser', 'Standard User', null, 'stduser@example.org', '1', '标准用户');

insert into USER_INFO (USER_ID, ACCOUNT, USERNAME, TELNUM, EMAIL, STATUS, REMARK) values ('1003', 'demo', 'Demo', '010-888888', 'demo@example.org', null, '演示用户');

标签：