自定義函式實現字串分割，返回集合型別

為了實現將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/，如需轉載，請註明出處，否則將追究法律責任。