将字符串拆分为多行

2024-01-10

我编写了一个连接到 Oracle 数据库以选择特定表中的多个条目的脚本。

该声明如下所示：

rs.open "SELECT PATH301 FROM NC301B WHERE EDIPROC like 'P30_' AND (LF301M > 0) AND (PATH301 NOT LIKE '%saptemp%') AND (PATH301 NOT LIKE '%SAPTEMP%') AND (PATH301 NOT LIKE '%usr%') AND (PATH301 NOT LIKE '%Windows%');", cn, 3

现在我想知道是否可以将此语句拆分为多行。例如这样：

rs.open "SELECT PATH301 FROM NC301B 
WHERE EDIPROC like 'P30_' 
AND (LF301M > 0) 
AND (PATH301 NOT LIKE '%saptemp%') 
AND (PATH301 NOT LIKE '%SAPTEMP%') 
AND (PATH301 NOT LIKE '%usr%') 
AND (PATH301 NOT LIKE '%Windows%');", cn, 3

那第一SELECT声明太大了，看起来一点也不好看。我希望你明白我的意思。

好吧，无论如何我都会发布一个答案，从来都不是这个的粉丝西部最快的枪问题 https://meta.stackexchange.com/q/9731/202529

使用行继续符

As I 评论中提到 https://stackoverflow.com/questions/37564704/split-select-statement/37565683#comment62617433_37564961听起来您想让 SQL 命令在代码中更具可读性。执行此操作的正常方法是使用行继续符(_)也称为语句中断 https://technet.microsoft.com/en-us/library/ee198844.aspx.

Dim sql
sql = "SELECT PATH301 " & _
  "FROM NC301B " & _
  "WHERE EDIPROC like 'P30_' " & _
  "AND (LF301M > 0) " & _
  "AND (PATH301 NOT LIKE '%saptemp%') " & _
  "AND (PATH301 NOT LIKE '%SAPTEMP%') " & _
  "AND (PATH301 NOT LIKE '%usr%') " & _
  "AND (PATH301 NOT LIKE '%Windows%');"

请记住，如果我们不处理字符串，则情况会有所不同，例如继续If两行的语句看起来像这样；

If result = ( _
  condition1 _
  And condition2 _
  And condition3) Then

因为我们不能跨行中断字符串，所以我们通过终止继续该行的字符串，然后将该字符串连接到下一个字符串来作弊& _。这也意味着这将会起作用；

Dim sql
sql = "SELECT PATH301 " _
  & "FROM NC301B " _
  & "WHERE EDIPROC like 'P30_' " _
  & "AND (LF301M > 0) " _
  & "AND (PATH301 NOT LIKE '%saptemp%') " _
  & "AND (PATH301 NOT LIKE '%SAPTEMP%') " _
  & "AND (PATH301 NOT LIKE '%usr%') " _
  & "AND (PATH301 NOT LIKE '%Windows%');"

这完全取决于个人喜好，并且任何一种方式都比其他方式没有任何性能优势。

就我个人而言，尽管我发现这种方法弊大于利，尤其是在涉及 SQL 字符串时。

使用字符串连接

假设您想在没有条件之一的情况下进行测试

AND (PATH301 NOT LIKE '%SAPTEMP%')

尝试注释掉该行将生成

Microsoft VBScript 编译错误：
语法错误

这是一个例子；

Dim sql
sql = "SELECT PATH301 " & _
  "FROM NC301B " & _
  "WHERE EDIPROC like 'P30_' " & _
  "AND (LF301M > 0) " & _
  "AND (PATH301 NOT LIKE '%saptemp%') " & _
  '"AND (PATH301 NOT LIKE '%SAPTEMP%') " & _
  "AND (PATH301 NOT LIKE '%usr%') " & _
  "AND (PATH301 NOT LIKE '%Windows%');"

这使得它非常不灵活，这就是为什么我更喜欢使用建立一个字符串，像这样；

Dim sql: sql = ""
sql = sql & "SELECT PATH301 "
sql = sql & "FROM NC301B "
sql = sql & "WHERE EDIPROC like 'P30_' "
sql = sql & "AND (LF301M > 0) "
sql = sql & "AND (PATH301 NOT LIKE '%saptemp%') "
sql = sql & "AND (PATH301 NOT LIKE '%SAPTEMP%') "
sql = sql & "AND (PATH301 NOT LIKE '%usr%') "
sql = sql & "AND (PATH301 NOT LIKE '%Windows%');"

尽管需要一些额外的工作，但它更加灵活，并且允许您通过注释掉行来测试 SQL 字符串，而不会影响整个 SQL 字符串，如下所示；

Dim sql: sql = ""
sql = sql & "SELECT PATH301 "
sql = sql & "FROM NC301B "
sql = sql & "WHERE EDIPROC like 'P30_' "
sql = sql & "AND (LF301M > 0) "
sql = sql & "AND (PATH301 NOT LIKE '%saptemp%') "
'sql = sql & "AND (PATH301 NOT LIKE '%SAPTEMP%') "
sql = sql & "AND (PATH301 NOT LIKE '%usr%') "
sql = sql & "AND (PATH301 NOT LIKE '%Windows%');"

我喜欢做的另一件小事是在每一行后面加上& vbNewLine像这样;

Dim sql: sql = ""
sql = sql & "SELECT PATH301 " & vbNewLine
sql = sql & "FROM NC301B " & vbNewLine
sql = sql & "WHERE EDIPROC like 'P30_' " & vbNewLine
sql = sql & "AND (LF301M > 0) " & vbNewLine
sql = sql & "AND (PATH301 NOT LIKE '%saptemp%') " & vbNewLine
sql = sql & "AND (PATH301 NOT LIKE '%SAPTEMP%') " & vbNewLine
sql = sql & "AND (PATH301 NOT LIKE '%usr%') " & vbNewLine
sql = sql & "AND (PATH301 NOT LIKE '%Windows%');"

这样输出字符串的时候（在使用经典 ASP、WScript 等）它的格式正确并且在 HTML 页面中显示时正确（如果使用经典 ASP）你可以轻松使用

sql = Replace(sql, vbNewLine, "<br />")

正确格式化它，在尝试调试动态 SQL 问题时非常有用。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

vbscript