------创建一个类型
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))
摘自 java小生
|