CREATE OR REPLACE FUNCTION splitstr(p_string IN clob, p_delimiter IN VARCHAR2:=',')

RETURN str_split PIPELINED

/*劈分字符串,返回结果集

 使用 select COLUMN_VALUE from table(splitstr('34324,65,gfds,yt,,')); --默认分隔符

 使用 select COLUMN_VALUE from table(splitstr('34324,65,gfds,yt,,',','));

 使用遍历 for tbl in (select * from table(splitstr('123,567,9'))) loop ... end loop;

*/

AS

  v_length NUMBER := LENGTH(p_string);

  v_start  NUMBER := 1;

  v_index  NUMBER;

  v_chars varchar(4000);--限制每行长度,方便使用

BEGIN

  WHILE(v_start <= v_length) LOOP

    v_index := INSTR(p_string, p_delimiter, v_start);

    IF v_index = 0 THEN

      v_chars:=substr(SUBSTR(p_string, v_start),1,4000);

      PIPE ROW(v_chars);

      v_start := v_length + 1;

    ELSE

      v_chars:=substr(SUBSTR(p_string, v_start, v_index - v_start),1,4000);

      PIPE ROW(v_chars);

      v_start := v_index + 1;

    END IF;

  END LOOP;

  RETURN;

END splitstr;