1
问题背景
今天在做报表查询时遇到一个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) 两者都有长度限制
