oracle 管道表函数

 

 

------创建一个类型

create or replace type whiteListType  as object(
   spid  number ,
   spname varchar2(200),
   spcode varchar2(200),
   listname varchar2(200),
   mdncount number

-----创建一个引用

CREATE OR REPLACE TYPE "WHITE_LIST_TYPE_ARRAY"                                                                           IS array(10000) OF WHITLISTCOUNT

-----创建function

create or replace function GETWHITELIST(
spid  in number
)
return whitelistArray pipelined
is
  v_whiteListType whiteListType ;
  strsql      varchar2(1000);
 -- spinfos     whitelistArray;
  rs          SYS_REFCURSOR;
  id          number;
  sp_name     varchar2(200);
  sp_code     varchar2(200);
  sp_status   number;
  sp_type     number;
  mdn_count   number;
begin

  ------------------------??????-----------------------------
  -- ??????? ??? id
  strsql := 'select  id, sp_code, sp_name ,sp_type  ,sp_status  from NM_SP_INFO where id != 0  ';
  if spid >0  then
  strsql := strsql||' and id = '''||spid||'''';
  end if ;
  strsql := strsql || ' order by id';
  -- ???? ?????????
  open rs for strsql;
  loop
    fetch rs
      into id, sp_name, sp_code,sp_type ,sp_status;
    exit when rs%NOTFOUND;
    strsql := 'select count(mdn) from NM_NET_USER_' || id || ' where 1=1 and status <>4';
    execute immediate strsql into mdn_count;
      if mdn_count > 0 then
      v_whiteListType :=whiteListType(id , sp_name , sp_code , '',mdn_count );

      pipe row(v_whiteListType) ;----
    end if ;
  end loop;
  close rs ;
  return ;
end GETWHITELIST;

 

 

 

----执行

select * from table(GETWHITELIST(0))