首先创建一个类型:
CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000);
创建函数:
1 CREATE OR REPLACE FUNCTION func_splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2) 2 RETURN str_split 3 PIPELINED 4 AS 5 v_length NUMBER := LENGTH(p_string); 6 v_start NUMBER := 1; 7 v_index NUMBER; 8 BEGIN 9 WHILE(v_start <= v_length) 10 LOOP 11 v_index := INSTR(p_string, p_delimiter, v_start); 12 13 IF v_index = 0 14 THEN 15 PIPE ROW(SUBSTR(p_string, v_start)); 16 v_start := v_length + 1; 17 ELSE 18 PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start)); 19 v_start := v_index + 1; 20 END IF; 21 END LOOP; 22 23 RETURN; 24 END func_splitstr;