在軟件開發(fā)過程中程序員經(jīng)常會遇到字符串的拼接和拆分工作。

以java開發(fā)為例：

前臺傳入字符串拼接形式的一個(gè)JSON數(shù)據(jù)，如："1001,1002,1003"，這可能代表了一組序號。

程序員需要將序號轉(zhuǎn)名稱后按照相同的格式輸出，如：“張三、李四、王五”。

Java程序員通用的做法是在service層將接收的"1001,1002,1003"拆分（使用java split函數(shù)），然后封裝List，將List傳遞給DAO，

再傳遞給ORM持久層的xml調(diào)用sql執(zhí)行，sql的返回結(jié)果用List接收，并在service層遍歷List和拼接字符串，

將拼接后的字符串封裝在實(shí)體類(BO/VO)中，再按JSON格式返回給前臺。

這種做法功能是實(shí)現(xiàn)了，但是多調(diào)用了一次數(shù)據(jù)庫連接，多寫了一個(gè)DAO方法，多寫了一個(gè)ORM持久層方法。

把問題交給pl/sql程序員怎么樣呢？

pl/sql程序員好像也沒有更好的方法，單句sql不好實(shí)現(xiàn)，為每個(gè)這個(gè)的功能分別寫存儲過程代價(jià)也很大。

本文要做的就是單句SQL實(shí)現(xiàn)該功能。

先分析一下,該業(yè)務(wù)有兩個(gè)關(guān)鍵點(diǎn)。

一是字符串拼接，oracle（11.2）提供了listagg函數(shù)已經(jīng)實(shí)現(xiàn)了該功能，我們直接使用就可以。

二是字符串拆分，oracle沒有實(shí)現(xiàn)該功能，但是java提供了split函數(shù)實(shí)現(xiàn)了字符串拆分功能。

我們可以參考java的split函數(shù)寫一個(gè)oracle版split函數(shù)。

split函數(shù)的功能是將字符串按照特定字符分隔為多個(gè)小字符串，返回結(jié)果以List或數(shù)組類型保存。

先創(chuàng)建一個(gè)type類型，代碼如下：

create or replace type type_str is table of varchar2(100);

再創(chuàng)建split函數(shù)，代碼如下：

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.字符轉(zhuǎn)數(shù)字+默認(rèn)分隔符

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

單個(gè)split函數(shù)測試成功了，和listagg函數(shù)聯(lián)合使用，需要構(gòu)建兩張表。

為了方便理解，我們構(gòu)建一下業(yè)務(wù)場景。
構(gòu)建業(yè)務(wù)場景（本業(yè)務(wù)場景純屬虛構(gòu)，如有雷同純屬巧合）：

有一張作者表，記錄作者的個(gè)人信息（如：姓名、年齡等），主鍵是序列號生成的。

有一張書籍表，記錄書籍的信息（如：書名、出版社、作者等），主鍵是序列號生成的。

一個(gè)作者可能寫過多本書，一個(gè)書可能由多個(gè)作者聯(lián)合編著。

對于多對多的情況，一般的設(shè)計(jì)原則是增加多對多關(guān)系表，用于記錄書籍表主鍵和作者表主鍵。

由于種種原因吧，我們現(xiàn)在要說的不是一般的設(shè)計(jì)，

而是直接在書籍表怎么作者屬性，取值為作者表主鍵，但存在多個(gè)作者時(shí)用','分隔。

好的。業(yè)務(wù)場景描述清楚了，現(xiàn)在開始建表和初始化數(shù)據(jù)。

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函數(shù)聯(lián)合查詢：

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；

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