oracle 带有变量但没有“INTO”的简单“SELECT”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9416727/
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
Simple "SELECT" with variable but without "INTO"
提问by Yasin Okumu?
I have some different SELECT queries with same values. I want to use something like DECLARE but when I write a simple DECLARE it says that "INTO" is expected.
我有一些具有相同值的不同 SELECT 查询。我想使用像 DECLARE 这样的东西，但是当我写一个简单的 DECLARE 时，它说“INTO”是预期的。
If I want only a "SELECT", how can I use this form witout "INTO"?
如果我只想要一个“SELECT”，我如何在没有“INTO”的情况下使用这个表格？
Simply I have two (or more) selects:
只是我有两个（或更多）选择：
SELECT * FROM my_table1 WHERE column1=5 and column2=6;
and
和
SELECT * FROM my_table2 WHERE col1=5 and col2=6;
Now I want to declare a variable like var_col1 and var_col2 and use them in both select queries at the same time.
现在我想声明一个像 var_col1 和 var_col2 这样的变量，并同时在两个选择查询中使用它们。
I thought this would work:
我认为这会奏效：
DECLARE
var_col1 number := 5;
var_vol2 number := 6;
BEGIN
SELECT * FROM my_table1 WHERE column1=var_col1 and column2=var_col2;
SELECT * FROM my_table2 WHERE col1=var_col1 and col2=var_col1;
/* and more SELECTs with var_col1 and var_col2 */
END;
But no chance... How is the way to do that without a procedure or function?
但是没有机会......没有过程或函数的情况下如何做到这一点？
采纳答案by Mark J. Bobak
When you write select * from some_table;in SQL*Plus, SQL*Plusis acting as the client program, and does a lot of work for you, under the covers, in terms of the data being returned from the database, formatting it and displaying it.
当您select * from some_table;使用 SQL*Plus编写时，SQL*Plus它充当客户端程序，在幕后为您完成大量工作，包括从数据库返回的数据、对其进行格式化和显示。
As soon as you type DECLARE, you begin a PL/SQL block. Now, You're calling PL/SQL, and PL/SQL is calling SQL. As a result, you need to decide how to handle the data being returned from the SQL, in PL/SQL. The way to do that, is via an INTOclause and a variable to receive the output. Considering that, where would the output data from the SELECTgo, if you don't provide an INTOclause? It has to go somewhere, right?
只要您键入DECLARE，就会开始一个 PL/SQL 块。现在，您正在调用 PL/SQL，而 PL/SQL 正在调用 SQL。因此，您需要决定如何在 PL/SQL 中处理从 SQL 返回的数据。这样做的方法是通过一个INTO子句和一个变量来接收输出。考虑到这一点，SELECT如果您不提供INTO子句，那么输出数据会在哪里？它必须去某个地方，对吧？
Hope that's clear.
希望这很清楚。
回答by northpole
You have to select into your declared variables if you want to do it that way or set the columns. For example:
如果你想这样做或设置列，你必须选择你声明的变量。例如：
DECLARE
var_col1 number := 5;
var_vol2 number := 6;
BEGIN
SELECT my_table1.var_col into var_col1
FROM my_table1
WHERE column1=var_col1
AND column2=var_col2;
--etc.....
END;
OR
或者
DECLARE
var_col1 number := 5;
var_vol2 number := 6;
BEGIN
SELECT 5 into var_col1
FROM my_table1
WHERE column1=var_col1
AND column2=var_col2;
--etc.....
END;
EDIT: I should also add that you can also declare output Vvariables that you can use in SQL to return output to help with debugging etc. So you can do something like:
编辑：我还应该补充一点，您还可以声明可以在 SQL 中使用的输出 Vvariables 来返回输出以帮助调试等。因此您可以执行以下操作：
DECLARE
out varchar2(200);
And in your BEGIN statement do something like:
并在您的 BEGIN 语句中执行以下操作：
dbms_output.put_line(out);
Which outputs some potential useful info (depending what you set out to).
它会输出一些潜在的有用信息（取决于您打算做什么）。
回答by APC
You're using PLSQL Developer, which has a choice of many different window types. If I recall correctly, the Command window is the one which is a SQL*Plus emulator. That being so this should work:
您正在使用 PLSQL Developer，它可以选择许多不同的窗口类型。如果我没记错的话，命令窗口是一个 SQL*Plus 模拟器。既然如此，这应该有效：
var n1 number
var n2 number
exec &&n1 := 5
exec &&n2 := 6
SELECT * FROM my_table1 WHERE column1=&&n1 and column2=&&n2 ;
SELECT * FROM my_table2 WHERE col1&&n1 and col2=&&n2;
In other words, define two substitution variables, assign values to them and finally deploy them in all your queries.
换句话说，定义两个替换变量，为它们赋值，最后在所有查询中部署它们。
