Oracle的字符串分割

TYPE ty_str_split IS TABLE OF VARCHAR2(32767);
FUNCTION fn_split(p_str IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN ty_str_split pipelined;
FUNCTION fn_split_clob(p_str clob,p_delimiter VARCHAR2 DEFAULT ',') RETURN ty_str_split pipelined;


/**
   * 模块:JSON解析
   * 功能:解析json数据的前提，json以逗号','进行分割
   * 参数:p_str:需要分割的字符串
   *      p_delimiter:分割符号
   * 返回:返回分割后的pipelined
   * 注意:
   **/
  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 (32767);
    str_split ty_str_split := ty_str_split();
  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);
          str_split.EXTEND;
          str_split(str_split.COUNT) := str;
          IF i >= len
          THEN
            EXIT;
          END IF;
        ELSE
          str := SUBSTR (p_str, i, j - i);
          i := j + len1;
          str_split.EXTEND;
          str_split(str_split.COUNT) := str;
        END IF;
      END LOOP;

   for i in 1..str_split.count loop
     pipe row(str_split(i));
   end loop;
    --RETURN str_split;
  END;

  FUNCTION fn_split_clob(p_str clob,p_delimiter VARCHAR2 DEFAULT ',')
  RETURN ty_str_split pipelined is
    j INT := 0;
    i INT := 1;
    len INT := 0;
    len1 INT := 0;
    str VARCHAR2 (32767);
    str_split ty_str_split := ty_str_split();
  BEGIN
    --len := LENGTH(p_str);
    len := dbms_lob.getlength(p_str);
    len1 := LENGTH(p_delimiter);

    WHILE j < len
      LOOP
        --j := INSTR (p_str, p_delimiter, i);
        j := dbms_lob.INSTR(p_str, p_delimiter, i);
        IF j = 0 THEN
          j := len;
          --str := SUBSTR (p_str, i);
          str := dbms_lob.substr(p_str, j - i ,i);
          str_split.EXTEND;
          str_split(str_split.COUNT) := str;
          --dbms_output.put_line(str);
          IF i >= len
          THEN
            EXIT;
          END IF;
        ELSE
          --str := SUBSTR (p_str, i, j - i);
          str := dbms_lob.substr(p_str, j - i ,i);
          i := j + len1;
          str_split.EXTEND;
          str_split(str_split.COUNT) := str;
          --dbms_output.put_line(str);
        END IF;
      END LOOP;

   for i in 1..str_split.count loop
     pipe row(str_split(i));
   end loop;

    --RETURN 1;
  END;