oracle自定义字符串分割函数

思路:
oracle没有提供类似java中split的函数来将字符串分割成数组,需要开发者自己搞定.
首先定一个数组类型,再定义一个函数或存储过程,处理字符串分割操作,
一个具体的实例如下:

--自定义数组类型:
CREATE OR REPLACE
TYPE Varchar2Varray IS VARRAY(100) of VARCHAR2(40);
/

--自定义字符串分割函数,返回分组后的数组
CREATE OR REPLACE FUNCTION f_strsplit (STRING VARCHAR2, substring VARCHAR2)
   RETURN varchar2varray
IS
   len       INTEGER        := LENGTH (substring);
   lastpos   INTEGER        := 1 - len;
   pos       INTEGER;
   num       INTEGER;
   i         INTEGER        := 1;
   ret       varchar2varray := varchar2varray (NULL);
   v_str     VARCHAR2 (32767);
 /**自定义split函数,将指定的字符串按指定的标志符分割成字符数组*/
BEGIN
   LOOP
      pos := INSTR (STRING, substring, lastpos + len);

      IF pos > 0
      THEN                                                            --found
         num := pos - (lastpos + len);
      ELSE                                                         --not found
         num := LENGTH (STRING) + 1 - (lastpos + len);
      END IF;

      IF i > ret.LAST
      THEN
         ret.EXTEND;
      END IF;

      v_str := SUBSTR (STRING, lastpos + len, num);
      --DBMS_OUTPUT.put_line (v_str);
     
      ret(i) := v_str;
     
      EXIT WHEN pos = 0;
      lastpos := pos;
      i := i + 1;
   END LOOP;

   RETURN ret;
END;
/

--调用方式:
SELECT *
FROM TABLE (CAST (f_strsplit ('ABC$DEFGH$IJKLMN$OPQRST', '$') AS varchar2varray ) )

将输出:
column_value
--------------
ABC
DEFGH
IJKLMN
OPQRST

也可以采用如下方式得到数组中的值
FOR cur_str IN
            (SELECT *
               FROM TABLE
                       (CAST
                           (f_strsplit ('ABC$DEFGH$IJKLMN$OPQRST', '$') AS varchar2varray
                           )
                       ))
         LOOP
           DBMS_OUTPUT.put_line (cur_str.column_value);
          --其他赋值操作
         END LOOP;

转载自：http://wangfc123.blog.163.com/blog/static/15746801200722832052502/