暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

巧用SQL:oracle pl/sql split函数

李伟 2016-08-24
1372


李伟

专注于oracle pl/sql开发和Java开发,擅长复杂业务逻辑、算法的pl/sql实现。



背景


在软件开发过程中程序员经常会遇到字符串的拼接和拆分工作。


以java开发为例:

前台传入字符串拼接形式的一个JSON数据,如:"1001,1002,1003",这可能代表了一组序号。程序员需要将序号转名称后按照相同的格式输出,如:“张三、李四、王五”。Java程序员通用的做法是在service层将接收的"1001,1002,1003"拆分(使用java split函数),然后封装List,将List传递给DAO,再传递给ORM持久层的xml调用sql执行,sql的返回结果用List接收,并在service层遍历List和拼接字符串,将拼接后的字符串封装在实体类(BO/VO)中,再按JSON格式返回给前台。


这种做法功能是实现了,但是多调用了一次数据库连接,多写了一个DAO方法,多写了一个ORM持久层方法。


把问题交给pl/sql程序员怎么样呢?

pl/sql程序员好像也没有更好的方法,单句sql不好实现,为每个这个的功能分别写存储过程代价也很大。



解决方案


本文要做的就是单句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函数,代码如下:




测试



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函数联合使用,需要构建两张表。

为了方便理解,我们构建一下业务场景。


构建业务场景


(本业务场景纯属虚构,如有雷同纯属巧合)


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

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

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


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

由于种种原因吧,我们现在要说的不是一般的设计,而是直接在书籍表怎么作者属性,取值为作者表主键,但存在多个作者时用','分隔。


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



与listagg函数联合查询:




注意:listagg是oracle 11.2版本的新功能。


至此,便简单实现了oracle的字符拆分与拼接。


如何加入"云和恩墨大讲堂"微信群

搜索 盖国强(Eygle) :eyygle,或者扫描下面二维码,备注:云和恩墨大讲堂,即可入群。每周与千人共享免费技术分享,与讲师在线讨论。



近期文章

分区剪裁特性剖析

oracle标量子查询和表连接改写

利用DMU修改数据库字符集 

UPDATE GLOBAL_NAME为空之后的恢复

深入剖析 ORA-04031 的前世今生

Database Link与GLOBAL_NAMES参数的关系



资源下载

(OraNews)回复关键字获取

2016DTCC, 2016数据库大会PPT;

DBALife,"DBA的一天"精品海报大图;

12cArch,“Oracle 12c体系结构”精品海报;

DBA01,《Oracle DBA手记》第一本下载;

YunHe“云和恩墨大讲堂”案例文档下载;





文章转载自李伟,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论