Oracle：在函数中返回多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22460509/
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
Oracle: Returning multiple rows in a function
提问by Kerri Williams
I am trying to create a function which would return multiple rows.
我正在尝试创建一个返回多行的函数。
Following is my function and type
以下是我的功能和类型
create or replace type emp_type
(
first_name varchar2(20)
, last_name varchar2(20)
, depart_name varchar2(20)
)
/
create or replace function get_employee
(loc in number)
return emp_type
as
emp_record emp_type;
begin
select a.first_name, a.last_name, b.department_name into emp_record.first_name,
emp_record.last_name,emp_record.depart_name
from employees a, departments b
where a.department_id=b.department_id and location_id=loc;
return(emp_record);
end;
And I used
我用过
select get_employee(5) from dual;
I am getting "exact fetch returns more than requested number of rows" error.
Later when I used rownum<2in the select query I got "Reference to uninitialized composite".
我收到“ exact fetch returns more than requested number of rows”错误。后来当我rownum<2在选择查询中使用时，我得到了“ Reference to uninitialized composite”。
Could you please help?
能否请你帮忙？
Thanks in Advance
提前致谢
回答by Justin Cave
If you want to return a sys_refcursor, there is no reason to declare the object type or to try to return an object type. Just return a sys_refcursor.
如果要返回 a sys_refcursor，则没有理由声明对象类型或尝试返回对象类型。只需返回一个sys_refcursor.
create or replace function get_employee
(p_loc in number)
return sys_refcursor
as
l_rc sys_refcursor;
begin
open l_rc
for select a.first_name, a.last_name, b.department_name
from employees a,
departments b
where a.department_id=b.department_id
and location_id=p_loc;
return l_rc;
end;
