在 where 子句中使用 Oracle SQL 变量时遇到问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14401795/
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
Trouble using Oracle SQL variable in where clause
提问by user1774673
I'm trying to write a script to help assist with the numerous "What's happened to this actionlist" type calls, but i'm coming up against a brick wall doing some of the most simplest operations.
我正在尝试编写一个脚本来帮助处理大量的“这个动作列表发生了什么”类型的调用，但是我在做一些最简单的操作时遇到了砖墙。
I'm trying to declare a variable and use it in the where clause of a query. I've trimmed everything irrelevant out of the query, in an attempt to get this core functionality to work.
我正在尝试声明一个变量并在查询的 where 子句中使用它。我已经删除了与查询无关的所有内容，试图让这个核心功能发挥作用。
The id is usually an 18 digit number, but does occasionally contain alpha-numerics, hence why it is a varchar. The column I'm trying to compare it to is an 18 byte varchar field.
id 通常是一个 18 位数字，但偶尔会包含字母数字，因此它是一个 varchar。我试图与它进行比较的列是一个 18 字节的 varchar 字段。
declare
id_to_check VARCHAR(18);
begin
id_to_check := '549576015500000109';
select
txn_timestamp, exception_type
from cut.event
where irn_id = id_to_check;
end;
Every time it throws an error: 'an INTO clause is expected in this select statement'. I understand how INTO's work, i.e. if I wanted to assign the result of a select to the variable, but I don't understand how that would apply in this instance as I'm not assigning the result of the query to a variable?
每次它抛出一个错误：'在这个 select 语句中需要一个 INTO 子句'。我了解 INTO 的工作方式，即如果我想将选择的结果分配给变量，但我不明白这将如何应用于这种情况，因为我没有将查询的结果分配给变量？
The other frustrating thing is I'm actually following documentation on docs.oracle.com.
另一件令人沮丧的事情是我实际上正在关注 docs.oracle.com 上的文档。
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/overview.htm#LNPLS001.
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/overview.htm#LNPLS001。
I've also looked at various google results, but I can't figure out how to do a comparison with the variable without first selecting something into it, but as you can see, I can't select into because I only need it for comparison reasons?
我还查看了各种谷歌结果，但我无法弄清楚如何在不先选择某些内容的情况下与变量进行比较，但是正如您所看到的，我无法选择，因为我只需要它比较原因？
Kind Regards, Ian
亲切的问候，伊恩
回答by Alex Poole
Rapha?l Althaus's answer applies if you are doing something in PL/SQL, but I'm not sure that's actually what you want. It sounds like you want a plain SQL query, but you want to be able to pass it a value? Assuming you're running this in SQL*Plus or SQL Developer you have a few ways to declare and use a fixed value. (Similar methods will be available in other clients; PL/SQL developer seems to like both of these, at least in the command window, but not sure about Toad or anything else).
如果您在 PL/SQL 中做某事，那么 Rapha?l Althaus 的回答适用，但我不确定这是否真的是您想要的。听起来您想要一个普通的 SQL 查询，但您希望能够向它传递一个值？假设您在 SQL*Plus 或 SQL Developer 中运行它，您有几种方法可以声明和使用固定值。（类似的方法将在其他客户端中可用；PL/SQL 开发人员似乎喜欢这两种方法，至少在命令窗口中，但不确定 Toad 或其他任何东西）。
1) Using a substitution variable:
1) 使用替代变量：
define id_to_check = 549576015500000109
select txn_timestamp, exception_type
from cut.event
where irn_id = '&id_to_check';
2) Using a bind variable:
2）使用绑定变量：
variable id_to_check varchar2(18)
exec :id_to_check := '549576015500000109';
select txn_timestamp, exception_type
from cut.event
where irn_id = :id_to_check;
The execis shorthand for a small anonymous PL/SQL block, but apart from step of assigning the value to the variable, it's plain SQL, so the query doesn't need to be wrapped in a begin ... end, and you don't have to worry about selecting intovariables.
theexec是一个小的匿名 PL/SQL 块的简写，但除了给变量赋值的步骤之外，它是普通的 SQL，所以查询不需要被包裹在 a 中begin ... end，你不必担心选择into变量。
In either case you can have the ID value passed as an argument, or ask for it as part of the script, when the script it run. For example, you could write a query.sqlscript:
在任何一种情况下，您都可以将 ID 值作为参数传递，或者在脚本运行时要求将其作为脚本的一部分。例如，您可以编写一个query.sql脚本：
define id_to_check = &1
select txn_timestamp, exception_type
from cut.event
where irn_id = '&id_to_check';
... and run it with:
...并运行它：
sqlplus -l -s <user>/<password> @query 549576015500000109
... or as:
...或作为：
accept id_to_check prompt 'Enter the ID to check: '
select txn_timestamp, exception_type
from cut.event
where irn_id = '&id_to_check';
... and have the user prompted to enter the ID at run-time.
... 并提示用户在运行时输入 ID。
回答by Rapha?l Althaus
The question is not "Do you want to put the result into a variable".
问题不是“您想将结果放入变量中吗”。
In the "body" of an Oracle procedure, you just can't use a SELECTwithout an INTOclause.
在 Oracle 过程的“主体”中，您不能使用SELECT不带INTO子句的 a。
(In fact you can have, but in a cursor).
（实际上你可以有，但在一个游标中）。
so
所以
declare
id_to_check VARCHAR(18);
event_timestamp cut.event.txn_timestamp%TYPE;
event_exception cut.event.exception_type%TYPE;
begin
id_to_check := '549576015500000109';
select
txn_timestamp, exception_type
INTO event_timestamp, event_exception
from cut.event
where irn_id = id_to_check;
end;
Caution : if no result is found, this will raise a NO_DATA_FOUND exception.
注意：如果没有找到结果，这将引发 NO_DATA_FOUND 异常。
You can manage it with
你可以管理它
begin
id_to_check := '549576015500000109';
BEGIN
select
txn_timestamp, exception_type
INTO event_timestamp, event_exception
from cut.event
where irn_id = id_to_check;
EXCEPTION WHEN NO_DATA_FOUND THEN
--...something
END
end
回答by fdreger
Your real problem is NOT about using variable (you got that right), but about returning cursors from PL/SQL blocks.
您真正的问题不在于使用变量（您说得对），而在于从 PL/SQL 块返回游标。
You seem to believe that a bare select at the end of a procedure should somehow make it return the results or at least display them. While some databases work like that - Oracle does not. If you want any data to leave a block of any kind (like function or procedure), you have to pass them explicitly. Usually this is done by using an in parameter of a cursor ref type.
您似乎认为程序末尾的裸选择应该以某种方式使其返回结果或至少显示它们。虽然有些数据库是这样工作的 - Oracle 没有。如果您希望任何数据离开任何类型的块（如函数或过程），您必须明确地传递它们。通常这是通过使用游标引用类型的 in 参数来完成的。
As you can see, the select without into would not make sense in Oracle. Even if you managed to make it work and select the data, you would not be able to access the results.
如您所见，不带 into 的选择在 Oracle 中没有意义。即使您设法使其工作并选择数据，您也无法访问结果。
