create type str_split is table of varchar2(4000) ;
1 CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
2 /**********************************************************************
3 * xdshi add blockment 2015-09-21
4 * 通用方法：按列返回分割字符串后的内容
5 * 入参：p_string 分割字符串
6 * p_delimiter 分割符
7 * 返回：每个分割出来的字符串
8 **********************************************************************/
9 RETURN str_split
10 PIPELINED
11 AS
12 v_length NUMBER := LENGTH(p_string);
13 v_start NUMBER := 1;
14 v_index NUMBER;
15 BEGIN
16 WHILE(v_start <= v_length)
17 LOOP
18 v_index := INSTR(p_string, p_delimiter, v_start);
19
20 IF v_index = 0
21 THEN
22 PIPE ROW(SUBSTR(p_string, v_start));
23 v_start := v_length + 1;
24 ELSE
25 PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
26 v_start := v_index + 1;
27 END IF;
28 END LOOP;
29
30 RETURN;
31 END splitstr;
