In the real application,in order to make the PL/SQL function returns the data in multiple rows ,must by returning a REF CURSOR or a data set to complete .REF CURSOR this is limited to selected data from the query ,and the entire collection at can return to before ,must be specific .
9i through the introduction of the pipelined table to correct the latter case .Table function is to return the row set ( usually as a set ) function ,womens supra shoes,can be directly from the SQL statement in the query,hackett precios, as if it were a real table .
Pipelined table similar to ,but it in construction as the return data ,rather than a full return .Pipelined table is more effective, because the data can be back as soon as possible .The pipeline of table function must return a collection .
In the function, the ROW statement is used to return the collection of individual elements, the function must be to a free end of the RETURN statement ,to indicate that it has completed .
Once we have created the function ,we can use TABLE SQL query operators from using it .Pipelined table is often used to move data from one type to another type .The following is the Pipelined Table split CREATE OR REPLACE function examples :TYPE ty_str_split IS TABLE OF VARCHAR2 ( 4000 ) ;CREATE OR REPLACE FUNCTION fn_split ( p_str IN VARCHAR2 ,p_delimiter IN VARCHAR2 RETURN ty_str_split PIPELINED IS J INT ) :I = 0 ,ralph lauren socks;INT = 1 ;len :INT :INT :len1 = = 0 ;0 ;STR VARCHAR2 ( 4000 ) :len ;BEGIN = LENGTH ,hermes kelly bag;len1 = ( p_str ) :LENGTH ( p_delimiter ) ;WHILE J = len THEN EXIT ;END IF ;ELSE = STR :SUBSTR ( p_str , s my job i} J high up the way tall high as} s my job i} ) ;I = j + :len1 ;PIPE ROW ( STR ) ;END IF ;END LOOP ;RETURN ;END fn_split ;/ test :SELECT * FROM TABLE ( fn_split ( 1 ;;;12 ;123 ,christian audigier shirts;1234 ;12345 , ;;;;results :112123123412345 .
) ) ;and a simple example :CREATE TYPE mytype AS OBJECT ( field1 NUMBER ,field2 VARCHAR2 ( 50) ) ;CREATE TYPE mytypelist AS TABLE OF mytype ;CREATE OR REPLACE FUNCTION pipelineme RETURN mytypelist PIPELINED IS v_mytype mytype ;BEGIN FOR v_count IN 1 .
20 LOOP v_mytype := mytype ( v_count ,Row v_count ) ;PIPE ROW ( v_mytype ) ;END LOOP END ;RETURN ,fred perry london;pipelineme ;SELECT * FROM TABLE ( pipelineme ) ;FIELD1 FIELD2 1 Row 12 Row
23 ------------------------------ Row 34 Row 45 Row 56 Row 67 Row 78 Row 89 Row 910 Row 1011 Row 1112 Row 1213 Row 1314 Row 1415 Row 1516 Row 1617 Row 1718 Row 1819 Row 1920 Row 2020
相关的主题文章：

• http://fagui.ceo360.net/plus/view-350037-1.html
• http://home.mychangdao.cn/space.php?uid=9722&do=blog&id=1647846