在軟件開發過程中程序員經常會遇到字符串的拼接和拆分工作。
以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.基本功能
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；
注意：listagg是oracle 11.2版本的新功能。
