Oracle implements the Split function function

Reprint: http://blog.csdn.net/jojo52013145/article/details/6758279
In an actual application, in order for the PL/SQL function to return multiple rows of data, it must be done by returning a REF CURSOR or a . This case of REF CURSOR is limited to the data that can be selected from the query, and the entire collection must be materialized before it can be returned. 9i corrects the latter case by introducing a pipelined table function. A table function is a function that returns the entire set of rows (usually as a collection), which can be queried directly from an SQL statement as if it were a real database table. The pipelined table function is similar, but it returns the data as it was built, rather than returning it all at once. pipelining table functions are more efficient because the data can be returned as quickly as possible.

The pipelined table function must return a collection. In the function, the PIPE ROW statement is used to return a single element of the collection, and the function must end with an empty return statement to indicate that it has completed. Once we have created the above function, we can invoke it from the SQL query using the TABLE operator.

1. Define a type of object
CREATE OR replace TYPE ty_obj as OBJECT (str1 VARCHAR2 (50));
2. Define a type that inherits the Ty_obj;
CREATE OR replace TYPE Strsplit_type as TABLE of Ty_obj;
--drop TYPE Strsplit_type;
--drop TYPE ty_obj;
3. Test case: Implementing the Split Function function

 Create or Replace functionStrsplit (P_valuevarchar2, P_splitvarchar2:= ',')--Usage:select * FROM table (strsplit (' 1,2,3,4,5 ')) RETURNStrsplit_type pipelined isV_idxinteger; V_strvarchar2( -); V_strs_lastvarchar2(4000) :=P_value;beginLoop V_idx:=InStr (V_strs_last, P_split); Exit  whenV_idx= 0; V_STR:=SUBSTR (V_strs_last,1, V_idx- 1); V_strs_last:=substr (V_strs_last, V_idx+ 1); PipeRow (Ty_obj (V_STR)); EndLoop; PipeRow (Ty_obj (v_strs_last)); RETURN;EndStrsplit;

Oracle implements the Split function function