将返回从选择查询中检索到的数据的函数 - Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4749650/
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
Function that would return the data retrieved from a select query - Oracle
提问by macha
I am trying to write a function that would return the result of a select query. I have worked with very basic functions that would return a number and a varchar2(string). But now I want to return the result of a select, which would be like 10 rows and their corresponding columns.
我正在尝试编写一个函数来返回选择查询的结果。我使用过非常基本的函数，这些函数会返回一个数字和一个 varchar2(string)。但现在我想返回一个选择的结果，就像 10 行和它们对应的列。
How would I write the function and what would the return type be?
我将如何编写函数以及返回类型是什么？
An example function that I have written is:
我编写的一个示例函数是：
create or replace function func1 return varchar2 as begin return('hello from func1'); end func1;
创建或替换函数func1 return varchar2 as begin return('hello from func1'); 结束函数1;
I am still at a basic level, so can anybody help me out with returning the result of a select query? I believe cursors are to be used, as there would be more than one row.
我仍然处于基本级别，所以有人可以帮助我返回选择查询的结果吗？我相信要使用游标，因为会有不止一行。
回答by Justin Cave
Normally, a function returns a single "thing". Normally, that is a scalar (a number, a varchar2, a record, etc) though you can return a collection. So, for example, you could return a collection (in this case a nested table) with all the EMPNO values from the EMP table
通常，函数返回单个“事物”。通常，这是一个标量（数字、varchar2、记录等），尽管您可以返回一个集合。因此，例如，您可以返回一个集合（在本例中为嵌套表），其中包含 EMP 表中的所有 EMPNO 值
CREATE TYPE empno_tbl
IS TABLE OF NUMBER;
CREATE OR REPLACE FUNCTION get_empnos
RETURN empno_tbl
IS
l_empnos empno_tbl;
BEGIN
SELECT empno
BULK COLLECT INTO l_empnos
FROM emp;
RETURN l_empnos;
END;
But this isn't a particularly common thing to do in a function. It would be a bit more common to have the function return a cursor rather than returning values and to let the caller handle fetching the data, i.e.
但这在函数中并不是特别常见的事情。让函数返回游标而不是返回值并让调用者处理获取数据会更常见，即
CREATE OR REPLACE FUNCTION get_empnos2
RETURN SYS_REFCURSOR
IS
l_rc SYS_REFCURSOR;
BEGIN
OPEN l_rc
FOR SELECT empno
FROM emp;
RETURN l_rc;
END;
But even that isn't particularly common in Oracle. Depending on what you're trying to accomplish, it would generally be more common to simply create a view that selected the data you were interested in and to query that view rather than calling a function or procedure.
但即便如此，这在 Oracle 中也不是特别常见。根据您要完成的任务，通常更常见的是简单地创建一个选择您感兴趣的数据的视图并查询该视图，而不是调用函数或过程。
回答by Thomas Barker
Well, if you're just learning, you should know about pipelined functions. A pipelined function lets you return dynamically generated tables within PLSQL.
好吧，如果您只是在学习，您应该了解流水线函数。流水线函数允许您在 PLSQL 中返回动态生成的表。
For example...
例如...
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;
Which I borrowed from http://www.akadia.com/services/ora_pipe_functions.html:-)
我从http://www.akadia.com/services/ora_pipe_functions.html借来的:-)
回答by buzzwang
Without context of how you would be calling this function, I'm a little lost on exactly how to help you.
如果没有你将如何调用这个函数的上下文，我对如何帮助你有点迷茫。
Are you sure you wouldn't be better off with a subselect, join, or view instead?
您确定使用子选择、加入或查看不会更好吗？
