如何忽略从 SQL Plus 运行的 SQL 脚本中的＆符号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/118190/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I ignore ampersands in a SQL script running from SQL Plus?
提问by JoshL
I have a SQL script that creates a package with a comment containing an ampersand (&). When I run the script from SQL Plus, I am prompted to enter a substitute value for the string starting with &. How do I disable this feature so that SQL Plus ignores the ampersand?
我有一个 SQL 脚本，它创建了一个包，其中包含一个与符号 (&) 的注释。当我从 SQL Plus 运行脚本时，系统会提示我为以 & 开头的字符串输入替代值。如何禁用此功能以便 SQL Plus 忽略 & 号？
回答by Austin Salonen
回答by Leigh Riffel
If you sometimes use substitution variables you might not want to turn define off. In these cases you could convert the ampersand from its numeric equivalent as in || Chr(38) ||or append it as a single character as in || '&' ||.
如果您有时使用替换变量，您可能不想关闭定义。在这些情况下，您可以将 & 符号从其等效的数字转换为 in|| Chr(38) ||或将其附加为单个字符，如 in || '&' ||。
回答by Cauca
I resolved with the code below:
我用下面的代码解决了：
set escape on
and put a \ beside & in the left 'value_\&_intert'
并在左边的 & 旁边放一个 \ 'value_\&_intert'
Att
阿特
回答by tvCa
You can set the special character, which is looked for upon execution of a script, to another value by means of using the SET DEFINE <1_CHARACTER>
您可以通过使用 SET DEFINE <1_CHARACTER>
By default, the DEFINE function itself is on, and it is set to &
默认情况下，DEFINE 函数本身是打开的，并且设置为&
It can be turned off - as mentioned already - but it can be avoided as well by means of setting it to a different value. Be very aware of what sign you set it to. In the below example, I've chose the # character, but that choice is just an example.
它可以关闭 - 如前所述 - 但也可以通过将其设置为不同的值来避免。非常清楚你将它设置为什么标志。在下面的示例中，我选择了 # 字符，但该选择只是一个示例。
SQL> select '&var_ampersand #var_hash' from dual;
Enter value for var_ampersand: a value
'AVALUE#VAR_HASH'
-----------------
a value #var_hash
SQL> set define #
SQL> r
1* select '&var_ampersand #var_hash' from dual
Enter value for var_hash: another value
'&VAR_AMPERSANDANOTHERVALUE'
----------------------------
&var_ampersand another value
SQL>
回答by ora
set define off <- This is the best solution I found
set define off <- 这是我找到的最佳解决方案
I also tried...
我也试过...
set define }
设置定义 }
I was able to insert several records containing ampersand characters '&' but I cannot use the '}' character into the text So I decided to use "set define off" and everything works as it should.
我能够插入几条包含与符号“&”的记录，但我不能在文本中使用“}”字符所以我决定使用“set define off”并且一切正常。
回答by user19387
According to this nice FAQthere are a couple solutions.
根据这个不错的常见问题解答，有几个解决方案。
You might also be able to escape the ampersand with the backslash character \if you can modify the comment.
\如果您可以修改注释，您也可以使用反斜杠字符转义与号。
回答by AWOLKiwi
I had a CASE statement with WHEN column = 'sometext & more text' THEN ....
我有一个 CASE 语句，其中 WHEN 列 = 'sometext & more text' THEN ....
I replaced it with WHEN column = 'sometext ' || CHR(38) || ' more text' THEN ...
我用 WHEN column = 'sometext ' || 替换它 CHR(38) || '更多文字'然后...
you could also use WHEN column LIKE 'sometext _ more text' THEN ...
您也可以使用 WHEN 列 LIKE 'sometext _ more text' THEN ...
(_ is the wildcard for a single character)
（_ 是单个字符的通配符）
