oracle pl/sql split函数

张兴康

在实际工作中我们

经常会遇到字符串的拼接和拆分工作。

本文要做的就是单句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；

张兴康

fteair

fteair

fteair

fteair

张兴康