Oracle学习笔记二十四：自定义函数之返回结果集

一、基本说明

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

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

二、常见形式

2.1、以游标形式返回结果集

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;

2.2、以Table形式返回结果集

1）创建函数

--定义一个行类型
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）调用函数

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

2.3、以管道形式返回结果集

1）创建函数

--定义一个行类型
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);
    end loop;
    return;
end;

2）调用函数

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