oracle 我如何在oracle过程中运行多个select语句

how do i run multiple select statements in oracle procedure

提问by Gaurav Sharma

I have written the following procedure

我写了以下程序

create or replace procedure sp_abc_profile 
(
  f_symbol_in abc.colname%TYPE
)
is profile abc%rowtype;
is profile2 abc2%rowtype;
begin
  SELECT fname, lname,mname,age
    INTO profile
  FROM abc
  WHERE f_symbol = f_symbol_in;

  SELECT initiaiinvestment AS minInitialInvestment, pr as qt, class1 as clss
    into profile2
  FROM 
      abc2 
  WHERE f_symbol = f_symbol_in;
end;

Upon executing the above I get error message as follows:

执行上述操作后，我收到如下错误消息：

Error(7,3): PL/SQL: SQL Statement ignored

Error(21,5): PL/SQL:ORA-00913: too many values

错误（7,3）：PL/SQL：SQL 语句被忽略

错误 (21,5): PL/SQL:ORA-00913: 值太多

I don't want to select all the rows in both tables.

我不想选择两个表中的所有行。

How can I write multiple select statements in a procedure, so that every single select statement in procedure returns a resultset.

如何在一个过程中编写多个选择语句，以便过程中的每个选择语句都返回一个结果集。

回答by Donato Szilagyi

Try the following:

请尝试以下操作：

is
profile abc.fname%type;
profile2 abc2.initiaiinvestment%type;

There is no problem with having multiple select statements in the procedure. It is about a mismatch of the selected columns and the PL/SQL type.

在过程中有多个选择语句没有问题。这是关于所选列和 PL/SQL 类型不匹配的问题。

Beside that you seem to have too many isin your code.

除此之外，您is的代码中似乎有太多内容。

For more info about using select into, check this link: Oracle PL/SQL "select into" clause

有关使用的更多信息select into，请查看此链接：Oracle PL/SQL“select into”子句

回答by user2001117

Try this we can have multiple select statement inside the procedure:

试试这个，我们可以在过程中有多个 select 语句：

There was issue of too many value in your code.

您的代码中存在太多价值的问题。

create or replace procedure sp_abc_profile 
(
  f_symbol_in abc.colname%TYPE
)
is profile abc.fname%type;
is profile2 abc2.initiaiinvestment%type;
begin
  SELECT fname
    INTO profile
  FROM abc
  WHERE f_symbol = f_symbol_in;

  SELECT initiaiinvestment into profile2
  FROM 
      abc2 
  WHERE f_symbol = f_symbol_in;
end;

回答by Jason

As others have pointed out you have too many "is" statements.

正如其他人指出的那样，您有太多的“是”语句。

When performing a select into a row variable, you need to select everything:

在对行变量执行选择时，您需要选择所有内容：

select *
into profile
from abc
where f_symbol = f_symbol_in;

select *
into profile2
from abc2
where f_symbol = f_symbol_in;

You are also running the risk of throwing an exception when you have multiple matches on f_symbol_in. Your code could catch this exception, or you can restrict the number of rows (i.e. and rownum<=1) or alternatively look at collections to load all rows matching the parameter.

当您在 f_symbol_in 上有多个匹配项时，您也面临着抛出异常的风险。您的代码可以捕获此异常，或者您可以限制行数（即和 rownum<=1），或者查看集合以加载与参数匹配的所有行。

回答by Gaurav Sharma

The solutions is to use CURSORS in oracle so that every single select statement in procedure returns a resultset.

解决方案是在oracle 中使用CURSORS，以便procedure 中的每个select 语句都返回一个结果集。

That resultset can then be traversed in your preferred scripting language for desired output.

然后可以在您首选的脚本语言中遍历该结果集以获得所需的输出。

create or replace 
procedure sp_abc_profile
(
  symbol_in in tablename.fieldname%type,
  cursor1 out SYS_REFCURSOR,
  cursor2 out SYS_REFCURSOR,
)
as
begin


    open cursor1 for
         {your select statement here}


    open cursor2 for
        {your second select statement here}


end sp_abc_profile;

回答by the_slk

1. Remove redundant "IS"
2. Add: ROWNUM = 1

1. 删除多余的“IS”
2. 添加：ROWNUM = 1

create or replace procedure sp_abc_profile 
(
  f_symbol_in abc.colname%TYPE
)
is profile abc%rowtype;
/*is*/ profile2 abc2%rowtype;
begin
  SELECT fname, lname,mname,age
    INTO profile
  FROM abc
  WHERE f_symbol = f_symbol_in
  AND ROWNUM = 1;

  SELECT initiaiinvestment AS minInitialInvestment, pr as qt, class1 as clss
    into profile2
  FROM 
      abc2 
  WHERE f_symbol = f_symbol_in
  AND ROWNUM = 1;
end;

create or replace procedure sp_abc_profile 
(
  f_symbol_in abc.colname%TYPE
)
is profile abc%rowtype;
/*is*/ profile2 abc2%rowtype;
begin
  SELECT fname, lname,mname,age
    INTO profile
  FROM abc
  WHERE f_symbol = f_symbol_in
  AND ROWNUM = 1;

  SELECT initiaiinvestment AS minInitialInvestment, pr as qt, class1 as clss
    into profile2
  FROM 
      abc2 
  WHERE f_symbol = f_symbol_in
  AND ROWNUM = 1;
end;