简单的“选择”具有可变但不具有“INTO” [英] Simple "SELECT" with variable but without "INTO"

问题描述

我有一些不同的SELECT查询具有相同的值。我想使用类似DECLARE的东西，但是当我写一个简单的DECLARE它说INTO是期望的。

如果我只想一个选择使用此表单输入INTO？

只有两个（或多个）选择：

  SELECT * FROM my_table1 WHERE column1 = 5 and column2 = 6; 
  

和

  SELECT * FROM my_table2 WHERE col1 = 5 and col2 = 6; 
  

现在我想声明一个变量var_col1和var_col2，并在两个select查询中使用它们时间。

我认为这会奏效：

  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和col2 = var_col1; 
 / *和更多SELECT和var_col1和var_col2 * / 
 END; 
  

但没有机会...没有程序或函数，怎么办？当在SQL * Plus中写入 select * from some_table; 时，

解决方案

code> SQL * Plus 充当客户端程序，根据从数据库返回的数据，对您进行大量工作，格式化和显示

只要输入 DECLARE ，就会开始一个PL / SQL块。现在，您正在调用PL / SQL，而PL / SQL正在调用SQL。因此，您需要决定如何处理从SQL返回的数据（在PL / SQL中）。这样做的方法是通过 INTO 子句和一个接收输出的变量。考虑到如果不提供 INTO 子句， SELECT 的输出数据将放在哪里？它必须去某个地方，对吗？

希望很清楚。

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.

If I want only a "SELECT", how can I use this form witout "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.

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?

解决方案

When you write select * from some_table; in SQL*Plus, SQL*Plus is 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.

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 INTO clause and a variable to receive the output. Considering that, where would the output data from the SELECT go, if you don't provide an INTO clause? It has to go somewhere, right?

Hope that's clear.

这篇关于简单的“选择”具有可变但不具有“INTO”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！