度量快速开发平台-业界最好的全功能软件开发平台

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
热搜: 部件 流程 SQL
查看: 59|回复: 6

[分享] oracle pl/sql split函数

[复制链接]

542

主题

5919

帖子

1万

积分

作者

Rank: 7Rank: 7Rank: 7

积分
13589
发表于 2018-3-30 13:57:34 | 显示全部楼层 |阅读模式
在实际工作中我们
经常会遇到字符串的拼接和拆分工作。

本文要做的就是单句SQL实现该功能。


先分析一下,该业务有两个关键点。


一是字符串拼接,oracle(11.2)提供了listagg函数已经实现了该功能,我们直接使用就可以。


二是字符串拆分,oracle没有实现该功能,但是java提供了split函数实现了字符串拆分功能。


我们可以参考java的split函数写一个oracle版split函数。


split函数的功能是将字符串按照特定字符分隔为多个小字符串,返回结果以List或数组类型保存。


先创建一个type类型,代码如下:


create or replace type type_str is table of varchar2(100);


再创建split函数,代码如下:


create or replace function split(p_str varchar2,p_delimiter varchar2 default ',') return type_str


is


  rs type_str:=type_str();


  l_str varchar2(4000):='';


  l_len number:=0;


begin


  l_str:=p_str;


  l_len:=length(p_delimiter);


  while length(l_str)>0 loop


     if instr(l_str,p_delimiter)>0 then


       rs.extend;


       rs(rs.count):=substr(l_str,1,instr(l_str,p_delimiter)-1);


       l_str:=substr(l_str,instr(l_str,p_delimiter)+l_len);


     else


       rs.extend;


       rs(rs.count):=l_str;


       exit;


     end if;


  end loop;


  return rs;


end;


/


show err;


测试:


1.基本功能
SQL> select column_value from table(split('1001,1002,1003',','));

COLUMN_VALUE
--------------------------------------------------------------------------------
1001
1002
1003


2.字符转数字+默认分隔符


SQL> select to_number(column_value) from table(split('1001,1002,1003'));

TO_NUMBER(COLUMN_VALUE)
-----------------------
                   1001
                   1002
                   1003
3.支持多分隔符


SQL> select column_value from table(split('1001@#1002@#1003','@#'));

COLUMN_VALUE
--------------------------------------------------------------------------------
1001
1002
1003



单个split函数测试成功了,和listagg函数联合使用,需要构建两张表。


为了方便理解,我们构建一下业务场景。
构建业务场景(本业务场景纯属虚构,如有雷同纯属巧合):


有一张作者表,记录作者的个人信息(如:姓名、年龄等),主键是序列号生成的。


有一张书籍表,记录书籍的信息(如:书名、出版社、作者等),主键是序列号生成的。


一个作者可能写过多本书,一个书可能由多个作者联合编著。


对于多对多的情况,一般的设计原则是增加多对多关系表,用于记录书籍表主键和作者表主键。


由于种种原因吧,我们现在要说的不是一般的设计,


而是直接在书籍表怎么作者属性,取值为作者表主键,但存在多个作者时用','分隔。


好的。业务场景描述清楚了,现在开始建表和初始化数据。


create table author
(
  a_id    number(8) not null,
  a_name  varchar2(100),
  a_age   number(3)
);
create table book
(
  b_id    number(8) not null,
  b_name  varchar2(100),
  a_id    varchar2(100)
);
insert into author values (1001,'zhangsan',40);
insert into author values (1002,'lisi',30);
insert into author values (1003,'wangwu',50);
commit;
insert into book values (2001,'Think in pl/sql','1001,1002,1003');
commit;


与listagg函数联合查询:


select b_id,b_name,a_id,
(select listagg(a_name,',') within group (order by a_id) from author
   where a_id in (select to_number(column_value) from table(split(b.a_id,',')))) a_name
from book b;

回复

使用道具 举报

542

主题

5919

帖子

1万

积分

作者

Rank: 7Rank: 7Rank: 7

积分
13589
 楼主| 发表于 2018-3-30 13:58:11 | 显示全部楼层
回复 支持 反对

使用道具 举报

226

主题

2486

帖子

5682

积分

论坛元老

Rank: 8Rank: 8

积分
5682
发表于 2018-3-30 17:39:04 | 显示全部楼层
回复 支持 反对

使用道具 举报

226

主题

2486

帖子

5682

积分

论坛元老

Rank: 8Rank: 8

积分
5682
发表于 2018-3-30 17:39:29 | 显示全部楼层
回复 支持 反对

使用道具 举报

226

主题

2486

帖子

5682

积分

论坛元老

Rank: 8Rank: 8

积分
5682
发表于 2018-4-2 17:49:30 | 显示全部楼层
回复 支持 反对

使用道具 举报

226

主题

2486

帖子

5682

积分

论坛元老

Rank: 8Rank: 8

积分
5682
发表于 2018-4-2 17:50:05 | 显示全部楼层
回复 支持 反对

使用道具 举报

542

主题

5919

帖子

1万

积分

作者

Rank: 7Rank: 7Rank: 7

积分
13589
 楼主| 发表于 前天 16:44 | 显示全部楼层
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

手机版|小黑屋|重庆度量科技  本站关键词:快速开发平台

GMT+8, 2018-4-20 03:07 , Processed in 0.357966 second(s), 25 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表