oracle11g 拆分字符串的详细技巧

 

<-->功能需求

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

 

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,cbd2,db3,db5', '[^,]+', 1,rownum)

  FROM DUAL

  CONNECTBYROWNUM <=

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

 

执行如下：

SQL>

SQL> SELECT REGEXP_SUBSTR ('abc1,cbd2,db3,db5', '[^,]+', 1,rownum)

  2    FROM DUAL

  3    CONNECTBYROWNUM <=

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

REGEXP_SUBSTR('ABC1,CBD2,DB3,D

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

abc1

cbd2

db3

db5

 

SQL>

 

 

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

（1）建立TYPE类型

CREATEORREPLACETYPE str_split ISTABLEOFVARCHAR2 (4000)

 

 

（2）建立FUNCTION存储函数

CREATEORREPLACEFUNCTION fun_splitstr(p_string INVARCHAR2, p_delimiter INVARCHAR2)                                                                                                                                 

    RETURN str_split

    PIPELINED

AS

    v_length   NUMBER := LENGTH(p_string);

    v_start    NUMBER := 1;

    v_index    NUMBER;

BEGIN

    WHILE(v_start <= v_length)

    LOOP

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

 

        IF v_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;

END fun_splitstr;

 

（3）      开始验证使用函数

SQL> select * fromtable(select fun_splitstr('abc1,cbd2,db3,db5',',') ids from dual)t1;                                                                                                                                                               

COLUMN_VALUE

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

abc1

cbd2

db3

db5

 

SQL>

 

 

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临时表的办法来实现，sql如下，不过效率比较低：

with temp0 as (selectLEVEL lv from dual CONNECTBYLEVEL <= 100) 

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

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

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

                  ) ASname 

      from (selectid,',' || name || ','AS vals, 

                 length(name || ',') - nvl(length(REPLACE(name, ',')), 0) AS cnt 

            from (select1asid,'abc1,cbd2,db3,db5'asname  from dual union  allselect2, 'zhangsan1,lisi2,wanger3'from dual)                                                                                               

           ) t join temp0 tv 

                      on  tv.lv <= t.cnt  orderby1;

 

 

执行过程如下：

SQL> with temp0 as (selectLEVEL lv from dual CONNECTBYLEVEL <= 100)

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

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

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

  5                    ) ASname

  6        from (selectid,',' || name || ','AS vals,

  7                   length(name || ',') - nvl(length(REPLACE(name, ',')), 0) AS cnt

  8              from (select1asid,'abc1,cbd2,db3,db5'asname  from dual union  allselect2, 'zhangsan1,lisi2,wanger3'from dual)                                                                                        

  9             ) t join temp0 tv

 10                        on  tv.lv <= t.cnt  orderby1;

        IDNAME

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

         1 abc1

         1 db3

         1 cbd2

         1 db5

         2 wanger3

         2 zhangsan1

         2 lisi2

7rows selected

 

SQL>

 

 

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

 

 

临时解决办法是，createtable z_temp2 asselect1asid,'abc1,cbd2,db3,db5'asname  from dual union  allselect2, 'zhangsan1,lisi2,wanger3'from dual;然后连接这个temp1表进行操作，那么总体sql如下：

createtable z_temp2 asselect1asid,'abc1,cbd2,db3,db5'asname  from dual union  allselect2, 'zhangsan1,lisi2,wanger3'from dual;                                                                          

with temp0 as (selectLEVEL lv from dual CONNECTBYLEVEL <= 100) 

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

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

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

                  ) ASname 

      from (selectid,',' || name || ','AS vals, 

                 length(name || ',') - nvl(length(REPLACE(name, ',')), 0) AS cnt 

            from z_temp2

           ) t join temp0 tv 

                      on  tv.lv <= t.cnt  orderby1;

 

 

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

 

参考文档：http://www.anbob.com/archives/221.html