失樂(lè)園

CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)


           RETURN ty_str_split PIPELINED


IS


           j INT := 0;


    i INT := 1;


    len INT := 0;


    len1 INT := 0;


    str VARCHAR2 (4000);


BEGIN


           len := LENGTH (p_str);


    len1 := LENGTH (p_delimiter);





           WHILE j < len


           LOOP


               j := INSTR (p_str, p_delimiter, i);





               IF j = 0


               THEN


                   j := len;


            str := SUBSTR (p_str, i);


            PIPE ROW (str);





                   IF i >= len


                   THEN


                EXIT;


            END IF;


               ELSE


            str := SUBSTR (p_str, i, j - i);


            i := j + len1;


            PIPE ROW (str);


        END IF;


    END LOOP;





           RETURN;


END fn_split;

SELECT * FROM TABLE (fn_split ('INC1316086732328,INC1316086737562,INC1316083148750,INC1316072987015,INC1316053713729', ','))