wm_concat 多行字符串拼接以及字符串拆分详解

逃离我推掉我的手 2022-05-22 03:04 307阅读 0赞

**一、wm\_concat 多行字符串拼接**  
有如下员工部门表emp\_dept,数据如下：  
![这里写图片描述][70]；  
需要实现如下结果  
![这里写图片描述][70 1]  
就需要用到wm\_concat 函数:  
sql如下：

select dept_name 部门, wm_concat(t.emp_name) 员工 from emp_dept t group by dept_name

但是这样的查询结果却是：  
![这里写图片描述][70 2]

这里还需要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

执行结果：  
![这里写图片描述][70 3]  
二、字符串拆分为多行  
有字符串 ‘a,b,c,d,e’需要拆分为：  
![这里写图片描述][70 4]  
sql如下:

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

同样我们可以将销售部的张三,王五,李四进行拆分

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, '[^,]+', 1, rownum) 员工 FROM tmp CONNECT BY rownum <= length(regexp_replace(tmp.emp_name, '[^,]', NULL)) + 1;
    执行结果如下:

![这里写图片描述][70 5]  
到这里，可能有人会像可不可以将一中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, '[^,]+', 1, rownum) e_name FROM dual CONNECT BY rownum <= length(regexp_replace(cur_string, '[^,]', NULL)) + 1;
      --声明变量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 ;

查询结果为：  
![这里写图片描述][70 6]

[70]: /images/20220522/c72fcee3ec0744f0ac2cf229108a0d3e.png
[70 1]: /images/20220522/6f443a6c305149fba305081974097377.png
[70 2]: /images/20220522/66b3e36f8d61480198824ad6e723e21a.png
[70 3]: /images/20220522/dc8a6941029b4f0c96e66282816a6665.png
[70 4]: /images/20220522/e56ff921a0eb4a95a5e35cb01008c3d7.png
[70 5]: /images/20220522/18e58d335897452a993905f8a733d16a.png
[70 6]: /images/20220522/81718bb1e33b4a9fa4749c049f7b232d.png