对于在单个语句中多次出现的替换变量，如何让 SQL Developer/SQL+ 只提示一次？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/735429/
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 can I make SQL Developer/SQL+ prompt only once for a substitution variable that occurs multiple times in a single statement?
提问by Trampas Kirk
I have a query roughly like this:
我有一个大致如下的查询：
select *
from A_TABLE
where A_COLUMN = '&aVariable'
union
select *
from A_TABLE
where B_COLUMN = '&aVariable';
But when I run it, SQL Developer prompts me for the variable twice, even though it's the same variable.
但是当我运行它时，SQL Developer 两次提示我输入变量，即使它是同一个变量。
If there's a way to make it prompt only once for a variable that is used twice, how do I do it?
如果有办法让它只提示一次使用两次的变量，我该怎么做？
I do notwant to use a script, it must be a single executable query.
我不希望使用脚本，它必须是一个可执行查询。
回答by Trampas Kirk
As I was forming this post, I figured out how to do it:
在我撰写这篇文章时，我想出了如何做到这一点：
:a_var
select *
from A_TABLE
where A_COLUMN = :a_var
union
select *
from A_TABLE
where B_COLUMN = :a_var;
SQL Developer will then prompt for a bind variable, you can enter it and hit apply.
SQL Developer 然后将提示输入绑定变量，您可以输入它并点击应用。
回答by user313218
select *
from A_TABLE
where A_COLUMN = '&aVariable'
union
select *
from A_TABLE
where B_COLUMN = '&&aVariable';
Notice how the 2nd (and subsequent) variables will use double-ampersand (&&)
请注意第二个（及后续）变量将如何使用双与号 (&&)
回答by Dave Costa
I know you found another way to do it, but FYI the basic answer is that if you double up the ampersand (e.g., use '&&aVariable'), then the value you enter for the substitution variable will be remembered for the length of your session. Note that in this case if you re-execute the query you will not be prompted again, it will keep using the same value.
我知道您找到了另一种方法来做到这一点，但仅供参考，基本答案是，如果您将＆符号加倍（例如，使用“&&aVariable”），那么您为替换变量输入的值将在您的会话期间被记住. 请注意，在这种情况下，如果您重新执行查询，将不会再次提示您，它将继续使用相同的值。
回答by Juarren Helgerson
So when you use & substitution you are just using the single & as a faster way instead of having to type a value over again. By using the && the value becomes locked-in to the variable name. So &&variable would be different from &&variable1. By doing it this way you will be prompted once and that value you inputted will be adhered to that variable name. Simply put if you write your code like this:
因此，当您使用 & 替换时，您只是使用单个 & 作为一种更快的方式，而不必再次键入一个值。通过使用 && 值被锁定在变量名中。所以 &&variable 与 &&variable1 不同。通过这种方式，您将收到一次提示，并且您输入的值将遵循该变量名称。简单地说，如果你像这样编写代码：
SELECT *
FROM A_TABLE
WHERE A_COLUMN = '&&aVariable1'
UNION
SELECT *
FROM A_TABLE
WHERE B_COLUMN ='&&aVariable2';
Then if you wanted to use a different set of values you will have to change the variable name to something like this '&&aVariable3' and '&&aVariable4' should be sufficient for another set.
然后，如果您想使用一组不同的值，则必须将变量名称更改为类似这样的 '&&aVariable3' 和 '&&aVariable4' 应该足以用于另一组。
回答by Isa C
ACCEPT variableToPrompt PROMPT "Enter variable" DEFAULT "test";
SELECT
*
FROM
TABLE
WHERE
COLUMN = '&variableToPrompt'
Note that the ' in '&variableToPrompt' is only required if your variable is a string. The default value is optional.
请注意，仅当您的变量是字符串时，才需要 '&variableToPrompt' 中的 '。默认值是可选的。
