標籤:
在軟體開發過程中程式員經常會遇到字串的拼接和拆分工作。
以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(‘[email protected]#[email protected]#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版本的新功能。
oracle pl/sql split函數