Oracle拆分字符串及排序,oracle11g 拆分字符串的详细技巧

功能需求

@H_403_11@ @H_403_11@有一个比较长的@H_403_11@sql@H_403_11@语句，查询出来中间会有类似“@H_403_11@abc1,cbd2,db3,db5@H_403_11@”这样的行记录，然后想要达到的效果就是将这样的记录按照逗号间隔符拆分出来一条变成@H_403_11@4@H_403_11@条，这样记录有多条，@H_403_11@然后所有有逗号间隔符的都要拆分出来，然后形成新结果集去关联别的表记录。这条长的@H_403_11@sql@H_403_11@如下：

@H_502_41@

select extractvalue(xmltype(r.approve_content),'/templet/content/nodeId') ids from res_approve_info t

inner join res_approve_content r on t.res_approve_info_id=r.res_approve_info_id

where

t.auth_type_cd='JHGL_KFJH_10' and t.status_cd='2' and t.created_date >to_date('2016-01-01','yyyy-mm-dd')

and extractvalue(xmltype(r.approve_content),'/templet/content/isOnTimeOrDelay')='2'

1、思路分析

核心在于拆分字符串，拆分字符串sql参考先拆分然后再整合成一个临时表，拆分表达式已经想好了大概有如下2种方法：

(1)正则表达式的方式

(2)存储函数的方式

2、正则表达式的实现方式

必须是oracle 10g+的版本才支持，以逗号间隔：

SELECTREGEXP_SUBSTR ('abc1,db5','[^,]+',1,rownum)

FROMDUAL

CONNECTBYROWNUM<=

LENGTH('abc1,db5') -LENGTH(REPLACE('abc1,',',''))+1;

执行如下：

@H_502_41@

>SELECTREGEXP_SUBSTR ('abc1,rownum)

2FROMDUAL

3CONNECTBYROWNUM<=

4LENGTH('abc1,''))+1;

REGEXP_SUBSTR('ABC1,CBD2,DB3,D

----------------------------------

abc1

cbd2

db3

db5

3、以类型和函数的方式实现

(1)建立TYPE类型

@H_502_41@

CREATEORREPLACETYPEstr_splitISTABLEOFVARCHAR2(4000)

(2)建立FUNCTION存储函数

@H_502_41@

CREATEORREPLACEFUNCTIONfun_splitstr(p_stringINVARCHAR2,p_delimiterINVARCHAR2)

RETURNstr_split

PIPELINED

AS

v_lengthNUMBER:= LENGTH(p_string);

v_startNUMBER:=1;

v_indexNUMBER;

BEGIN

WHILE(v_start <= v_length)

LOOP

v_index := INSTR(p_string,p_delimiter,v_start);

IFv_index =0

THEN

PIPEROW(SUBSTR(p_string,v_start));

v_start := v_length +1;

ELSE

PIPEROW(SUBSTR(p_string,v_start,v_index - v_start));

v_start := v_index +1;

ENDIF;

ENDLOOP;

RETURN;

ENDfun_splitstr;

(3)开始验证使用函数

@H_502_41@

>select*fromtable(selectfun_splitstr('abc1,') idsfromdual)t1;

COLUMN_VALUE

--------------------------------------------------------------------------------

abc1

cbd2

db3

db5

4、效率比较高的办法

(1)，在java代码(或者存储过程)里面循环遍历如下原始结果集，

(2)，通过拆分函数，按行循环来拆，把每一个拆出来的结果都插入到一个临时表或者临时集合t3里面，使用select * from table(fun_splitstr('aaa,bbb,ccc',','));来实现。

(3)，最后你用这个临时集合去关联你需要关联的表就可以了

select t4.* from t3 left join plan6_node t4where t4.id=t3.id

5、效率比较低的办法(with临时表)

采用with临时表的办法来实现，如下，不过效率比较低：

@H_502_41@

withtemp0as(selectLEVELlvfromdualCONNECTBYLEVEL<=100)

selectid,substr(t.vals,instr(t.vals,tv.lv) +1,

instr(t.vals,tv.lv +1) -(

instr(t.vals,tv.lv) +1)

)ASname

from(selectid,'||name||','ASvals,

length(name||',') -nvl(length(REPLACE(name,')),0)AScnt

from(select1asid,'abc1,db5'asnamefromdualunionallselect2,'zhangsan1,lisi2,wanger3'fromdual)

) tjointemp0 tv

ontv.lv <= t.cntorderby1;

执行过程如下：

@H_502_41@

>withtemp0as(selectLEVELlvfromdualCONNECTBYLEVEL<=100)

2selectid,

3instr(t.vals,tv.lv +1) -(

4instr(t.vals,tv.lv) +1)

5)ASname

6from(selectid,

7length(name||',0)AScnt

8from(select1asid,wanger3'fromdual)

9) tjointemp0 tv

10ontv.lv <= t.cntorderby1;

IDNAME

---------- --------------------------------------------------

1abc1

1db3

1cbd2

1db5

2wanger3

2zhangsan1

2lisi2

7rowsselected

这里效率比较低的原因是：select1asid,wanger3'fromdual，这里临时表，以为需要大量不停的循环遍历它，如果表数据量大或者获取比较复杂的话，这里就会卡死的。

临时解决办法是，createtablez_temp2asselect1asid,wanger3'fromdual;然后连接这个temp1表进行操作，那么总体sql如下：

@H_502_41@

createtablez_temp2asselect1asid,wanger3'fromdual;

withtemp0as(selectLEVELlvfromdualCONNECTBYLEVEL<=100)

selectid,0)AScnt

fromz_temp2

) tjointemp0 tv

ontv.lv <= t.cntorderby1;

这样用临时表的缺陷就是，不能一条搞定，需要分2个阶段来执行，而且每次都需要清空临时表z_temp2，这点比较麻烦。

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。