自定义函数实现字符串分割，返回集合类型

为了实现将tom,lucy,polly这种样式的字符串按照分隔符分割，可以使用下面的自定义函数：

--创建工具类型
CREATE OR REPLACE TYPE VARCHAR2ARRAY AS TABLE OF VARCHAR2(1000);

--创建工具函数
CREATE OR REPLACE FUNCTION SPLITSTR2ARRAY(v_sourcestring IN VARCHAR2,v_delimiter IN VARCHAR2)
  RETURN VARCHAR2ARRAY
IS
  v_len NUMBER;
  v_tmpstr VARCHAR2(1000);
  v_postion1 NUMBER := 0;
  v_postion2 NUMBER := 0;
  v_resultarray VARCHAR2ARRAY := VARCHAR2ARRAY();
BEGIN
  v_tmpstr := TRIM(v_delimiter FROM TRIM(v_sourcestring));
  v_len := LENGTH(v_tmpstr);
  IF v_len = 0 THEN
    RETURN v_resultarray;
  ELSIF INSTR(v_tmpstr,v_delimiter) = 0 THEN
    v_resultarray.extend;
    v_resultarray(v_resultarray.count) := TRIM(v_tmpstr);
    RETURN v_resultarray;
  ELSE
    WHILE INSTR(v_tmpstr,v_delimiter) > 0 LOOP
      v_resultarray.extend;
      v_resultarray(v_resultarray.count) := SUBSTR(v_tmpstr,1,INSTR(v_tmpstr,v_delimiter)-1);
      v_tmpstr := SUBSTR(v_tmpstr,INSTR(v_tmpstr,v_delimiter)+1,LENGTH(v_tmpstr));
    END LOOP;
    v_resultarray.extend;
    v_resultarray(v_resultarray.count) := TRIM(v_delimiter FROM TRIM(v_tmpstr));
    RETURN v_resultarray;
  END IF;
END SPLITSTR2ARRAY;
/

--使用

declare v_results VARCHAR2ARRAY := VARCHAR2ARRAY();
v_counter NUMBER;
begin
SELECT SPLITSTR2ARRAY('tom,lucy,lily',',') INTO v_results FROM dual;
v_counter := v_results.first;
WHILE v_counter IS NOT NULL LOOP
dbms_output.put_line(v_results(v_counter));
v_counter := v_results.next(v_counter);
END LOOP;
end;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28998293/viewspace-1311627/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28998293/viewspace-1311627/