【函式】wm_concat包的訂製
【函式】wm_concat包的訂製
1 BLOG文件結構圖
2 前言部分
2.1 導讀和注意事項
各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：
① 利用系統包建立WM_CONCAT函式（重點）
② ORA-00904: "wm_concat":invalid identifier錯誤解決
③ 訂製自己的WM_CONCAT函式
④ listagg分析函式的使用
⑤ ORA-01489: result of string concatenation is too long的錯誤解決
Tips：
① 本文在itpub（http://blog.itpub.net/26736162）、部落格園(http://www.cnblogs.com/lhrbest)和微信公眾號（xiaomaimiaolhr）有同步更新。
② 文章中用到的所有程式碼，相關軟體，相關資料請前往小麥苗的雲盤下載（http://blog.itpub.net/26736162/viewspace-1624453/）。
③ 若網頁文章程式碼格式有錯亂，推薦使用360瀏覽器，也可以下載pdf格式的文件來檢視，pdf文件下載地址：http://blog.itpub.net/26736162/viewspace-1624453/，另外itpub格式顯示有問題，也可以去部落格園地址閱讀。
④ 本篇BLOG中命令的輸出部分需要特別關注的地方我都用灰色背景和粉紅色字型來表示，比如下邊的例子中，thread 1的最大歸檔日誌號為33，thread 2的最大歸檔日誌號為43是需要特別關注的地方；而命令一般使用黃色背景和紅色字型標注；對程式碼或程式碼輸出部分的注釋一般採用藍色字型表示。
|
List of Archived Logs in backup set 11 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48 1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58 2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49 2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53 [ZHLHRDB1:root]:/>lsvg -o T_XLHRD_APP1_vg rootvg [ZHLHRDB1:root]:/> 00:27:22 SQL> alter tablespace idxtbs read write; ====》2097152*512/1024/1024/1024=1G |
本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。
2.2 相關參考文章連結
行轉列參考文章：http://blog.itpub.net/26736162/viewspace-1272538/
2.3 本文簡介
WMSYS使用者下的WM_CONCAT函式有很重要的用途，比如行轉列（http://blog.itpub.net/26736162/viewspace-1272538/），但是該函式不穩定，在10G和11GR2上返回值不同，一個是字串一個是CLOB，而且12C上已經摒棄了WM_CONCAT函式，但是我們很多程式設計師在程式中使用了該函式，若是系統升級就會導致程式出現錯誤，為了減輕程式設計師修改程式的工作量，只有建立這個WM_CONCAT函式來解決該問題。
一般情況下報錯資訊，ORA-00904: "wm_concat":invalid identifier，查詢DBA_OBJECTS檢視，也未發現wm_concat的相關資訊。正常情況下查詢，
SQL> SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE 'WM_CONCAT%';
應如下所示：
解決辦法有2種，一種是採用Oracle本身的指令碼來建立WM_CONCAT函式，一種是採用自己建立的函式來完成這個功能。
3 用Oracle自帶指令碼重建WMSYS使用者的WMSYS.WM_CONCAT函式
執行如下指令碼解除安裝WMSYS：
@$ORACLE_HOME/rdbms/admin/owmuinst.plb
執行如下指令碼執行安裝WMSYS：
@$ORACLE_HOME/rdbms/admin/owminst.plb
解鎖wmsys使用者：
ALTER USER WMSYS ACCOUNT UNLOCK;
4 自己建立wmsys
4.1 訂製指令碼
若只是某個使用者使用，那麼我們可以不用刻意去建立wmsys使用者，可以在當前使用者下執行指令碼，生成WM_CONCAT函式，為了和系統的函式名區別開來，我們也可以修改函式名稱，訂製自己的指令碼。
4.1.1 無分隔符，返回CLOB
建立函式的指令碼如下：
|
CREATE OR REPLACE TYPE WM_CONCAT_IMPL_CLOB_NULL_LHR AUTHID CURRENT_USER AS OBJECT ( CURR_STR CLOB, STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR, P1 IN CLOB) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL_CLOB_NULL_LHR, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR, SCTX2 IN WM_CONCAT_IMPL_CLOB_NULL_LHR) RETURN NUMBER ); / CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_CLOB_NULL_LHR IS STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR) RETURN NUMBER IS BEGIN SCTX := WM_CONCAT_IMPL_CLOB_NULL_LHR(NULL); RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR, P1 IN CLOB) RETURN NUMBER IS BEGIN IF (CURR_STR IS NOT NULL) THEN CURR_STR := CURR_STR || P1; ELSE CURR_STR := P1; END IF; RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL_CLOB_NULL_LHR, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER IS BEGIN RETURNVALUE := CURR_STR; RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR, SCTX2 IN WM_CONCAT_IMPL_CLOB_NULL_LHR) RETURN NUMBER IS BEGIN IF (SCTX2.CURR_STR IS NOT NULL) THEN SELF.CURR_STR := SELF.CURR_STR || SCTX2.CURR_STR; END IF; RETURN ODCICONST.SUCCESS; END; END; /
CREATE OR REPLACE FUNCTION WM_CONCAT_CLOB_NULL_LHR(P1 VARCHAR2) RETURN CLOB AGGREGATE USING WM_CONCAT_IMPL_CLOB_NULL_LHR; /
CREATE PUBLIC SYNONYM WM_CONCAT_CLOB_NULL_LHR FOR WM_CONCAT_CLOB_NULL_LHR; GRANT EXECUTE ON WM_CONCAT_CLOB_NULL_LHR TO PUBLIC; |
測試案例，注意函式的返回值是無分隔符的CLOB，在PL/SQL中注意使用to_char進行轉換：
|
SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
USER_ID ---------- 0 5
SYS@lhrdb21> SELECT WM_CONCAT_CLOB_NULL_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
WM_CONCAT_CLOB_LHR_NULL(D.USER_ID) -------------------------------------------------------------------------------- 05
SYS@lhrdb21> |
4.1.2 逗號分隔符，返回CLOB
建立函式的指令碼如下：
|
CREATE OR REPLACE TYPE WM_CONCAT_IMPL_CLOB_LHR AUTHID CURRENT_USER AS OBJECT ( CURR_STR CLOB, STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_LHR) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_LHR, P1 IN CLOB) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL_CLOB_LHR, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL_CLOB_LHR, SCTX2 IN WM_CONCAT_IMPL_CLOB_LHR) RETURN NUMBER ); / CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_CLOB_LHR IS STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_LHR) RETURN NUMBER IS BEGIN SCTX := WM_CONCAT_IMPL_CLOB_LHR(NULL); RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_LHR, P1 IN CLOB) RETURN NUMBER IS BEGIN IF (CURR_STR IS NOT NULL) THEN CURR_STR := CURR_STR || ',' || P1; ELSE CURR_STR := P1; END IF; RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL_CLOB_LHR, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER IS BEGIN RETURNVALUE := CURR_STR; RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL_CLOB_LHR, SCTX2 IN WM_CONCAT_IMPL_CLOB_LHR) RETURN NUMBER IS BEGIN IF (SCTX2.CURR_STR IS NOT NULL) THEN SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR; END IF; RETURN ODCICONST.SUCCESS; END; END; /
CREATE OR REPLACE FUNCTION WM_CONCAT_CLOB_LHR(P1 VARCHAR2) RETURN CLOB AGGREGATE USING WM_CONCAT_IMPL_CLOB_LHR; /
CREATE PUBLIC SYNONYM WM_CONCAT_CLOB_LHR FOR WM_CONCAT_CLOB_LHR; GRANT EXECUTE ON WM_CONCAT_CLOB_LHR TO PUBLIC; |
測試案例，注意函式的返回值是以逗號為分隔符的CLOB，在PL/SQL中注意使用to_char進行轉換：
|
SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
USER_ID ---------- 0 5 SYS@lhrdb21> SELECT WM_CONCAT_CLOB_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
WM_CONCAT_LHR(D.USER_ID) -------------------------------------------------------------------------------- 0,5 |
4.1.3 逗號分隔符，返回字串
建立函式的指令碼如下：
|
CREATE OR REPLACE TYPE WM_CONCAT_IMPL_STRINGS_LHR AUTHID CURRENT_USER AS OBJECT ( CURR_STR VARCHAR2(32767), STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_STRINGS_LHR) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_STRINGS_LHR, P1 IN VARCHAR2) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL_STRINGS_LHR, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL_STRINGS_LHR, SCTX2 IN WM_CONCAT_IMPL_STRINGS_LHR) RETURN NUMBER ); / CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_STRINGS_LHR IS STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_STRINGS_LHR) RETURN NUMBER IS BEGIN SCTX := WM_CONCAT_IMPL_STRINGS_LHR(NULL); RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_STRINGS_LHR, P1 IN VARCHAR2) RETURN NUMBER IS BEGIN IF (CURR_STR IS NOT NULL) THEN CURR_STR := CURR_STR || ',' || P1; ELSE CURR_STR := P1; END IF; RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL_STRINGS_LHR, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS BEGIN RETURNVALUE := CURR_STR; RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL_STRINGS_LHR, SCTX2 IN WM_CONCAT_IMPL_STRINGS_LHR) RETURN NUMBER IS BEGIN IF (SCTX2.CURR_STR IS NOT NULL) THEN SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR; END IF; RETURN ODCICONST.SUCCESS; END; END; /
CREATE OR REPLACE FUNCTION WM_CONCAT_STRINGS_LHR(P1 VARCHAR2) RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL_STRINGS_LHR; /
CREATE PUBLIC SYNONYM WM_CONCAT_STRINGS_LHR FOR WM_CONCAT_STRINGS_LHR; GRANT EXECUTE ON WM_CONCAT_STRINGS_LHR TO PUBLIC; |
測試案例，注意函式的返回值是以逗號為分隔符的字串：
|
SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
USER_ID ---------- 0 5
SYS@lhrdb21> SELECT WM_CONCAT_STRINGS_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
WM_CONCAT_STRINGS_LHR(D.USER_ID) --------------------------------------------------- 0,5 |
5 listagg的使用
這是一個Oracle的列轉行函式：LISTAGG()
with temp as(
select 'China' nation ,'Guangzhou' city from dual union all
select 'China' nation ,'Shanghai' city from dual union all
select 'China' nation ,'Beijing' city from dual union all
select 'USA' nation ,'New York' city from dual union all
select 'USA' nation ,'Bostom' city from dual union all
select 'USA' nation ,'Bostom' city from dual union all
select 'Japan' nation ,'Tokyo' city from dual
)
select nation,listagg(city,',') within GROUP (order by city)
from temp
group by nation;
但是如果聚合的內容太多就會報ORA-01489: result of string concatenation is too long的錯誤，這個時候可以從業務的角度去修改SQL，也可以使用WM_CONCAT函式返回CLOB型別來解決這個問題。如下聚合DBA_OBJECTS中的OBJECT_NAME就會報錯：
SELECT LISTAGG(OBJECT_NAME, ',') WITHIN GROUP(ORDER BY OBJECT_NAME)
FROM DBA_OBJECTS D;
報錯：ORA-01489: result of string concatenation is too long
解決：可以用WM_CONCAT返回CLOB型別即可。SELECT WM_CONCAT_CLOB_LHR(D.OBJECT_NAME) FROM DBA_OBJECTS D;
注意：有關WM_CONCAT函式返回CLOB型別的效能問題，我們本篇文章不討論，聚合的內容多了，自然就慢，到底是避免出ORA-01489錯誤還是要結果，這個還得根據自己的情況權衡決定，比如有的系統tmp很大，隨便用，那作為開發人員，估計才不會考慮這麼多的，不管白貓黑貓，抓住老鼠就是好貓。
About Me
|
............................................................................................................................... ● 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用 ● 本文在itpub（http://blog.itpub.net/26736162）、部落格園(http://www.cnblogs.com/lhrbest)和個人微信公眾號（）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2124931/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest/p/5869463.html ● 本文pdf版： （提取碼：ed9b） ● 小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● QQ群：230161599 微信群：私聊 ● 聯絡我請加QQ好友(642808185)，註明新增緣由 ● 於 2016-09-13 09:00~ 2016-09-13 11:30 在中行完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解！ ● 【版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任】 ............................................................................................................................... 手機長按下圖識別二維碼或微信客戶端掃描下邊的二維碼來關注小麥苗的微信公眾號：xiaomaimiaolhr,免費學習最實用的資料庫技術。
|
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2124931/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- wm_concat函式函式
- oracle wm_concat(column)函式的使用Oracle函式
- wm_concat函式的排序問題函式排序
- wm_concat函式與oracle版本函式Oracle
- WM_CONCAT這函式，別再用了！！！！函式
- 【SQL 分析函式】wm_concat 行列轉換SQL函式
- Oracle ORA-06575: 程式包或函式WM_CONCAT處於無效狀態Oracle函式
- Oracle沒有WM_CONCAT函式的解決辦法Oracle函式
- WM_CONCAT函式在11g上的變化函式
- Oracle的wm_concat和MySQL的group_concat函式OracleMySql函式
- 閉包函式（匿名函式）的理解函式
- js函式 函式自呼叫 返回函式的函式 (閉包)JS函式
- 函式閉包函式
- 閉包函式函式
- 回撥函式 與 函式閉包函式
- JS函式表示式——函式遞迴、閉包JS函式遞迴
- JavaScript進階系列01,函式的宣告，函式引數，函式閉包JavaScript函式
- go 閉包函式Go函式
- js函式閉包JS函式
- PL/SQL 函式 包SQL函式
- 動畫函式的繪製及自定義動畫函式動畫函式
- JS閉包函式和回撥函式JS函式
- 函式物件、裝飾器、閉包函式函式物件
- 列轉行聚合的簡單實現ORACLE WM_CONCAT LISTAGG函式Oracle函式
- python中的閉包函式Python函式
- dbms_xplan 函式包函式
- JS閉包函式概念JS函式
- JavaScript 匿名函式 閉包JavaScript函式
- 第五篇 匿名函式、內建函式、import的使用、包的使用函式Import
- 理解Python函式閉包Python函式
- 匯入jar包，main函式JARAI函式
- js中的函式巢狀和閉包JS函式巢狀
- 一個閉包函式的簡單例子函式單例
- C++再議建構函式及複製建構函式深度複製C++函式
- 一個常見的閉包函式的分析函式
- swift1.2語言函式和閉包函式介紹Swift函式
- C++模板的定製一：定製函式模板 (轉)C++函式
- C/C++——建構函式、複製建構函式和解構函式的執行時刻C++函式