专栏首页数据和云巧用SQL:oracle pl/sql split函数

巧用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),作者:李伟

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

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

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 巧用SQL:Oracle中实现split相关方法总结

    尚世波 从事数据库方面工作多年,专注于pl/sql开发、数据库设计、优化方面的研究,喜欢挑战 前文回顾:巧用SQL:oracle pl/sql split函...

    数据和云
  • 一波三折:DBA需要头脑冷清思路清晰解决故障以幸存

    杨廷琨(yangtingkun) 云和恩墨 CTO 高级咨询顾问,Oracle ACE总监,ITPUB Oracle数据库管理版版主 这是一则生产环境的真实维护...

    数据和云
  • 【安全警告】Oracle 12c 多租户的SQL注入高危风险防范

    在使用Oracle多租户选件时,由于Container容器和PDB融合共存,则权限控制必将更加重要,在之前的文章中我们提到,Oracle 12.2 的 loc...

    数据和云
  • Glide三问—虾皮真题

    说到图片加载框架,第一个想到的自然就是Glide,但是你真的了解它吗?如果面试问到相关问题你能顺利答出来吗?

    码上积木
  • 三步构造零信任安全网络

    随着移动设备涌入企业,物联网(IoT)的扩张,以及网络罪犯数量和复杂程度的增长,许多安全专家认为零信任是抵御不断变化网络和数据安全威胁的最好方法。

    FB客服
  • NeurIPS 2019 神经网络压缩与加速竞赛双项冠军技术解读

    日前,神经信息处理系统大会(NeurIPS2019)于12月8日至14日在加拿大温哥华举行,中国科学院自动化研究所及其南京人工智能芯片创新研究院联合团队在本次大...

    AI研习社
  • top k frequent words(前K个高频单词)

    返回的答案应该按单词出现频率由高到低排序。如果不同的单词有相同出现频率,按字母顺序排序。

    小歪
  • EF基础知识小记二

    1、EF的常用使用场景 (1)、维护一个已经存在的数据库,VS提供了工具帮助我们把数据库中的表和视图等对象导入到实体框架.        [数据库=>模型(Da...

    郑小超.
  • 蓝桥杯 java 序列求和

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 ...

    逝兮诚
  • 【JavaWeb】67:一张只有程序员能看懂的图片

    我们用的很多软件,都有一个用户名和密码,用户的很多数据都是被存在该软件服务器里面的。

    刘小爱

扫码关注云+社区

领取腾讯云代金券