wm_concat 多行字元串拼接以及字元串拆分詳解

一、wm_concat 多行字元串拼接

有如下員工部門表emp_dept,資料如下：

；

需要實作如下結果

就需要用到wm_concat 函數:

sql如下：

select dept_name 部門, wm_concat(t.emp_name) 員工 from  emp_dept t  group by dept_name

           

但是這樣的查詢結果卻是：

這裡還需要to_char一下

select dept_name 部門,  to_char(wm_concat(t.emp_name))員工 from  emp_dept t  group by dept_name

           

如果需要将拼接的字元串改為 ‘；’

select dept_name 部門,  replace(to_char(wm_concat(t.emp_name)),',',';')員工 from  emp_dept t  group by dept_name

           

執行結果：

二、字元串拆分為多行

有字元串 ‘a,b,c,d,e’需要拆分為：

sql如下:

SELECT regexp_substr('a,b,c,d,e', '[^,]+', , rownum) 字元串拆分
   from dual
 CONNECT BY rownum <= length(regexp_replace('a,b,c,d,e', '[^,]', NULL)) + ;
           

同樣我們可以将銷售部的張三,王五,李四進行拆分

with tmp as
 (select to_char(wm_concat(t.emp_name)) emp_name
    from emp_dept t
   where t.dept_name = '銷售部'
   group by dept_name)
SELECT regexp_substr(tmp.emp_name, '[^,]+', , rownum) 員工
  FROM tmp
CONNECT BY rownum <= length(regexp_replace(tmp.emp_name, '[^,]', NULL)) + ;
執行結果如下:
           

到這裡，可能有人會像可不可以将一中wm_concat 多行拼接字元串的結果全部拆分，答案是肯定，但是用sql語句是無法實作的，需要使用存儲過程，将多行拼接的字元串循環傳入到上面sql中進行拆分。有興趣的童鞋可以嘗試下，采用遊标是最好的選擇。

附上存儲過程代碼

create or replace procedure tmp_split is
  --聲明多行拼接字元串查詢遊标
  cursor cur_string_concat is
    select to_char(wm_concat(t.emp_name)) emp_name
      from emp_dept t
     group by dept_name;
  --聲明變量rec_emp_name表示為遊标cur_string_concat一行記錄
  rec_emp_name cur_string_concat%rowtype;
  --聲明拆分字元串遊标,傳入參數cur_string為需要拆分的字元串
  cursor cur_string_split(cur_string varchar2) is
    SELECT regexp_substr(cur_string, '[^,]+', , rownum) e_name
      FROM dual
    CONNECT BY rownum <=
               length(regexp_replace(cur_string, '[^,]', NULL)) + ;
  --聲明變量rec_string遊标cur_string_split的一行記錄
  rec_string cur_string_split%rowtype;
begin
  --先打開字元串拼接遊标
  open cur_string_concat;
  --開始循環
  loop
    --将cur_string_concat每次循環的結果指派給變量rec_emp_name
    fetch cur_string_concat
      into rec_emp_name;
    --當cur_string_concat疊代完了退出
    exit when cur_string_concat%notfound;
    open cur_string_split(rec_emp_name.emp_name);
    loop
      fetch cur_string_split
        into rec_string;
      exit when cur_string_split%notfound;
      --将拆分的結果儲存到表中
      insert into tmp_split_emp
        select rec_string.e_name from dual t;
    end loop;
    --一定要關閉遊标
    close cur_string_split;
  end loop;
  commit;
  close cur_string_concat;

end tmp_split;
           

執行存儲過程：

begin 
  tmp_split;
 end ;
           

查詢結果為：