自定义函数之返回结果集（以游标形式、Table形式(游标的For循环 或者 Bulk Collect)、管道形式返回结果集）

---

自定义函数之返回结果集

Oracle 中的函数可以返回表类型，但是这个表类型实际上是集合类型（与数组类似）。从 Oracle 9i 开始，提供了一个叫做"管道化表函数"来解决此问题。

管道化表函数，必须返回一个集合类型，且标明 pipelined。它不能返回具体变量，必须以一个空 return 返回，通过 pipe row () 语句来送出要返回的表中的每一行。调用这个函数的时候，通过 table() 关键字把管道流仿真为一个数据集。

以游标形式返回结果集

1）创建函数

create or replace function fn_GetEmpnoByCursor(p_ename in varchar2)
    return sys_refcursor 
is
    c_empno sys_refcursor;
begin
    open c_empno for
        select empno,ename from emp where ename=p_ename;
    return(c_empno);
end;

2）调用函数

select fn_GetEmpnoByCursor(p_ename => 'James') from dual;

以Table形式返回结果集（2种方式）

创建函数

1、使用Cursor的For循环，一条一条地处理

--定义一个行类型
create or replace type emp_row_type as object(empno varchar2(10),ename varchar2(20));
--定义一个表类型
create or replace type emp_table_type as table of emp_row_type;
--创建函数
create or replace function fn_GetEmpnoByTable (p_ename in varchar2)
    return emp_table_type 
is
    emp_row emp_row_type;                             --定义单行
    emp_table emp_table_type :=emp_table_type();    --定义返回结果，并初始化。
begin
    for currow in (select empno, ename from emp where ename = p_ename) loop
        emp_row := emp_row_type(currow.empno,currow.ename);
        emp_table.extend;    --
        emp_table(emp_table.count) := emp_row;    --
    end loop;
    return(emp_table);
end;

2、使用Bulk Collect可以将查询结果一次性地加载到Collections中，不是通过Cursor一条一条地处理

Bulk Collect 详细见：https://www.cnblogs.com/Ronger/archive/2011/09/22/2185574.html

--定义一个行类型
create or replace type emp_row_type as object(empno varchar2(10), ename varchar2(20));
--定义一个表类型
create or replace type emp_table_type as table of emp_row_type;
--创建函数
create or replace function fn_GetEmpnoByTable (p_ename in varchar2)
    return emp_table_type  
IS
	emp_table emp_table_type;    --定义返回结果
begin
	select emp_table_type (empno, ename)
	bulk collect
	into emp_table 
	from emp where ename = p_ename
	return emp_table ;
end;

调用函数

select * from table(fn_GetEmpnoByTable(p_ename => 'James'));

以管道形式返回结果集（2种方式）

创建函数

OBJECT TYPE + PIPE ROW

--定义一个行类型
create or replace type emp_row_type as object(empno varchar2(10),ename varchar2(20));
--定义一个表类型
create or replace type emp_table_type as table of emp_row_type;
--创建函数
create or replace function fn_GetEmpnoByPipe(p_ename in varchar2)
    return emp_table_type pipelined
is
    emp_row emp_row_type; --定义emp_row为行对象类型
begin
    for currow in (select empno,ename from emp where ename=p_ename) loop
        emp_row := emp_row_type(currow.empno,currow.ename);
        pipe row (emp_row);    -- 和以Table返回结果集的区别就几行
    end loop;
    return;
end;

RECORD TYPE + PIPE ROW

--定义一个行类型
type emp_row_type is record
(
	empno varchar2(10),
	ename varchar2(20)
)
--定义一个表类型
create or replace type emp_table_type as table of emp_row_type;
--创建函数
create or replace function fn_GetEmpnoByPipe(p_ename in varchar2)
    return emp_table_type pipelined
is
    emp_row emp_row_type; --定义emp_row为行对象类型
begin
    for currow in (select empno, ename from emp where ename = p_ename) loop
        emp_row.empno := currow.empno;    --
		emp_row.ename := currow.ename;    --
        pipe row (emp_row);
    end loop;
    return;
end;

调用函数

select * from table(fn_GetEmpnoByPipe(p_ename => 'James'));