oracle11g 拆分字元串的詳細技巧

知識 10-15

<-->功能需求

有一個比較長的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

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，這點比較麻煩。