Oracle多行轉換成字串方法總結

xccheese發表於2012-02-10
Oracle字串
當我們在做一些比較複雜的資料匯出時，可能會經常遇到要將不固定的多行資料組合成一個字串返回；ISVP中就遇到了類似的情況，要求對於每一個APP，返回他所屬的所有類目名稱，類目名稱之間用,隔開；對於該型別的問題，總結一下大致有如下幾種常見方案；在具體陳述實現方案之前，我們先介紹下我們即將操作的表結構：
  1. SQL> desc app_category_link;
    Name                 Type         Nullable Default Comments      
    -------------------- ------------ -------- ------- ------------- 
    APP_CATEGORY_LINK_ID VARCHAR2(20)                  主關鍵        
    APP_ID               VARCHAR2(20)                  應用ID        
    APP_CATEGORY_ID      VARCHAR2(20)                  應用類別ID   

其中欄位APP_ID和APP_CATEGORY_ID是一對多關係；

方案1：sys_connect_by_path + start with ... connect by ... prior + 分析函式

從上面的這個公式中我們可以看出，該方案主要是通過分析函式和父子級聯查詢來完成，一般是一條SQL搞定，比較省事；首先來看幾個具體實現SQL；
具體實現1：

  1. SELECT app_id,
  2.        ltrim(max(sys_connect_by_path(app_category_id, ',')), ',') categ_ids
  3.   FROM (SELECT app_id,
  4.                app_category_id,
  5.                app_category_id || '|' || rn rchild,
  6.                app_category_id || '|' || (rn - 1) rfather
  7.           FROM (SELECT app_id,
  8.                        app_category_id,
  9.                        row_number() over(PARTITION BY app_id ORDER BY app_category_id) rn
  10.                   FROM app_category_link))
  11.  START WITH rfather LIKE '%|0'
  12. CONNECT BY PRIOR rchild = rfather
  13.  GROUP BY app_id;

具體實現2：

  1. select app_id,
  2.        ltrim(max(sys_connect_by_path(app_category_id, ',')), ',') categ_ids
  3.   from (select t.app_id,
  4.                t.app_category_id,
  5.                min(t.app_category_id) over(partition by app_id) categ_min,
  6.                (row_number() over(order by app_id, app_category_id)) +
  7.                (dense_rank() over(order by app_id)) numid
  8.           from app_category_link t)
  9.  start with app_category_id = categ_min
  10. connect by numid - 1 = prior numid
  11.  group by app_id;

具體實現3：

  1. select app_id,
  2.        ltrim(max(sys_connect_by_path(app_category_id, ',')), ',') categ_ids
  3.   from (select t.app_id,
  4.                t.app_category_id,
  5.                (row_number()
  6.                 over(partition by app_id order by app_category_id)) numid
  7.           from app_category_link t)
  8.  start with numid = 1
  9. connect by numid - 1 = prior numid
  10.        and app_id = prior app_id
  11.  group by app_id;

具體實現4：

  1. select app_id,
  2.        ltrim(sys_connect_by_path(app_category_id, ','), ',') categ_ids
  3.   from (select t.app_id,
  4.                t.app_category_id,
  5.                (row_number()
  6.                 over(partition by app_id order by app_category_id)) numid
  7.           from app_category_link t)
  8.  WHERE connect_by_isleaf = 1
  9.  start with numid = 1
  10. connect by numid - 1 = prior numid
  11.        and app_id = prior app_id;

請注意看4種實現方式的區別，下面分別介紹下這4種實現方式的具體思路；
第1種實現採用了1個分析函式、2次子查詢、一個like、以及父子級聯查詢欄位值連線；可以猜測下效能肯定不咋的，2次子查詢本來已經很耗時了，對查詢出來的結果集還要用like匹配，速度就更慢了，此法可以查詢到我們需要的具體資料，但是效率很低，不可取；他的實現思路是利用待查詢欄位值與各APP下面各類目ID的序列值進行組合，並作為父子關係級聯的依據；
第2種實現採用了3個分析函式、1次全表掃描、以及父子級聯欄位值連線；和第1種實現比較而言的話效率會高不少；他的實現思路是利用各APP對應的最小類目ID作為父子級聯的開始點，而父子級聯的依據是row_number()+dense_rank()，這樣做主要是為了避免無限迴圈；
第3、4兩種實現思路基本上是一樣的，都是1個分析函式、1次全表掃描、以及父子級聯欄位值連線；從程式碼長度來說，比前2種實現方式簡潔了不少，思路也清晰了很多，直接利用各APP對應類目ID的序列值作為父子級聯的開始點和連線依據；但仔細看看兩者的SQL，會發現第3這種方式用到了group by子句，而第4種實現卻沒有用到，而是在where子句中新增了connect_by_isleaf = 1 的查詢條件；從效能上來看，應該是第4種實現方式更高，但他只能在10g及其以後的版本中才能使用，connect_by_isleaf 欄位是10g中新提供的一個偽列，他可以用來判斷該條記錄是否是樹形記錄的葉節點，不過還在用9i版本的可能就有些可惜了；
綜合以上分析，對4種實現方案，個人推薦使用第3、4兩種實現方式，具體哪種可以看所用oracle的版本而定，簡而言之，這種實現方式優雅、簡潔、高效；

