今天在做报表查询时遇到一个SQL问题:

某需求表环节处理人字段存储的是用户的工号,由于有多人的情况,所以该表在数据存储时是以英文逗号分开存储的。

客户需求是要把用户的工号展示成姓名,多个人用逗号区分。

需求表字段存储结构如下:

需求目标:

解决方案

1)创建一个Oracle Table 类型

--创建一个表类型
create or replace type table_type as table of varchar2(32676);

2) 创建一个Oracle 自定义 Function

--创建 自定义 split 函数
create or replace function split(p_list clob, p_sep varchar2 := ',')
  return table_type
  pipelined is
  l_idx pls_integer;
  v_list varchar2(32676) := p_list;
begin
  loop
    l_idx := instr(v_list, p_sep);
    if l_idx > 0 then
      pipe row(substr(v_list, 1, l_idx - 1));
      v_list := substr(v_list, l_idx + length(p_sep));
    else
      pipe row(v_list);
      exit;
    end if;
  end loop;
end;

3) 演示Table类型+自定义函数效果

select split('w06549,w06543',',') from dual;

(返回值为Collection类型)

4) 点击查看Collection详情:

5) 演示Table类型+自定义函数效果

select column_value from table(split('w06549,w06543',','));

(返回值为一列数据,列名称为COLUMN_VALUE)

6) 通过Table类型和自定义函数实现需求目标

SELECT e.emi_current_handler,
       (SELECT listagg(p.pn_name, ',') within
         GROUP(
         ORDER BY p.id)
          FROM person p
         where p.id in
               (select column_value
                  from table(split(e.emi_current_handler, ',')))) USER_NAME
  FROM env_maintenance_info e
 where e.emi_current_handler is not null
   and instr(e.emi_current_handler, ',') > 0;

(问题完美解决)

解释说明

自定义split函数:

该函数有两个参数,第一个参数为要处理的字符串,第二个参数为要分割的方式。灵活的支持业务表多种形式的分割,列:“,”、“|”、“&”、“_”...

listagg函数:

Oracle19C版本后因wm_concat函数效率过低已废弃,可以通过listagg函数来实现行转列的需求。

wm_concat函数使用示例:

listagg函数使用示例:

wm_concat与listagg对比:

1) wm_concat性能略差
2) wm_concat使用后为CLOB字段需要to_char转换
3) listagg可以自定义排序方式、以及拼接方式
4) listagg性能优于wm_concat
5) 两者都有长度限制

最后修改:2021 年 10 月 02 日
如果觉得我的文章对你有用,请随意赞赏