在线报名在线咨询
主页 > 学习园地 > 编程技巧 >

Oracle｜关于字符串的特殊处理

时间:2021-08-07 12:16来源:未知 作者:中博IT教育

某需求表环节处理人字段存储的是用户的工号，由于有多人的情况，所以该表在数据存储时是以英文逗号分开存储的。 客户需求是要把用户的工号展示成姓名，多个人用逗号区分。
某需求表环节处理人字段存储的是用户的工号，由于有多人的情况，所以该表在数据存储时是以英文逗号分开存储的。
 
客户需求是要把用户的工号展示成姓名，多个人用逗号区分。
 
解决方案
 
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)  通过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对比：
 
1) wm_concat性能略差
 
2) wm_concat使用后为CLOB字段需要to_char转换
 
3) listagg可以自定义排序方式、以及拼接方式
 
4) listagg性能优于wm_concat
 
5) 两者都有长度限制

(责任编辑：中博IT教育)
栏目列表
中心简介
ACCP软件工程师课程
  • 开班信息
  • 讲座试听
时　间:周日上午10：00
主　题:专业技术讲座
主讲人:北大青鸟资深讲师
电　话:051685628888
说　明:限30人 电话订座

推荐内容
热点内容

苏公网安备 32030302000649号