TOAD脚本中的变量

问题描述

我有一个正在TOAD中执行的SQL脚本.目前，我已经将其布置为仅一个语句一个语句，因此:

I have a SQL script that is being executed in TOAD. Currently, I have it laid out with just statement after statement, thusly:

select such-and-such from somewhere;

delete other-thing from somewhere-else;

以此类推.一些where子句最终是重复的，因为我需要复杂的内部查询来获取要操作的特定ID.我想在脚本的开头捕获一个变量中的ID，然后在后续的where子句中使用该变量.像这样:

And so on. Some of the where clauses end up being repetitive because I have complex inner queries to get particular IDs to operate on. I'd like to capture the ID in the beginning of the script in a variable, and then use that variable in subsequent where clauses. So something like this:

variable MY_ID = select the-ID from somewhere;

select such-and-such from somewhere where ID = @MY_ID;

很显然，我正在构造该语法，但这就是我想要的.但是我不确定在TOAD脚本中是否可行.我知道我可以将整个内容转换为PL/SQL块，但是出于各种原因，我试图避免这样做.

Obviously, I'm making up that syntax, but that is what I'm looking for. But I'm not sure if that is possible in a TOAD script. I know I can convert the whole thing to a PL/SQL block but I'm trying to avoid having to do that for various reasons.

有什么方法可以使用TOAD而不转换为PL/SQL块吗?

Any way to do this using TOAD without converting to a PL/SQL block?

推荐答案

我认为这将完成您想要的事情.您可以声明一个绑定变量，在其中插入一个值，然后在以后的语句中使用它.

I think this will accomplish what you want. You can declare a bind variable, insert a value into it, and then use it in future statements.

variable l_var varchar2(1);

begin
  select dummy
    into :l_var
    from dual;
end;

select *
  from dual
 where dummy = :l_var;

这篇关于TOAD脚本中的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！