最近项目中有很多需要做批量操作的需求，客户端把一组逗号分隔的ID字符串传给数据库，存储过程就需要把它们分割，然后逐个处理。

以往的处理方式有如下几种：

1、在存储过程内写循环，逐个分析字符串中的ID，然后逐个处理。缺点：循环一次处理一个，如果每次判断都很多，效率将很受影响。适合每次处理要做单独判断的情况。

2、使用临时表，先调用一个存储过程将ID拆分并插入到临时表中，然后结合临时表可以写SQL一次处理多笔。缺点：需要插临时表，效率不高，数据量越大影响越严重。

以前的项目用的最多的还是第2中方式，毕竟方便，且效率比第1种好。

现在项目中用到了很多很多的批量操作，很多的重复代码让我不厌其烦。忽然想到，.Net和JS中都有split类似的函数，拆分字符串很方便，oracle中要是也有这样的功能该多好呀。

多方查找资料发现，给oracle添加split函数是完全可以实现的，避免了插入临时表，所以效率比上面的第2中方法效率高很多。

后来我还添加了splitstr函数，可以很方便获取字符串中的指定节点。

有了这两个函数，处理批量操作，真是如虎添翼，效率倍增，嘿嘿……

好了，闲话少说，上代码！如有不妥之处，请各位前辈西友斧正。

1/*

2* Oracle创建split 和 splitstr 函数

3*/

4

5/*创建一个表类型*/

6createorreplacetypetabletypeastableofVARCHAR2(32676)

7/

8

9/*创建 split 函数*/

10CREATEORREPLACEFUNCTIONsplit(p_listCLOB,p_sepVARCHAR2:=',')

11RETURNtabletype

12PIPELINED

13/**************************************

14*Name:split

15*Author:SeanZhang.

16*Date:2012-09-03.

17*Function:返回字符串被指定字符分割后的表类型。

18*Parameters:p_list:待分割的字符串。

19p_sep:分隔符，默认逗号，也可以指定字符或字符串。

20*Example:SELECT*

21FROMusers

22WHEREu_idIN(SELECTCOLUMN_VALUE

23FROMtable(split('1,2')))

24返回u_id为1和2的两行数据。

25**************************************/

26IS

27l_idxPLS_INTEGER;

28v_listVARCHAR2(32676):=p_list;

29BEGIN

30LOOP

31l_idx:=INSTR(v_list,p_sep);

32

33IFl_idx>0

34THEN

35PIPEROW(SUBSTR(v_list,1,l_idx-1));

36v_list:=SUBSTR(v_list,l_idx+LENGTH(p_sep));

37ELSE

38PIPEROW(v_list);

39EXIT;

40ENDIF;

41ENDLOOP;

42END;

43/

44

45/*创建 splitstr函数*/

46CREATEORREPLACEFUNCTIONsplitstr(strINCLOB,

47iINNUMBER:=0,

48sepINVARCHAR2:=','

49)

50RETURNVARCHAR2

51/**************************************

52*Name:splitstr

53*Author:SeanZhang.

54*Date:2012-09-03.

55*Function:返回字符串被指定字符分割后的指定节点字符串。

56*Parameters:str:待分割的字符串。

57i:返回第几个节点。当i为0返回str中的所有字符，当i超过可被分割的个数时返回空。

58sep:分隔符，默认逗号，也可以指定字符或字符串。当指定的分隔符不存在于str中时返回sep中的字符。

59*Example:selectsplitstr('abc,def',1)asstrfromdual;得到abc

60selectsplitstr('abc,def',3)asstrfromdual;得到空

61**************************************/

62IS

63t_iNUMBER;

64t_countNUMBER;

65t_strVARCHAR2(4000);

66BEGIN

67IFi=0

68THEN

69t_str:=str;

70ELSIFINSTR(str,sep)=0

71THEN

72t_str:=sep;

73ELSE

74SELECTCOUNT(*)

75INTOt_count

76FROMtable(split(str,sep));

77

78IFi<=t_count

79THEN

80SELECTstr

81INTOt_str

82FROM(SELECTROWNUMASitem,COLUMN_VALUEASstr

83FROMtable(split(str,sep)))

84WHEREitem=i;

85ENDIF;

86ENDIF;

87

88RETURNt_str;

89END;

90/

相关阅读

• 暂无推荐