Oracle常用封装函数

 1 CREATE OR REPLACE FUNCTION MES1.strsplit(p_str       IN VARCHAR2 --字符串
 2                                         ,
 3                                          p_delimiter IN VARCHAR2 --分隔符
 4                                          ) RETURN ty_str_split IS
 5   --根据指定的字符与分隔符，对字符串进行拆分，返回表值类型的结果集
 6   j         INT := 0;
 7   i         INT := 1;
 8   len       INT := 0;
 9   len1      INT := 0;
10   str       VARCHAR2(4000);
11   str_split ty_str_split := ty_str_split();
12 BEGIN
13   len  := length(p_str);
14   len1 := length(p_delimiter);
15 
16   WHILE j < len LOOP
17     j := instr(p_str, p_delimiter, i);
18   
19     IF j = 0 THEN
20       j   := len;
21       str := substr(p_str, i);
22       str_split.extend;
23     
24       str_split(str_split.count) := str;
25     
26       IF i >= len THEN
27         EXIT;
28       END IF;
29     ELSE
30       str := substr(p_str, i, j - i);
31       i   := j + len1;
32       str_split.extend;
33       str_split(str_split.count) := str;
34     END IF;
35   END LOOP;
36 
37   RETURN str_split;
38 END;