|
在软件开发过程中程序员经常会遇到字符串的拼接和拆分工作。 以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函数，代码如下： 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.基本功能 2.字符转数字+默认分隔符 SQL> select to_number(column_value) from table(split('1001,1002,1003')); SQL> select column_value from table(split('1001@#1002@#1003','@#'));
为了方便理解，我们构建一下业务场景。 有一张作者表，记录作者的个人信息（如：姓名、年龄等），主键是序列号生成的。 有一张书籍表，记录书籍的信息（如：书名、出版社、作者等），主键是序列号生成的。 一个作者可能写过多本书，一个书可能由多个作者联合编著。 对于多对多的情况，一般的设计原则是增加多对多关系表，用于记录书籍表主键和作者表主键。 由于种种原因吧，我们现在要说的不是一般的设计， 而是直接在书籍表怎么作者属性，取值为作者表主键，但存在多个作者时用','分隔。 好的。业务场景描述清楚了，现在开始建表和初始化数据。 create table author 与listagg函数联合查询： select b_id,b_name,a_id, 注意：listagg是oracle 11.2版本的新功能。
|
|
|
