如何在 Oracle PL-SQL 中的 select 语句中使用变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40610932/
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 a select statement in Oracle PL-SQL
提问by John Doe
I have a simple query that I can run in toad.
我有一个可以在 toad 中运行的简单查询。
select * from my_table
where my_id = 11111;
Why can't I run the same query from toad this time declaring a variable and using it in the where clause?
为什么这次我不能从 toad 运行相同的查询来声明一个变量并在 where 子句中使用它？
In sql server I would:
在 sql server 中，我会：
declare @testID int
set @testID = 11111
select * from my_table
where my_id = @testID;
How can I accomplish the same in Oracle 11g?
如何在 Oracle 11g 中完成相同的操作？
回答by Tony Andrews
In Toad (or SQL Developer) you can do this:
在 Toad（或 SQL Developer）中，您可以这样做：
select * from my_table
where my_id = :testID;
When you run it, you will be prompted to enter a value for the testId bind variable.
运行它时，系统会提示您输入 testId 绑定变量的值。
回答by XING
PLSQL is different than SQL SERVER. It has its own syntax. See how you can do it as below:
PLSQL 不同于 SQL SERVER。它有自己的语法。看看你如何做到这一点，如下所示：
DECLARE
var NUMBER := 1;
var2 my_table%ROWTYPE;
BEGIN
SELECT *
INTO var2
FROM my_table
WHERE my_id = var;
--To display result you need to add dbsm_output.put_line function.
dbms_output.put_line(var2.<columnname>);
Exception
When others then
Null;
END;
Note: Assumption is that the query wil return a single row only.
注意：假设查询将仅返回单行。
