对于在单个语句中多次出现的替换变量,如何只对SQL Developer / SQL +提示一次?

时间:2022-08-03 06:31:09

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”应该足以用于另一组。