oracle数据库split()方法的实现与测试

CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);  
  
CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)  
  RETURN ty_str_split  
IS  
  j INT := 0;  
  i INT := 1;  
  len INT := 0;  
  len1 INT := 0;  
  str VARCHAR2 (4000);  
  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;  
  
  RETURN str_split;  
END fn_split;  
/  
  
测试：  
  
DECLARE  
  CURSOR c  
  IS  
    SELECT *  
      FROM TABLE (CAST (fn_split ('1;;12;;123;;1234;;12345', ';;') AS ty_str_split  
                  )  
              );  
  
  r c%ROWTYPE;  
BEGIN  
  OPEN c;  
  
  LOOP  
    FETCH c INTO r;  
  
    EXIT WHEN c%NOTFOUND;  
    DBMS_OUTPUT.put_line (r.column_value);  
  END LOOP;  
  
  CLOSE c;  
END;  
/   
  
结果：  
1  
12  
123  
1234  
12345  

下面语句均不行 :
fn_split('4,5,6,78',',') 返回的是 collection

select fn_split('4,5,6,78',',')[0] from dual
select fn_split('4,5,6,78',',').first from dual


SELECT * FROM TABLE (CAST (fn_split('4,5,6,78',',') AS ty_str_split)) where rownum < 2;