在 Oracle SQL Developer 3.2 中使用变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16758586/
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
Using variables in Oracle SQL Developer 3.2
提问by LeleyX
I am extremely new to SQL, and manage to extract from some other queries we use the following syntax:
我对 SQL 非常陌生，并且设法从我们使用以下语法的其他一些查询中提取：
--VARIABLES
undefine PF_PROD --product;
undefine PF_PSG --shop;
--QUERY
SELECT *
FROM ET1250
WHERE PRODUCT=&&PF_PROD
AND PRICE_SHOP_GROUP=&&PF_PSG
ORDER BY PERIOD_YEAR desc,PERIOD_WEEK desc;
This works fine as long as I run the undefinestatements first, is there a way to make the query always ask for these variables without me having to undefinethem first?
只要我先运行undefine语句，这就可以正常工作，有没有办法让查询始终要求这些变量，而不必先使用undefine它们？
采纳答案by Alex Poole
Use a single &. This is covered briefly in the SQL Developer documentation:
使用单个&. SQL Developer 文档中简要介绍了这一点：
For substitution variables, the syntax &&variable assigns a permanent variable value, and the syntax &variable assigns a temporary (not stored) variable value.
对于替换变量，语法 &&variable 分配一个永久变量值，语法 &variable 分配一个临时（未存储）变量值。
... and in more detail in the SQL*Plus documentation, which is largely relevant for both clients.
... 以及SQL*Plus 文档中的更多详细信息，该文档主要与两个客户端相关。
Note that if you defineor accepta variable then it won't be prompted for even with a single ampersand, but that doesn't seem to be relevant to you at the moment.
请注意，如果您define或accept一个变量，那么即使使用单个＆符号也不会提示它，但目前这似乎与您无关。
回答by gavenkoa
There are two types of variable in SQL-plus: substitution and bind.
SQL-plus 中有两种类型的变量：替换和绑定。
Substitution variables can replace SQL*Plus command options or other hard-coded text:
替换变量可以替换 SQL*Plus 命令选项或其他硬编码文本：
define a = 1;
select &a from dual;
undefine a;
Bind variables store data values for SQL and PL/SQL statements executed in the RDBMS; they can hold single values or complete result setsb:
绑定变量存储 RDBMS 中执行的 SQL 和 PL/SQL 语句的数据值；它们可以保存单个值或完整的结果集b：
var x number;
exec :x := 10;
select :x from dual;
exec select count(*) into :x from from dual;
exec print x;
SQL Developer support substitution variables, but when you execute query with bind :varsyntax you are prompted for binding (in dialog box).
SQL Developer 支持替换变量，但是当您使用绑定:var语法执行查询时，系统会提示您进行绑定（在对话框中）。
Reference:
参考：
- http://www.oracle.com/technetwork/testcontent/sub-var-087723.htmlSQL*Plus Substitution Variables, Christopher Jones, 2004
- https://stackoverflow.com/a/22171706/173149
- http://www.oracle.com/technetwork/testcontent/sub-var-087723.htmlSQL*Plus 替换变量，克里斯托弗·琼斯，2004 年
- https://stackoverflow.com/a/22171706/173149
