给ORACLE添加split和splitstr函数
转自：http://www.cnblogs.com/enjoycode/archive/2012/11/10/oracle_split.html
给Oracle添加split和splitstr函数 最近项目中有很多需要做批量操作的需求，客户端把一组逗号分隔的ID字符串传给数据库，存储过程就需要把它们分割，然后逐个处理。
?
以往的处理方式有如下几种：
?
1、在存储过程内写循环，逐个分析字符串中的ID，然后逐个处理。缺点：循环一次处理一个，如果每次判断都很多，效率将很受影响。适合每次处理要做单独判断的情况。
?
2、使用临时表，先调用一个存储过程将ID拆分并插入到临时表中，然后结合临时表可以写SQL一次处理多笔。缺点：需要插临时表，效率不高，数据量越大影响越严重。
?
以前的项目用的最多的还是第2中方式，毕竟方便，且效率比第1种好。?
?
现在项目中用到了很多很多的批量操作，很多的重复代码让我不厌其烦。忽然想到，.Net和JS中都有split类似的函数，拆分字符串很方便，oracle中要是也有这样的功能该多好呀。
?
多方查找资料发现，给oracle添加split函数是完全可以实现的，避免了插入临时表，所以效率比上面的第2中方法效率高很多。
?
后来我还添加了splitstr函数，可以很方便获取字符串中的指定节点。?
?
有了这两个函数，处理批量操作，真是如虎添翼，效率倍增，嘿嘿……
?
好了，闲话少说，上代码！如有不妥之处，请各位前辈博友斧正。
?
?
?1 /*
?2 ?* Oracle 创建 split 和 splitstr 函数
?3 ?*/
?4?
?5 /* 创建一个表类型 */
?6 create or replace type tabletype as table of VARCHAR2(32676)
?7 /
?8?
?9 /* 创建 split 函数 */
10 CREATE OR REPLACE FUNCTION split (p_list CLOB, p_sep VARCHAR2 := ',')
11 ? ?RETURN tabletype
12 ? ?PIPELINED
13 /**************************************
14 ?* Name: ? ? ? ?split
15 ?* Author: ? ? ?Sean Zhang.
16 ?* Date: ? ? ? ?2012-09-03.
17 ?* Function: ? ?返回字符串被指定字符分割后的表类型。
18 ?* Parameters: ?p_list: 待分割的字符串。
19 ? ? ? ? ? ? ? ? p_sep: 分隔符，默认逗号，也可以指定字符或字符串。
20 ?* Example: ? ? SELECT *
21 ? ? ? ? ? ? ? ? ? FROM users
22 ? ? ? ? ? ? ? ? ?WHERE u_id IN (SELECT COLUMN_VALUE
23 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? FROM table (split ('1,2')))
24 ? ? ? ? ? ? ? ? 返回u_id为1和2的两行数据。
25 ?**************************************/
26 IS
27 ? ?l_idx ? ?PLS_INTEGER;
28 ? ?v_list ? VARCHAR2 (32676) := p_list;
29 BEGIN
30 ? ?LOOP
31 ? ? ? l_idx ? := INSTR (v_list, p_sep);
32?
33 ? ? ? IF l_idx > 0
34 ? ? ? THEN
35 ? ? ? ? ?PIPE ROW (SUBSTR (v_list, 1, l_idx - 1));
36 ? ? ? ? ?v_list ? := SUBSTR (v_list, l_idx + LENGTH (p_sep));
37 ? ? ? ELSE
38 ? ? ? ? ?PIPE ROW (v_list);
39 ? ? ? ? ?EXIT;
40 ? ? ? END IF;
41 ? ?END LOOP;
42 END;
43 /
44?
45 /* 创建 splitstr 函数 */
46 CREATE OR REPLACE FUNCTION splitstr (str IN CLOB,
47 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?i ? IN NUMBER := 0,
48 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?sep IN VARCHAR2 := ','
49 )
50 ? ?RETURN VARCHAR2
51 /**************************************
52 ?* Name: ? ? ? ?splitstr
53 ?* Author: ? ? ?Sean Zhang.
54 ?* Date: ? ? ? ?2012-09-03.
55 ?* Function: ? ?返回字符串被指定字符分割后的指定节点字符串。
56 ?* Parameters: ?str: 待分割的字符串。
57 ? ? ? ? ? ? ? ? i: 返回第几个节点。当i为0返回str中的所有字符，当i 超过可被分割的个数时返回空。
58 ? ? ? ? ? ? ? ? sep: 分隔符，默认逗号，也可以指定字符或字符串。当指定的分隔符不存在于str中时返回sep中的字符。
59 ?* Example: ? ? select splitstr('abc,def', 1) as str from dual; ?得到 abc
60 ? ? ? ? ? ? ? ? select splitstr('abc,def', 3) as str from dual; ?得到 空
61 ?**************************************/
62 IS
63 ? ?t_i ? ? ? NUMBER;
64 ? ?t_count ? NUMBER;
65 ? ?t_str ? ? VARCHAR2 (4000);
66 BEGIN
67 ? ?IF i = 0
68 ? ?THEN
69 ? ? ? t_str ? := str;
70 ? ?ELSIF INSTR (str, sep) = 0
71 ? ?THEN
72 ? ? ? t_str ? := sep;
73 ? ?ELSE
74 ? ? ? SELECT COUNT ( * )
75 ? ? ? INTO t_count
76 ? ? ? FROM table (split (str, sep));
77?
78 ? ? ? IF i <= t_count
79 ? ? ? THEN
80 ? ? ? ? ?SELECT str
81 ? ? ? ? ?INTO t_str
82 ? ? ? ? ?FROM (SELECT ROWNUM AS item, COLUMN_VALUE AS str
83 ? ? ? ? ? ? ? ?FROM table (split (str, sep)))
84 ? ? ? ? ?WHERE item = i;
85 ? ? ? END IF;
86 ? ?END IF;
87?
88 ? ?RETURN t_str;
89 END;
90 /?
?转自：http://hi.baidu.com/zha0ku1/item/0e65dda912f0cdfa14329b0b
oracle中的split 字符串分割函数（绝对可用）
Oracle中的Split函数首先需要定义 2 个类型
1. Row 类型CREATE OR REPLACE TYPE ty_row_str_split ?as object (strValue VARCHAR2 (4000))
2. Table 类型CREATE OR REPLACE TYPE ty_tbl_str_split IS TABLE OF ty_row_str_split
创建函数：
CREATE OR REPLACE FUNCTION fn_split(p_str ? ? ? IN VARCHAR2,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?p_delimiter IN VARCHAR2)
? RETURN ty_tbl_str_split IS
? ?j ? ? ? ? INT := 0;
? ?i ? ? ? ? INT := 1;
? len ? ? ? INT := 0;
? ?len1 ? ? ?INT := 0;
? str ? ? ? VARCHAR2(4000);
? ?str_split ty_tbl_str_split := ty_tbl_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) := ty_row_str_split(strValue => 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) := ty_row_str_split(strValue => str);
? ? END IF;
? END LOOP;
?
?
? RETURN str_split;
END fn_split;
?
?
使用样例：
select to_number(strvalue) as Value from table(fn_split('1,2,3',','))
select to_char(strvalue) as Value from table(fn_split('aa,bb,cc',','))?
