Oracle pipeline table functions

------ Create a type
 
Create or replace type whiteListType as object (
Spid number,
Spname varchar2 (200 ),
Spcode varchar2 (200 ),
Listname varchar2 (200 ),
Mdncount number
 
----- Create a reference
 
Create or replace type "WHITE_LIST_TYPE_ARRAY" IS array (10000) OF WHITLISTCOUNT
 
----- Create a 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;
 
 
 
 
 
 
 
---- Execution
 
Select * from table (GETWHITELIST (0 ))
From java Xiaosheng