执行立即选择不返回任何值 [英] Execute immediate select returns no values

问题描述

我有选择语句

select name, surname
from student
where id_student = 1;

返回

name surname
Bob Smith

我想使用立即执行创建具有相同选择语句的过程:

I want to create procedure with same select statement, using execute immediate:

create or replace procedure select_procedure
as
begin
execute immediate
'select name, surname
from student
where id_student = 1';
end;
/
exec select_procedure;

执行此过程时，它显示PL/SQL过程已成功完成.我如何得到结果? (将serveroutput设置为on)

When this procedure is executed it shows PL/SQL procedure successfully completed. How do I get the result? (set serveroutput is on)

推荐答案

您必须选择 into .如果您不这样做，则查询

You have to select into something. If you don't then the query isn't even executed (though it is parsed).

create or replace procedure select_procedure
as
  l_name student.name%TYPE;
  l_surname student.name%TYPE;
begin
  execute immediate
  'select name, surname
  from student
  where id_student = 1'
  into l_name, l_surname;
end;
/

但是，没有特别的顺序:(a)您应该使用绑定变量，而不要在动态语句中嵌入文字值1； (b)这根本不需要是动态的；和(c)调用者无论如何都不会看到查询返回的值-除非您改为选择OUT自变量，或使用dbms_output()显示它们(尽管实际上，这仅应在您调试时使用)无法控制客户端是否将其显示.)

But, in no particular order: (a) you should use bind variables instead of having the literal value 1 embedded in the dynamic statement; (b) this doesn't need to be dynamic at all; and (c) the caller won't be able to see the values returned by the query anyway - unless you select into OUT arguments instead, or display them with dbms_output() (although that should really only be used for debugging as you can't control whether the client will show it).

所以您可以这样做:

create or replace procedure select_procedure
as
  l_name student.name%TYPE;
  l_surname student.name%TYPE;
begin
  select name, surname
  into l_name, l_surname
  from student
  where id_student = 1;

  dbms_output.put_line('name=' || l_name ||', surname=' || l_surname);
end;
/

或

create or replace procedure select_procedure (
  p_name OUT student.name%TYPE,
  p_surname OUT student.name%TYPE
)
as
begin
  select name, surname
  into p_name, p_surname
  from student
  where id_student = 1;
end;
/

，并让您的调用者传入其自己的变量名以进行填充，然后使用这些变量名执行所需的任何操作.呼叫者通常还会传递您要查找的ID，因此您无需输入1的硬编码.

and have your caller pass in its own variable names to populate, and then do whatever it needs with those. The caller would usually also pass in the ID you're looking for, so you don't have the 1 hard-coded.

看来，过程似乎并不是实现此目的的最佳机制.

It doesn't seem like a procedure is really the best mechanism for this though.

此外，如果查询返回零行或多于一行，则使用select ... into(静态或动态)也会出错.仅当返回准确的一行时，它才起作用.游标可以处理任意数量的行-但是除非您只是打印结果(如@Jayanth所示)，否则您需要将游标传递回调用方.您可以改用bulk collect into集合，但是您仍然必须对此做些事情.

Also, using a select ... into (static or dynamic) will error if the query returns zero rows or more than one row. It will only work if there is exactly one row returned. A cursor would handle any number of rows - but unless you are just printing the results (as @Jayanth shows) you need to pass the cursor back to the caller instead. You could do a bulk collect into a collection instead, but you still have to do something with that.

这篇关于执行立即选择不返回任何值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！