Excel - 根据库表表格生成DML语句

Page / - 最后修改者 Qiongpan Ke 打开 2024-04-18

根据库表表格生成DML语句

最后修改者 Qiongpan Ke

打开 2024-04-18

内容列表

VBA 脚本

使用示例

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, '演示用户');