oracle自定义split分割函数

函数如下：

create or replace FUNCTION fn_rme_split(p_str       IN VARCHAR2,
                                        p_delimiter IN VARCHAR2)
  RETURN rme_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_rme_split;

测试如下：

1 select temp.ass_code
2 from (SELECT COLUMN_VALUE AS ass_code
3 FROM TABLE(rme_delete.fn_rme_split(‘a,b,c,d,e,f ‘,‘,‘))) temp;执行结果如下：