SQL 过程可以返回表吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13673502/
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
Can an SQL procedure return a table?
提问by Jaqualembo
Can an Oracle SQL procedure return a table? I'm currently using a dbms_outputto print out the outputs of two cursors which are in a loop, although this would look nicer if it was returning two columns instead. Would that be possible within a procedure?
Oracle SQL 过程可以返回表吗？我目前正在使用 adbms_output打印出循环中的两个游标的输出，尽管如果它返回两列，这看起来会更好。这在一个程序中是可能的吗？
回答by APC
A PL/SQL function can return a nested table. Provided we declare the nested table as a SQL type we can use it as the source of a query, using the the TABLE() function.
PL/SQL 函数可以返回嵌套表。如果我们将嵌套表声明为 SQL 类型，我们就可以使用TABLE() 函数将其用作查询源。
Here is a type, and a nested table built from it:
这是一个类型，以及一个由它构建的嵌套表：
SQL> create or replace type emp_dets as object (
2 empno number,
3 ename varchar2(30),
4 job varchar2(20));
5 /
Type created.
SQL> create or replace type emp_dets_nt as table of emp_dets;
2 /
Type created.
SQL>
Here is a function which returns that nested table ...
这是一个返回嵌套表的函数......
create or replace function get_emp_dets (p_dno in emp.deptno%type)
return emp_dets_nt
is
return_value emp_dets_nt;
begin
select emp_dets(empno, ename, job)
bulk collect into return_value
from emp
where deptno = p_dno;
return return_value;
end;
/
... and this is how it works:
...这是它的工作原理：
SQL> select *
2 from table(get_emp_dets(10))
3 /
EMPNO ENAME JOB
---------- ------------------------------ --------------------
7782 CLARK MANAGER
7839 KING PRESIDENT
7934 MILLER CLERK
SQL>
SQL Types offer us a great deal of functionality, and allow us to build quite sophisticated APIs in PL/SQL. Find out more.
SQL 类型为我们提供了大量功能，并允许我们在 PL/SQL 中构建非常复杂的 API。 了解更多。
回答by zygimantus
I think that you can use Oracle Cursor for this (if your Oracle version supports it):
我认为您可以为此使用 Oracle Cursor（如果您的 Oracle 版本支持它）：
PROCEDURE myprocedure(
mycursor OUT SYS_REFCURSOR )
AS
BEGIN
OPEN mycursor FOR SELECT * FROM mytable;
END;
END;
回答by Art
This may also help:
这也可能有帮助：
DECLARE
TYPE t_emptbl IS TABLE OF scott.emp%rowtype;
v_emptbl t_emptbl;
ret_val t_emptbl;
--
Function getEmployeeList Return t_emptbl
IS
BEGIN
SELECT * bulk collect INTO v_emptbl FROM scott.emp;
-- Print nested table of records:
FOR i IN 1 .. v_emptbl.COUNT LOOP
DBMS_OUTPUT.PUT_LINE (v_emptbl(i).empno);
END LOOP;
RETURN v_emptbl;
END;
--
BEGIN
ret_val:= getEmployeeList;
END;
/
