Oracle｜字符串特殊处理

Java小技巧

发布于 2022-05-23 04:39:45

1.5K00

代码可运行

文章被收录于专栏：Java小技巧Java小技巧

运行总次数：0

代码可运行

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;