oracle pl/sql split函數

標籤：

在軟體開發過程中程式員經常會遇到字串的拼接和拆分工作。

以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函數