何时或为何在 Oracle 数据库中使用“SET DEFINE OFF”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34332639/
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
When or Why to use a "SET DEFINE OFF" in Oracle Database
提问by Enrique Benito Casado
I'm watching a Script in Oracle and I see something I don't recognize
我正在 Oracle 中观看脚本，但看到了一些我不认识的内容
REM INSERTING into database1."Users"
SET DEFINE OFF;
Insert into database1."Users" ("id","right") values ('1','R');
I'm looking for documentation about "set define off" and it's literally writing "disable the parsing of commands to replace substitution variable with their values"
我正在寻找有关“设置定义关闭”的文档，它的字面意思是“禁用解析命令以将替换变量替换为其值”
I don't really understand what they want to say.
我真的不明白他们想说什么。
Can anyone help me?
谁能帮我？
回答by Tony Andrews
By default, SQL Plus treats '&' as a special character that begins a substitution string. This can cause problems when running scripts that happen to include '&' for other reasons:
默认情况下，SQL Plus 将 '&' 视为开始替换字符串的特殊字符。由于其他原因，当运行碰巧包含“&”的脚本时，这可能会导致问题：
SQL> insert into customers (customer_name) values ('Marks & Spencers Ltd');
Enter value for spencers:
old 1: insert into customers (customer_name) values ('Marks & Spencers Ltd')
new 1: insert into customers (customer_name) values ('Marks Ltd')
1 row created.
SQL> select customer_name from customers;
CUSTOMER_NAME
------------------------------
Marks Ltd
If you know your script includes (or may include) data containing '&' characters, and you do not want the substitution behaviour as above, then use set define offto switch off the behaviour while running the script:
如果您知道您的脚本包含（或可能包含）包含 '&' 字符的数据，并且您不希望出现上述替换行为，请set define off在运行脚本时使用来关闭该行为：
SQL> set define off
SQL> insert into customers (customer_name) values ('Marks & Spencers Ltd');
1 row created.
SQL> select customer_name from customers;
CUSTOMER_NAME
------------------------------
Marks & Spencers Ltd
You might want to add set define onat the end of the script to restore the default behaviour.
您可能希望set define on在脚本末尾添加以恢复默认行为。
回答by Durga Viswanath Gadiraju
Here is the example:
这是示例：
SQL> set define off;
SQL> select * from dual where dummy='&var';
no rows selected
SQL> set define on
SQL> /
Enter value for var: X
old 1: select * from dual where dummy='&var'
new 1: select * from dual where dummy='X'
D
-
X
With set define off, it took a row with &varvalue, prompted a user to enter a value for it and replaced &varwith the entered value (in this case, X).
使用set define off，它需要一行带有&var值，提示用户为其输入一个值并替换&var为输入的值（在本例中为X）。
