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 not want to use a script, it must be a single executable query.
我不想使用脚本,它必须是单个可执行查询。
4 个解决方案
#1
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将提示输入绑定变量,您可以输入它并点击apply。
#2
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 (&&)
注意第二个(和后续)变量将如何使用双符号(&&)
#3
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'),那么为替换变量输入的值将记住会话的长度。请注意,在这种情况下,如果重新执行查询,将不会再次提示您,它将继续使用相同的值。
#4
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:
因此,当您使用&替换时,您只需使用单个&更快的方式,而不必再次键入值。通过使用&&,值变为锁定到变量名称。所以&&变量与&& 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”应该足以用于另一组。
#1
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将提示输入绑定变量,您可以输入它并点击apply。
#2
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 (&&)
注意第二个(和后续)变量将如何使用双符号(&&)
#3
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'),那么为替换变量输入的值将记住会话的长度。请注意,在这种情况下,如果重新执行查询,将不会再次提示您,它将继续使用相同的值。
#4
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:
因此,当您使用&替换时,您只需使用单个&更快的方式,而不必再次键入值。通过使用&&,值变为锁定到变量名称。所以&&变量与&& 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”应该足以用于另一组。