根据库表表格生成DML语句

|{{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}}

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
		49	' 获取数据行对应的 DML 删除语句。
		50	Function dbdel(tableName As String, primaryKey As String, primaryValue As String) As String
		51	dbdel = dbval(primaryValue)
		52	dbdel = "delete from " & tableName & " where " & primaryKey & IIf(dbdel = "null", " is ", " = ") & dbdel & ";"
		53	End Function
		54
		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}}

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