# 根据库表表格生成DML语句

由 Qiongpan Ke 于 2024-07-03 最后修改
导出
Office格式
以PDF格式导出 以ODT格式导出 以RTF格式导出
其他格式

选择要导出的页面：

选择所有/ 无

Legend:
  已创建页面
  修改后的扩展页
  Clean Extension Page

以HTML格式导出

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

0
标签：
由 Qiongpan Ke 在 2024-04-18 创建

- 附件 （1）
- 历史记录
- 信息

# 同级页面

# 博客类别

- https://www.keqiongpan.cn:80/bin/view/Blog/">全部

- News (0)
- Other (0)
- Personal (0)

# 当前已创建的

- 线性布局组件与占位组件
- React
- CSS Flexbox Cheat Sheet
- CSS界面布局控制属性概述
- CSS

粤公网安备 44011802000481号 粤ICP备2020117634号
版权所有，如发现盗用模仿必追诉法律责任！
CopyRight © 2020-2023 keqiongpan.cn. All Right Reserved.