# Wiki源代码根据库表表格生成DML语句

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

选择要导出的页面：

选择所有/ 无

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

以HTML格式导出

显示最后作者

author | version | line-number | content
--- | --- | --- | ---
|  | 1 | = VBA 脚本 =
|  | 2 |
|  | 3 | {{code language="basic"}}
|  | 4 | ' 获取文本内容作为一个值出现在 DML 语句中的拼写形式，入参支持多个单元格。
|  | 5 | Function dbval(textOrCells As Variant) As String
|  | 6 | If (TypeOf textOrCells Is Range) Then
|  | 7 | dbval = ""
|  | 8 | For Each c In textOrCells.Cells
|  | 9 | If (Len(dbval) > 0) Then
|  | 10 | dbval = dbval & ", "
|  | 11 | End If
|  | 12 | dbval = dbval & dbval(c.Text)
|  | 13 | Next
|  | 14 | Else
|  | 15 | dbval = Trim("" & textOrCells)
|  | 16 |
|  | 17 | If (Len(dbval) <= 0) Then
|  | 18 | dbval = "null"
|  | 19 | Exit Function
|  | 20 | End If
|  | 21 |
|  | 22 | dbval = Replace(dbval, "'", "' || chr(" & Asc("'") & ") || '")
|  | 23 | dbval = Replace(dbval, """", "' || chr(" & Asc("""") & ") || '")
|  | 24 | dbval = Replace(dbval, "\", "' || chr(" & Asc("\") & ") || '")
|  | 25 | dbval = Replace(dbval, "`", "' || chr(" & Asc("`") & ") || '")
|  | 26 | dbval = Replace(dbval, "!", "' || chr(" & Asc("!") & ") || '")
|  | 27 | dbval = Replace(dbval, "@", "' || chr(" & Asc("@") & ") || '")
|  | 28 | dbval = Replace(dbval, "#", "' || chr(" & Asc("#") & ") || '")
|  | 29 | dbval = Replace(dbval, "$", "' || chr(" & Asc("$") & ") || '")
|  | 30 | dbval = Replace(dbval, "%", "' || chr(" & Asc("%") & ") || '")
|  | 31 | dbval = Replace(dbval, "&", "' || chr(" & Asc("&") & ") || '")
|  | 32 | dbval = Replace(dbval, ";", "' || chr(" & Asc(";") & ") || '")
|  | 33 | dbval = Replace(dbval, vbTab, "' || chr(" & Asc(vbTab) & ") || '")
|  | 34 | dbval = Replace(dbval, vbCr, "' || chr(" & Asc(vbCr) & ") || '")
|  | 35 | dbval = Replace(dbval, vbLf, "' || chr(" & Asc(vbLf) & ") || '")
|  | 36 |
|  | 37 | dbval = "'" & dbval & "'"
|  | 38 | dbval = Replace(dbval, "'' || ", "")
|  | 39 | dbval = Replace(dbval, " || ''", "")
|  | 40 | End If
|  | 41 | End Function
|  | 42 |
|  | 43 | ' 获取数据行对应的 DML 插入语句。
|  | 44 | Function dbins(tableName As String, titleCells As Range, valueCells As Range) As String
|  | 45 | dbins = "insert into " & tableName & " (" & Replace(dbval(titleCells), "'", "") & ") values (" & dbval(valueCells) & ");"
|  | 46 | End Function
|  | 47 |
|  | 48 | ' 获取数据行对应的 DML 删除语句。
|  | 49 | Function dbdel(tableName As String, primaryKey As String, primaryValue As String) As String
|  | 50 | dbdel = dbval(primaryValue)
|  | 51 | dbdel = "delete from " & tableName & " where " & primaryKey & IIf(dbdel = "null", " is ", " = ") & dbdel & ";"
|  | 52 | End Function
|  | 53 | {{/code}}
|  | 54 |
|  | 55 | = 使用示例 =
|  | 56 |
|  | 57 | 详细内容见附件《[[USER_INFO.xlsm>>attach:USER_INFO.xlsm]]》
|  | 58 |
|  | 59 | |表名:|(% colspan="7" %)USER_INFO
|  | 60 | |序号:|USER_ID|ACCOUNT|USERNAME|TELNUM|EMAIL|STATUS|REMARK
|  | 61 | |1|1001|admin|Administrator|　|　|1|系统管理员
|  | 62 | |2|1002|stduser|Standard User|　|[[stduser@example.org>>mailto:stduser@example.org]]|1|标准用户
|  | 63 | |3|1003|demo|Demo|010-888888|[[demo@example.org>>mailto:demo@example.org]]|　|演示用户
|  | 64 |
|  | 65 | |DELETE_STATEMENT
|  | 66 | |{{code language="sql"}}delete from USER_INFO where USER_ID = '1001';{{/code}}
|  | 67 | |{{code language="sql"}}delete from USER_INFO where USER_ID = '1002';{{/code}}
|  | 68 | |{{code language="sql"}}delete from USER_INFO where USER_ID = '1003';{{/code}}
|  | 69 |
|  | 70 | |INSERT_STATEMENT
|  | 71 | |{{code language="sql"}}insert into USER_INFO (USER_ID, ACCOUNT, USERNAME, TELNUM, EMAIL, STATUS, REMARK) values ('1001', 'admin', 'Administrator', null, null, '1', '系统管理员');{{/code}}
|  | 72 | |{{code language="sql"}}insert into USER_INFO (USER_ID, ACCOUNT, USERNAME, TELNUM, EMAIL, STATUS, REMARK) values ('1002', 'stduser', 'Standard User', null, 'stduser@example.org', '1', '标准用户');{{/code}}
|  | 73 | |{{code language="sql"}}insert into USER_INFO (USER_ID, ACCOUNT, USERNAME, TELNUM, EMAIL, STATUS, REMARK) values ('1003', 'demo', 'Demo', '010-888888', 'demo@example.org', null, '演示用户');{{/code}}

# 同级页面

# 博客类别

- 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.