管道表函数

CREATE OR REPLACE TYPE "ORG_TYPE"       as object
(orgid varchar2(50),

 orgsort number)

CREATE OR REPLACE TYPE "ORG_TABLE"     as table of Org_type

create or replace function orgtable(orgids in varchar2)
  return Org_table
  pipelined is
  v_str     varchar2(30000) := orgids || ',';
  v_org     varchar2(30);
  v_org_end pls_integer;
  inti      number;
begin
  inti := 0;
  loop
    v_org_end := instrb(v_str, ',');
    exit when(v_org_end = 0 or v_str is null);
    inti  := inti + 1;
    v_org := substrb(v_str, 1, v_org_end - 1);
    v_str := ltrim(v_str, v_org);
    v_str := ltrim(v_str, ',');
    pipe row(Org_type(rtrim(v_org, ';'), rtrim(inti)));
  end loop;
  return;
end;

select * from table(orgtable(?))