Oracle管道函数

Oracle管道函数是一类特殊的函数，关键字pipelined表明这是一个Oracle管道函数，Oracle管道函数的返回值类型必须为集合，在函数中，pipe row语句被用来返回该集合的单个元素，函数以一个空的return语句结束，以表明它已经完成。一旦我们创建了上述函数，我们就可以使用table操作符从SQL查询中调用它，从而使Oracle函数返回table集合。

例1：使用隐式游标 for .. in
-------------------------------------------------------------------
create type f_emp_row_type as object
(
  empno NUMBER(4),
  ename VARCHAR2(10),
  sal   NUMBER(7,2),
  job   VARCHAR2(9)
);
/

create type f_emp_tab_type as table of f_emp_row_type;
/

create or replace function f_emp_test return f_emp_tab_type
  pipelined as
  emp_rows f_emp_row_type;
begin
  for tt in (select empno,ename,sal,job from emp) loop
    emp_rows := f_emp_row_type(tt.empno, tt.ename, tt.sal, tt.job);
    pipe row(emp_rows);
  end loop;
  return;
end;
/

select * from table(f_emp_test);
-------------------------------------------------------------------

带有参数的管道函数
------------------------------------------------------------------------
create or replace function f_emp_test1(dno number) return f_emp_tab_type
  pipelined as
  emp_tables f_emp_row_type;
begin
  for tt in (select empno,ename,sal,job from emp where deptno=dno) loop
    emp_tables := f_emp_row_type(tt.empno, tt.ename, tt.sal, tt.job);
    pipe row(emp_tables);
  end loop;
  return;
end;
/

select * from table(f_emp_test1(10));
------------------------------------------------------------------------

例2：使用集合(要使用包)
------------------------------------------------------------------------
create or replace package test_pkg2 as
  type emp_row_type is record(
    empno NUMBER(4),
    ename VARCHAR2(10),
    sal   NUMBER(7, 2),
    job   VARCHAR2(9));
  type emp_tab_type is table of emp_row_type;

  function f_test_emp return emp_tab_type
    pipelined;
end test_pkg2;
/

create or replace package body test_pkg2 as
  function f_test_emp return emp_tab_type
    pipelined is
    emp_rows      emp_row_type;
    emp_rows_list emp_tab_type;
  begin
    select empno,ename,sal,job bulk collect into emp_rows_list from emp;
    for i in 1 .. emp_rows_list.count loop
      emp_rows := emp_rows_list(i);
      pipe row(emp_rows);
    end loop;
    return;
  end;
end test_pkg2;
/

select * from table(test_pkg2.f_test_emp);
------------------------------------------------------------------------

例3：使用游标
------------------------------------------------------------------------
create or replace type test_user_record as object
(
  test_id   number,
  test_name varchar2(20),
  test_sal  number,
  test_job  varchar2(20)
);
/

create or replace type test_user_table as table of test_user_record;
/

create or replace package test_user_proc is
  function select_user return test_user_table
    pipelined;
end test_user_proc;
/

create or replace package body test_user_proc is
  function select_user return test_user_table
    pipelined as
    pragma autonomous_transaction;
    type t_cursor is ref cursor;
    r           t_cursor;
    v_sql       varchar2(3000);
    v_test_id   number;
    v_test_name varchar2(20);
    v_test_sal  number;
    v_test_job  varchar2(20);
  begin
    v_sql := 'select empno,ename,sal,job from emp';
    open r for v_sql;
    loop
      fetch r
        into v_test_id, v_test_name, v_test_sal, v_test_job;
      exit when r%notfound;
      pipe row(test_user_record(v_test_id,
                                v_test_name,
                                v_test_sal,
                                v_test_job));
    end loop;
    close r;
    return;
  end;
end test_user_proc;
/

select * from table(test_user_proc.select_user);
------------------------------------------------------------------------

例4：Oracle 中使用 fetch bulk collect into 批量效率的读取游标数据
------------------------------------------------------------------------
create or replace package test_pkg1 as
  type emp_row_type is record(
    empno NUMBER(4),
    ename VARCHAR2(10),
    sal   NUMBER(7, 2),
    job   VARCHAR2(9));
  type emp_tab_type is table of emp_row_type;

  function f_test_emp return emp_tab_type
    pipelined;
end test_pkg1;
/

create or replace package body test_pkg1 as
  function f_test_emp return emp_tab_type
    pipelined is
    emp_rows      emp_row_type;
    emp_rows_list emp_tab_type;
    cursor cur_emp is
      select empno, ename, sal, job from emp;
  begin
    open cur_emp;
    fetch cur_emp bulk collect
      into emp_rows_list;
    for i in 1 .. emp_rows_list.count loop
      emp_rows.empno := emp_rows_list(i).empno;
      emp_rows.ename := emp_rows_list(i).ename;
      emp_rows.sal   := emp_rows_list(i).sal;
      emp_rows.job   := emp_rows_list(i).job;
      pipe row(emp_rows);
    end loop;
    close cur_emp;
    return;
  end;
end test_pkg1;
/

select * from table(test_pkg1.f_test_emp);
------------------------------------------------------------------------

例5：
------------------------------------------------------------------------
create or replace type ty_obj_user as object
(
  id   number(20),
  name varchar2(60)
);
/

create or replace package pkg1 as
  type ty_rec_user is record(
    id   number(20),
    name varchar2(60));
   
  type tab_rec_user is table of ty_rec_user;

  function f1(x number) return tab_rec_user
    pipelined;

  type tab_obj_user is table of ty_obj_user;
  function f_pie_test(c number) return tab_obj_user
    pipelined;
end pkg1;
/

create or replace package body pkg1 as
  function f1(x number) return tab_rec_user
    pipelined is
    user_rec ty_rec_user;
  begin
    for i in 1 .. x loop
      user_rec.id   := i;
      user_rec.name := 'user' || i;
      pipe row(user_rec);
    end loop;
    return;
  end;

  function f_pie_test(c number) return tab_obj_user
    pipelined is
    user_obj ty_obj_user;
  begin
    for i in 1 .. c loop
      user_obj := ty_obj_user(i, 'name' || i);
      pipe row(user_obj);
    end loop;
    return;
  end;
end pkg1;
/

select * from table(pkg1.f1(4));
select * from table(pkg1.f_pie_test(4));
select * from table(cast(pkg1.f1(4) as tab_rec_set)); 

------------------------------------------------------------------------