巧用SQL:oracle pl/sql split函数

李伟

专注于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的字符拆分与拼接。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-08-24

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏java一日一条

编写高质量代码的思考

最近在看《代码大全》,可以说是一本软件开发的百科全书,特别厚,但是干货也很多。平时写代码,代码规范是一个最低的要求(很多老代码连最低要求都达不到),为什么要这样...

832
来自专栏灯塔大数据

塔秘 | Python 2.7即将停止支持,请收下这份3.x迁移指南

前言 目前,Python 科学栈中的所有主要项目都同时支持 Python 3.x 和 Python 2.7,不过,这种情况很快即将结束。 去年 11 月,Num...

3519
来自专栏PHP在线

PHP 代码规范简洁之道

原文出处: Scholer 1. 统一的编码规范 编码规范往简单说其实就是三个方面: 换行 空格 变量命名 放在 PHP 里面,还有一些附加的地方,比如关键字...

3856
来自专栏Crossin的编程教室

【Python 第34课】 break

我们已经熟悉了循环的使用,包括while和for...in。while循环在条件不满足时结束,for循环遍历完序列后结束。如果在循环条件仍然满足或序列没有遍历完...

27810
来自专栏WindCoder

网易MySQL微专业学习笔记(六)-内置函数

这个系列属于个人学习网易云课堂MySQL数据库工程师微专业的相关课程过程中的笔记,本篇为其“MySQL数据库对象与应用”中的MySQL数据类型相关笔记。

561
来自专栏机器学习算法与Python学习

python初学者的建议

Python是一种非常富有表现力的语言。它为我们提供了一个庞大的标准库和许多内置模块,帮助我们快速完成工作。然而,许多人可能会迷失在它提供的功能中,不能充分利用...

42112
来自专栏java一日一条

编写高质量代码的思考

最近在看《代码大全》,可以说是一本软件开发的百科全书,特别厚,但是干货也很多。平时写代码,代码规范是一个最低的要求(很多老代码连最低要求都达不到),为什么要这样...

832
来自专栏架构师之旅

轻松学习设计模式之面向对象的设计原则

对于面向对象软件系统的设计而言,在支持可维护性的同时,提高系统的可复用性是一个至关重要的问题,如何同时提高一个软件系统的可维护性和可复用性是面向对象设计需要解决...

863
来自专栏Linyb极客之路

工作流引擎之activiti6实时流程图追踪

有接触过activiti的朋友可能知道,在activiti5.x的追踪流程节点查找,可以用

1.6K2
来自专栏企鹅号快讯

Python 相较于Java 而言,有什么优势?

最近在后台,有很多的同学私聊我说,Python和Java.,哪个具有前景呢?今天我们就来简单的讲一下这两者的区别: 我认为C, Java跟Python都是非常成...

2246

扫码关注云+社区