方案2：自定義Function/SP

在Oracle中提供了非常強大的自定義Function/SP功能，對於該需求，如果說大家覺得方案1中的SQL太長太複雜，那完全可以考慮這種方案；但是和java開發一樣，相同的功能不同的人員來實現都會有不同的效果，程式碼的質量也都會不一樣；下面我們就從不同方面來介紹幾種function的具體實現方式；
實現1：最簡單明瞭、但可擴充套件性極差；對於我們需要的該表app_category_link寫一個特定的function，傳入app_id，然後在function內部查詢出該app_id對應的所有類目ID資訊，然後對結果集迴圈，最後將連線成的字串返回；若有很多類似的表都有這樣的查詢，就得寫多個對應的function，所以擴充套件性極差，但卻最簡單、有效；
實現2：實現較複雜、但可擴充套件性較好；這種實現方式從實現1演變而來，主要是為了解決多表問題；需要將待查詢表名、待查詢欄位key-value、需連線的欄位名 作為引數傳入function，function內部會組裝這些引數並形成select語句，查詢並對返回結果集迴圈連線；
實現3：實現簡單、可擴充套件性好；不會吧！魚和熊掌能夠兼得？呵呵，當然可以，實現2中的思路值得借鑑，但是擴充套件性仍然不夠，因為function內部需要組裝查詢sql，當然最好的方式是分工明確，function中只針對結果集進行迴圈連線，而不需要關心結果集是如何得來的，這和實際java開發有異曲同工之妙！
針對以上3種實現方式，很明顯實現3佔據幾乎所有的優勢，推薦採用；以下貼一段參考程式碼；
function實現：

  1. create or replace type strings_table is table of varchar2(20);
  2. /
  3. create or replace function mymerge (pv in strings_table) return varchar2
  4. is
  5.   ls varchar2(4000);
  6. begin
  7.   for i in 1..pv.count loop
  8.     ls := ls || ',' || pv(i);
  9.   end loop;
  10.   return ls;
  11. end;
  12. /

具體呼叫function的SQL實現：

  1. select t0.app_id,
  2.        mymerge(cast(multiset (select t.app_category_id
  3.                        from app_category_link t
  4.                       where t.app_id = t0.app_id) as strings_table)) categ_ids
  5.   from (select distinct app_id from app_category_link) t0;

方案3：Oracle 10g + wmsys.wm_concat

可能我們提到的這個功能太普遍了，oracle在10g及其以上版本提供了一個內建函式，可以搞定類似需求，用了這個function之後程式碼變得異常簡單，只能說太優雅嘍；具體參見如下SQL：

  1. select app_id, wmsys.wm_concat(app_category_id) categ_ids
  2.   from app_category_link t
  3.  group by t.app_id;

注意：該函式好像不支援對待查詢欄位排序，也就是說無法對app_category_id連線的順序進行指定；若要完成該功能，只能利用子查詢先用order by排序好，然後再呼叫該函式；
另外，由於該函式是加密過的，看不到原始碼，所以不知道function內部具體是如何實現的，是不是採用我們上面方案2中所提到的某一種呢？
其實在10g裡面，還有一個MODEL SQL，也可以實現類似的功能，不過我還沒有研究過，暫時略過，有興趣的同學可以先研究研究；

上面提到了很多的針對不固定行轉換成字串的實現方案，從各方案的比較來看，當然推薦使用者升級到10g，這樣就有很多的高階功能可以使用了；不過在現實需求中，還有一些是固定行轉換成字串，對於這種需求想象下，實現應該會更簡單，呵呵，但實際效果卻並沒有我們想象中那麼好，其實這些實現都是人肉級的查詢，那都有哪些實現方式呢？
其一，MAX + DECODE / CASE：該組合經常被用於固定行轉換成固定多列，我們只要稍微變化下就可以滿足我們的固定行轉換成字串的需求了，用 || 或者concat函式將各列連線起來即可；
其二，ROW_NUMBER + LEAD：這種組合主要是利用分析函式來實現，因為分析函式可以很容易的提取到當前記錄之前或者之後N條記錄的值，所以當我們明確知道有多少行需要連線組合時，就可以利用LEAD這個分析函式來搞定；

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25148509/viewspace-716037/，如需轉載，請註明出處，否則將追究法律責任。

相關文章