函数如下：
1 create or replace FUNCTION fn_rme_split(p_str IN VARCHAR2, 2 p_delimiter IN VARCHAR2) 3 RETURN rme_split 4 PIPELINED IS 5 j INT := 0; 6 i INT := 1; 7 len INT := 0; 8 len1 INT := 0; 9 str VARCHAR2(4000); 10 BEGIN 11 len := LENGTH(p_str); 12 len1 := LENGTH(p_delimiter); 13 14 WHILE j < len LOOP 15 j := INSTR(p_str, p_delimiter, i); 16 IF j = 0 THEN 17 j := len; 18 str := SUBSTR(p_str, i); 19 PIPE ROW(str); 20 IF i >= len THEN 21 EXIT; 22 END IF; 23 ELSE 24 str := SUBSTR(p_str, i, j - i); 25 i := j + len1; 26 PIPE ROW(str); 27 END IF; 28 END LOOP; 29 RETURN; 30 END fn_rme_split;
测试如下：
1 select temp.ass_code 2 from (SELECT COLUMN_VALUE AS ass_code 3 FROM TABLE(rme_delete.fn_rme_split('a,b,c,d,e,f ',','))) temp;
执行结果如下：
