在軟件開發(fā)過程中程序員經(jīng)常會(huì)遇到字符串的拼接和拆分工作。
以java開發(fā)為例：
前臺(tái)傳入字符串拼接形式的一個(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格式返回給前臺(tái)。
這種做法功能是實(shí)現(xiàn)了，但是多調(diào)用了一次數(shù)據(jù)庫連接，多寫了一個(gè)DAO方法，多寫了一個(gè)ORM持久層方法。
把問題交給pl/sql程序員怎么樣呢？
pl/sql程序員好像也沒有更好的方法，單句sql不好實(shí)現(xiàn)，為每個(gè)這個(gè)的功能分別寫存儲(chǔ)過程代價(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版本的新功能。
