VBA　SQL文ログ出力を解析するツール

最新推荐文章于 2021-02-22 17:37:04 发布

anshyy227

最新推荐文章于 2021-02-22 17:37:04 发布

阅读量216

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/anshyy227/article/details/84446775

本文介绍了一个使用VB.NET实现的SQL语句转换功能，包括字符串替换和格式调整，以及将转换后的SQL语句复制到剪贴板的操作。通过实例演示了如何将用户输入的SQL组织和参数进行格式化，并提供了SQL转换函数，使得SQL语句更具可读性和易于维护。

Option Explicit
Dim MyDataObject As DataObject

Private Sub CommandButton1_Click()
Dim sqlOrg, sqlWithParams, PARAMSYMPOL, QUOTER As String
Dim params() As String
Dim i As Integer
PARAMSYMPOL = "?"
QUOTER = "'"
sqlOrg = TextBox1.Text

params = Split(TextBox2.Text, ",")
For i = 0 To UBound(params)
Dim tempParam As String
tempParam = QUOTER + LTrim(params(i)) + QUOTER
sqlOrg = Replace(sqlOrg, PARAMSYMPOL, tempParam, 1, 1)
Next i
TextBox3.Text = SQLConvert(sqlOrg) 'sqlOrg
CommandButton2_Click
End Sub

Private Sub CommandButton2_Click()
If TextBox3.Text <> "" Then
'クリップボードにコピーする
Set MyDataObject = New DataObject
MyDataObject.SetText TextBox3.Text
MyDataObject.PutInClipboard
End If
End Sub

Private Sub CommandButton3_Click()
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
End Sub

Private Sub CommandButton4_Click()

End Sub

Function SQLConvert(StrOrg)
Dim tempStrSQL As String
Dim index As Integer
Dim vbSpace As String
vbSpace = Chr(32)

' index = InStr(1, StrOrg, Chr(10), 0)
' index = InStr(1, StrOrg, Chr(13), 0)
' index = InStr(1, StrOrg, "SELECT" + vbSpace, 0)
While InStr(1, StrOrg, vbSpace + vbSpace, 0) > 0
StrOrg = Replace(StrOrg, vbSpace + vbSpace, vbSpace)
Wend

StrOrg = Replace(StrOrg, vbNewLine, "")
StrOrg = Replace(StrOrg, vbCr, "")
StrOrg = Replace(StrOrg, "SELECT" + vbSpace, vbLf + "SELECT" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "FROM" + vbSpace, vbLf + "FROM" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "WHERE" + vbSpace, vbLf + "WHERE" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "ORDER" + vbSpace + "BY" + vbSpace, vbLf + "ORDER" + vbSpace + "BY" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "UNION" + vbSpace + "ALL" + vbSpace, vbLf + "UNION" + vbSpace + "ALL")
StrOrg = Replace(StrOrg, vbSpace + "UNION" + vbSpace, vbLf + "UNION")
StrOrg = Replace(StrOrg, vbSpace + "LEFT" + vbSpace + "JOIN" + vbSpace, vbLf + "LEFT JOIN" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "LEFT" + vbSpace + "OUTER" + vbSpace + "JOIN" + vbSpace, vbLf + "LEFT OUTER JOIN" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "LEFT" + vbSpace + "JOIN" + vbSpace, vbLf + "LEFT JOIN" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "RIGHT" + vbSpace + "OUTER" + vbSpace + "JOIN" + vbSpace, vbLf + "RIGHT OUTER JOIN" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "RIGHT" + vbSpace + "JOIN" + vbSpace, vbLf + "RIGHT JOIN" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "INNER" + vbSpace + "JOIN" + vbSpace, vbLf + "INNER JOIN" + vbLf + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "ON" + vbSpace, vbLf + "ON" + vbTab)

StrOrg = Replace(StrOrg, vbSpace + "AND" + vbSpace, vbLf + "AND" + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "OR" + vbSpace, vbLf + "OR" + vbTab)
StrOrg = Replace(StrOrg, vbSpace + "AS" + vbSpace, vbTab + vbTab + "AS" + vbTab)
'StrOrg = Replace(StrOrg, vbSpace + "WHERE" + vbSpace, vbLf + "WHERE" + vbLf + vbTab)

'StrOrg = Replace(StrOrg, "\\s*RIGHT\\s*OUTER\\s*JOIN\\s*", "\\n\\tRIGHT OUTER JOIN\\t\\t")
'StrOrg = Replace(StrOrg, "\\s*RIGHT\\s*JOIN\\s*", "\\n\\tRIGHT JOIN\\t\\t")
'StrOrg = Replace(StrOrg, "\\sAND\\s", "\\nAND\\t")
'StrOrg = Replace(StrOrg, "\\sOR\\s", "\\nOR\\t")
'StrOrg = Replace(StrOrg, "\\sAS\\s", "\\tAS\\t\\t")
StrOrg = Replace(StrOrg, vbSpace + ",", vbLf + vbTab + ",")

SQLConvert = StrOrg
End Function