如何从存储过程返回多行？（Oracle PL/SQL）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/101033/
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 return multiple rows from the stored procedure? (Oracle PL/SQL)
提问by Tony Andrews
I want to create a stored procedure with one argument which will return different sets of records depending on the argument. What is the way to do this? Can I call it from plain SQL?
我想创建一个带有一个参数的存储过程，它将根据参数返回不同的记录集。有什么方法可以做到这一点？我可以从普通 SQL 调用它吗？
回答by Tony Andrews
Here is how to build a function that returns a result set that can be queried as if it were a table:
以下是如何构建一个函数，该函数返回一个可以像表一样查询的结果集：
SQL> create type emp_obj is object (empno number, ename varchar2(10));
2 /
Type created.
SQL> create type emp_tab is table of emp_obj;
2 /
Type created.
SQL> create or replace function all_emps return emp_tab
2 is
3 l_emp_tab emp_tab := emp_tab();
4 n integer := 0;
5 begin
6 for r in (select empno, ename from emp)
7 loop
8 l_emp_tab.extend;
9 n := n + 1;
10 l_emp_tab(n) := emp_obj(r.empno, r.ename);
11 end loop;
12 return l_emp_tab;
13 end;
14 /
Function created.
SQL> select * from table (all_emps);
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7902 FORD
7934 MILLER
回答by Thilo
I think you want to return a REFCURSOR:
我想你想返回一个REFCURSOR：
create function test_cursor
return sys_refcursor
is
c_result sys_refcursor;
begin
open c_result for
select * from dual;
return c_result;
end;
Update: If you need to call this from SQL, use a table function like @Tony Andrews suggested.
更新：如果您需要从 SQL 调用它，请使用@Tony Andrews 建议的表函数。
回答by Mohsen Heydari
You may use Oracle pipelined functions
您可以使用 Oracle 流水线函数
Basically, when you would like a PLSQL (or java or c) routine to be the ?source? of data -- instead of a table -- you would use a pipelined function.
基本上，当您希望 PLSQL（或 java 或 c）例程作为 ?source? 时？数据 - 而不是表 - 您将使用流水线函数。
Simple Example - Generating Some Random Data
How could you create N unique random numbers depending on the input argument?
简单示例 - 生成一些随机数据
如何根据输入参数创建 N 个唯一的随机数？
create type array
as table of number;
create function gen_numbers(n in number default null)
return array
PIPELINED
as
begin
for i in 1 .. nvl(n,999999999)
loop
pipe row(i);
end loop;
return;
end;
Suppose we needed three rows for something. We can now do that in one of two ways:
假设我们需要三行来做某事。我们现在可以通过以下两种方式之一来做到这一点：
select * from TABLE(gen_numbers(3));
COLUMN_VALUE
COLUMN_VALUE
1
2
3
or
或者
select * from TABLE(gen_numbers)
where rownum <= 3;
COLUMN_VALUE
COLUMN_VALUE
1
2
3
回答by John Smithers
If you want to use it in plain SQL, I would let the store procedure fill a table or temp table with the resulting rows (or go for @Tony Andrews approach).
If you want to use @Thilo's solution, you have to loop the cursor using PL/SQL.
Here an example: (I used a procedure instead of a function, like @Thilo did)
如果您想在纯 SQL 中使用它，我会让存储过程用结果行填充表或临时表（或采用 @Tony Andrews 方法）。
如果要使用@Thilo 的解决方案，则必须使用 PL/SQL 循环游标。这是一个例子：（我使用了一个过程而不是一个函数，就像@Thilo 那样）
create or replace procedure myprocedure(retval in out sys_refcursor) is
begin
open retval for
select TABLE_NAME from user_tables;
end myprocedure;
declare
myrefcur sys_refcursor;
tablename user_tables.TABLE_NAME%type;
begin
myprocedure(myrefcur);
loop
fetch myrefcur into tablename;
exit when myrefcur%notfound;
dbms_output.put_line(tablename);
end loop;
close myrefcur;
end;
回答by S. Mayol
create procedure <procedure_name>(p_cur out sys_refcursor) as begin open p_cur for select * from <table_name> end;
