当前位置： 移动技术网 > IT编程>数据库>Oracle > 给Oracle添加split和splitstr函数的方法

给Oracle添加split和splitstr函数的方法

2017年12月12日  | 移动技术网IT编程  | 我要评论

非你莫属王宇,qq飞车卡商城,涂料机械

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

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

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

以前的项目用的最多的还是第2中方式，毕竟方便，且效率比第1种好。
现在项目中用到了很多很多的批量操作，很多的重复代码让我不厌其烦。忽然想到，.net和js中都有split类似的函数，拆分字符串很方便，oracle中要是也有这样的功能该多好呀。

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

后来我还添加了splitstr函数，可以很方便获取字符串中的指定节点。
有了这两个函数，处理批量操作，真是如虎添翼，效率倍增，嘿嘿……
好了，闲话少说，上代码！如有不妥之处，请各位前辈博友斧正。
复制代码 代码如下:

/*
* oracle 创建 split 和 splitstr 函数
*/
/* 创建一个表类型 */
create or replace type tabletype as table of varchar2(32676)
/
/* 创建 split 函数 */
create or replace function split (p_list clob, p_sep varchar2 := ',')
return tabletype
pipelined
/**************************************
* name: split
* author: sean zhang.
* date: 2012-09-03.
* function: 返回字符串被指定字符分割后的表类型。
* parameters: p_list: 待分割的字符串。
p_sep: 分隔符，默认逗号，也可以指定字符或字符串。
* example: select *
from users
where u_id in (select column_value
from table (split ('1,2')))
返回u_id为1和2的两行数据。
**************************************/
is
l_idx pls_integer;
v_list varchar2 (32676) := p_list;
begin
loop
l_idx := instr (v_list, p_sep);
if l_idx > 0
then
pipe row (substr (v_list, 1, l_idx - 1));
v_list := substr (v_list, l_idx + length (p_sep));
else
pipe row (v_list);
exit;
end if;
end loop;
end;
/
/* 创建 splitstr 函数 */
create or replace function splitstr (str in clob,
i in number := 0,
sep in varchar2 := ','
)
return varchar2
/**************************************
* name: splitstr
* author: sean zhang.
* date: 2012-09-03.
* function: 返回字符串被指定字符分割后的指定节点字符串。
* parameters: str: 待分割的字符串。
i: 返回第几个节点。当i为0返回str中的所有字符，当i 超过可被分割的个数时返回空。
sep: 分隔符，默认逗号，也可以指定字符或字符串。当指定的分隔符不存在于str中时返回sep中的字符。
* example: select splitstr('abc,def', 1) as str from dual; 得到 abc
select splitstr('abc,def', 3) as str from dual; 得到 空
**************************************/
is
t_i number;
t_count number;
t_str varchar2 (4000);
begin
if i = 0
then
t_str := str;
elsif instr (str, sep) = 0
then
t_str := sep;
else
select count ( * )
into t_count
from table (split (str, sep));
if i <= t_count
then
select str
into t_str
from (select rownum as item, column_value as str
from table (split (str, sep)))
where item = i;
end if;
end if;
return t_str;
end;
/

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网