oracle pl/sql split函數

來源:互聯網
上載者:User

標籤:

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

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

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.