Wiki源代码根据库表表格生成DML语句
由用户 Qiongpan Ke 在 2024-04-19 保存的版本 4.1
复制
导出
打印预览
源代码
子项
内容
附件 (1)
历史记录
信息
Likes
隐藏最后作者
| author | version | line-number | content |
|---|---|---|---|
 |
1.1 | 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("""") & ") || '") | ||
| |
3.1 | 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) & ") || '") | ||
| |
1.1 | 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 | |||
| 49 | ' 获取数据行对应的 DML 删除语句。 | ||
| 50 | Function dbdel(tableName As String, primaryKey As String, primaryValue As String) As String | ||
| |
3.1 | 51 | dbdel = dbval(primaryValue) |
| 52 | dbdel = "delete from " & tableName & " where " & primaryKey & IIf(dbdel = "null", " is ", " = ") & dbdel & ";" | ||
| |
1.1 | 53 | End Function |
| |
3.1 | 54 | |
| |
1.1 | 55 | {{/code}} |
| 56 | |||
| 57 | = 使用示例 = | ||
| 58 | |||
| 59 | 详细内容见附件《[[USER_INFO.xlsm>>attach:USER_INFO.xlsm]]》 | ||
| 60 | |||
| 61 | |表名:|(% colspan="7" %)USER_INFO | ||
| 62 | |序号:|USER_ID|ACCOUNT|USERNAME|TELNUM|EMAIL|STATUS|REMARK | ||
| 63 | |1|1001|admin|Administrator| | |1|系统管理员 | ||
| 64 | |2|1002|stduser|Standard User| |[[stduser@example.org>>mailto:stduser@example.org]]|1|标准用户 | ||
| 65 | |3|1003|demo|Demo|010-888888|[[demo@example.org>>mailto:demo@example.org]]| |演示用户 | ||
| 66 | |||
| 67 | |DELETE_STATEMENT | ||
| 68 | |{{code language="sql"}}delete from USER_INFO where USER_ID = '1001';{{/code}} | ||
| 69 | |{{code language="sql"}}delete from USER_INFO where USER_ID = '1002';{{/code}} | ||
| 70 | |{{code language="sql"}}delete from USER_INFO where USER_ID = '1003';{{/code}} | ||
| 71 | |||
| 72 | |INSERT_STATEMENT | ||
| 73 | |{{code language="sql"}}insert into USER_INFO (USER_ID, ACCOUNT, USERNAME, TELNUM, EMAIL, STATUS, REMARK) values ('1001', 'admin', 'Administrator', null, null, '1', '系统管理员');{{/code}} | ||
| 74 | |{{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}} | ||
| 75 | |{{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}} |
粤公网安备 44011802000481号 粤ICP备2020117634号