SQL TOAD 脚本中的变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1947280/
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
Variables in TOAD scripts
提问by RationalGeek
I have a SQL script that is being executed in TOAD. Currently, I have it laid out with just statement after statement, thusly:
我有一个正在 TOAD 中执行的 SQL 脚本。目前，我只用一个又一个的声明来布置它，因此：
select such-and-such from somewhere;
delete other-thing from somewhere-else;
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:
等等。一些 where 子句最终是重复的，因为我有复杂的内部查询来获取要操作的特定 ID。我想在变量中捕获脚本开头的 ID，然后在后续的 where 子句中使用该变量。所以像这样：
variable MY_ID = select the-ID from somewhere;
select such-and-such from somewhere where ID = @MY_ID;
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?
有什么方法可以使用 TOAD 做到这一点而不转换为 PL/SQL 块？
回答by Daniel Emge
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;
回答by Chris Ellison
I use SQL*PLUS substitution variables. They are supported by TOAD. You can execute this code by pressing F5.
我使用 SQL*PLUS 替换变量。它们由 TOAD 支持。您可以按 F5 执行此代码。
COLUMN VAR NEW_VALUE VAR_VALUE
SELECT 'SOMETHING' VAR FROM DUAL; --this sets 'VAR_VALUE' = 'SOMETHING'
SELECT '&VAR_VALUE' FROM DUAL; --this uses the value set by the previous stmt.
回答by Dan
I no longer actively use TOAD, but there should be some mechanism for setting values for bind parameters ie select such-and-such from somewhere where ID = :myid;such that every time it occurs TOAD supplies the same value for that parameter.
我不再主动使用 TOAD，但应该有一些机制来设置绑定参数的值，即select such-and-such from somewhere where ID = :myid;每次发生时 TOAD 都会为该参数提供相同的值。
Alternatively, you could create a session context value or PL/SQL package variable (note: not the same thing as rewriting your entire code to use PL/SQL). See this question
或者，您可以创建会话上下文值或 PL/SQL 包变量（注意：与重写整个代码以使用 PL/SQL 不同）。看到这个问题
回答by Fandango68
The "Calculated Fields" feature in TOAD is actually quite powerful if used in the right way. It's nothing more than a "token" script editor that attaches itself to the Query itself. It's only available via the Query Design Editor, and not from the native Editor, which allows you to write straight up SQL.
如果使用得当，TOAD 中的“计算字段”功能实际上非常强大。它只不过是一个将自身附加到查询本身的“令牌”脚本编辑器。它只能通过查询设计编辑器使用，而不能从允许您直接编写 SQL 的本机编辑器使用。
As a hint, next time you create a Query Designed in TOAD and need to create complex WHERE or sub-queries, try the "Calculated Fields" feature and use the FORMS option to basically attach your conditions to a given column or query. You'll be surprised how powerful it is. And it helps keep your SQL query in a nice readable format.
作为提示，下次您创建在 TOAD 中设计的查询并需要创建复杂的 WHERE 或子查询时，请尝试“计算字段”功能并使用 FORMS 选项基本上将您的条件附加到给定的列或查询。你会惊讶于它的强大。它有助于使您的 SQL 查询保持良好的可读格式。
