如何在 Oracle PL/SQL where 子句中使用变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5666647/
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
How to use variables in an Oracle PL/SQL where clause
提问by MattB
I can't seem to get variables to work in an Oracle PL/SQL where clause. I come from a Microsoft SQL Server background and there it was easy. For example, what would be all steps needed to do something similar to the following?
我似乎无法让变量在 Oracle PL/SQL where 子句中工作。我来自 Microsoft SQL Server 背景，这很容易。例如，执行以下类似操作所需的所有步骤是什么？
declare @var int set @var = 1
select * from SomeTable where SomeField = @var
This doesn't seem like it should be hard in PL/SQL, but evidently it is. :-/ I hear I need to use cursors and the like in PL/SQL?
这在 PL/SQL 中似乎并不难，但显然确实如此。:-/ 我听说我需要在 PL/SQL 中使用游标等？
Any help would be greatly appreciated. Thanks.
任何帮助将不胜感激。谢谢。
回答by Tony Andrews
What do you want to do with the data that the SELECT returns? If you just want to see it you don't need PL/SQL at all, just do this in SQL Plus:
你想用 SELECT 返回的数据做什么？如果您只是想查看它，您根本不需要 PL/SQL，只需在 SQL Plus 中执行此操作：
variable var number
exec :var := 1
select * from SomeTable where SomeField = :var;
Or in a tool like SQL Developer or Toad, just do this:
或者在 SQL Developer 或 Toad 之类的工具中，只需执行以下操作：
select * from SomeTable where SomeField = :var;
and it will prompt you to enter the value for :var.
它会提示您输入 :var 的值。
回答by Simon
The following code declares a variable varto use in the WHEREclause, and a variable resultto put the result in then executes it inside a PL/SQL block.
以下代码声明了一个var要在WHERE子句中使用的变量，以及一个result将结果放入的变量，然后在 PL/SQL 块中执行它。
DECLARE
var INT := 1;
result INT;
BEGIN
SELECT 123
INTO result
FROM DUAL
WHERE var = 1;
DBMS_OUTPUT.put_line (var);
DBMS_OUTPUT.put_line (result);
END;
The DBMS_OUTPUT.PUT_LINEcalls make it produce this DBMS output:
该DBMS_OUTPUT.PUT_LINE电话使之产生这个DBMS输出：
1
123
回答by tbone
declare
type t_rec is record
(
col1 number,
col2 myTable.col2%type
);
v_rec t_rec;
type t_tab is table of v_rec%type index by binary_integer;
v_tab t_tab;
begin
select col1, col2
bulk collect into v_tab
from myTable
where col3 = 'BLAH';
-- do something great with v_tab...
end;
Also know that if you try to select into (or bulk collect into) a variable and no rows are returned, you'll get a no_data_found exception, so you may want to handle that situation.
还要知道，如果您尝试选择（或批量收集）一个变量并且没有返回任何行，您将收到 no_data_found 异常，因此您可能需要处理这种情况。
See more hereon pl/sql collections. Above uses an associative array, but there are nested tables and varrays as well. Again, see the link.
参见更多在这里在PL / SQL集合。以上使用关联数组，但也有嵌套表和变量数组。再次，请参阅链接。
Hope that helps.
希望有帮助。
回答by Irfan Ashraf
I use it like this
我像这样使用它
select * from sec_mainmenu where serno = '&MENU_ID';
When you run it, pl/sql will prompt for MENU_ID value.
当你运行它时，pl/sql 会提示输入 MENU_ID 值。
