oracle 12C wmsys.wm_concat()函式
對於一些業務,需要連線函式把內容拼接文字檔案的時候,藉助合適的函式,非常重要,減少很多工作。
目前常用的連線函式有
wmsys.wm_concat()和
LISTAGG(
)函式,當然還有看拼接內容的長度來選。
oracle資料庫中,還有一個根據版本選擇。最新的兩個版本中,11G中,自帶有兩個函式,但在12C中,
oracle不再自帶
wmsys.wm_concat(),如果實際業務中需要到,需要自己建立上。當然,如何建立,會在本文的最後,
會提供兩個版本的
wmsys.wm_concat()和
wmsys.wm_concat()函式沿用過來。
很多人說在12C中,
)函式
就已經夠用了,但是在服務過的客戶的應用中,該函式是遠遠不夠使用的,連版本1的
wmsys.wm_concat()函式都不夠用,
不能滿足業務應用的大量拼接業務的使用。
----建立測試表:
--測試表結構:
SQL> set lines 80
SQL> desc suxing.WMCONCAT_TAB
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(20)
TEL VARCHAR2(20)
INSERT_DATE DATE
--測試表記錄數:
SQL> select distinct count(*) from suxing.WMCONCAT_TAB;
COUNT(*)
----------
262144
--測試表內容:
SQL> select distinct * from suxing.WMCONCAT_TAB;
NAME TEL INSERT_DA
-------------------- -------------------- ---------
suxing1 18777104737 27-NOV-17
suxing7 18777104733 27-NOV-17
suxing2 18777104738 27-NOV-17
suxing3 18777104739 27-NOV-17
suxing5 18777104731 27-NOV-17
suxing0 18777104736 27-NOV-17
suxing4 18777104730 27-NOV-17
suxing6 18777104732 27-NOV-17
8 rows selected.
##該表共26w多條記錄,其中是以上8條記錄的重複記錄。三個欄位,分別為名字、11位號碼和輸入日期。
----使用不同函式或者不同版本查詢拼接表
LISTAGG(
)函式:
SELECT name, LISTAGG(TEL, ',') WITHIN GROUP (ORDER BY TEL) AS pho_lists
FROM suxing.WMCONCAT_TAB
GROUP BY name;
##直接報錯,拼接內容過長,超出該函式的儲存長度varchar2(4000
)
。
--使用版本1的
wmsys.wm_concat()函式:
SELECT name, wm_concat(TEL) AS pho_lists
FROM suxing.WMCONCAT_TAB
GROUP BY name;
##同樣報內部的拼接內容的長度超出了內部類的儲存長度pl/sql
varchar2
(32767
)
。
wmsys.wm_concat()函式
:
--
wmsys.wm_concat()函式
:
改版本的函式,拼接的內容是以varchar2(32767
)資料型別的文字形式儲存。
一.解鎖wmsys使用者
alter user wmsys identified by "XXXXXX" account unlock;
--
建立包、包體和函式
以wmsys使用者登入資料庫,執行下面的命令
CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT
-- AUTHID CURRENT_USER AS OBJECT
(
CURR_STR VARCHAR2(32767),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER
);
/
--定義型別body:
CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
SCTX := WM_CONCAT_IMPL(NULL) ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
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,
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,
SCTX2 IN WM_CONCAT_IMPL)
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(P1 VARCHAR2)
RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL ;
/
--建立同義詞並授權:
[sql] view plain copy
create public synonym WM_CONCAT_IMPL for wmsys.WM_CONCAT_IMPL
/
create public synonym wm_concat for wmsys.wm_concat
/
grant execute on WM_CONCAT_IMPL to public
/
grant execute on wm_concat to public
/
版本2的
:
該版本的函式,拼接的內容是以clob
(4G
<span style="white-space:normal;font-family:;" "="">)資料型別的文字形式儲存。
alter user wmsys identified by "Test_2017" account unlock;
--建立包、包體和函式:
以wmsys使用者登入資料庫,執行下面的命令
CREATE OR REPLACE TYPE wm_concat_impl AUTHID CURRENT_USER AS OBJECT
(
CURR_STR VARCHAR2(32767),
CURR_STR_C CLOB,
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF WM_CONCAT_IMPL, RETURNVALUE OUT CLOB, FLAGS NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 WM_CONCAT_IMPL) RETURN NUMBER
);
/
--定義型別body:
CREATE OR REPLACE TYPE BODY wm_concat_impl IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER IS
BEGIN
SCTX := WM_CONCAT_IMPL(NULL,NULL) ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 VARCHAR2) RETURN NUMBER IS
BEGIN
IF (CURR_STR_C IS NULL AND (CURR_STR IS NULL OR LENGTH(CURR_STR)<29950)) THEN
IF(CURR_STR IS NOT NULL) THEN
CURR_STR := CURR_STR || ',' || P1;
ELSE
CURR_STR := P1;
END IF;
ELSE
IF (CURR_STR_C IS NULL) THEN
CURR_STR_C := CURR_STR ;
CURR_STR := NULL ;
END IF ;
CURR_STR_C := CURR_STR_C || ',' || P1;
END IF ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF WM_CONCAT_IMPL, RETURNVALUE OUT CLOB, FLAGS NUMBER) RETURN NUMBER IS
BEGIN
IF (CURR_STR IS NOT NULL) THEN
RETURNVALUE := CURR_STR ;
ELSE
RETURNVALUE := CURR_STR_C ;
END IF ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 WM_CONCAT_IMPL) 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(P1 VARCHAR2)
RETURN CLOB AGGREGATE USING WM_CONCAT_IMPL ;
/
--建立同義詞並授權:
[sql] view plain copy
create public synonym WM_CONCAT_IMPL for wmsys.WM_CONCAT_IMPL
/
create public synonym wm_concat for wmsys.wm_concat
/
grant execute on WM_CONCAT_IMPL to public
/
grant execute on wm_concat to public
/
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31392094/viewspace-2149577/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【ORACLE12C】oracle 12C wmsys.wm_concat()函式Oracle函式
- wmsys.wm_concat 函式用法函式
- WMSYS.WM_CONCAT 函式的用法 ZT函式
- Oracle - 分組連線欄位函式WMSYS.WM_CONCAT的使用Oracle函式
- Oracle 12c中的SQL/JSON函式OracleSQLJSON函式
- Oracle 12c新特性之:APPROX_COUNT_DISTINCT 函式OracleAPP函式
- 【轉】Oracle: wmsys.wm_concat、sys_connect_by_path、自定義函式實現行列轉換Oracle函式
- 【函式】ORACLE函式大全函式Oracle
- Oracle聚合函式/分析函式Oracle函式
- Oracle 12C R2-新特性-轉換函式的增強Oracle函式
- 【函式】Oracle EXTRACT()函式與to_char() 函式函式Oracle
- oracle 函式Oracle函式
- oracle or 函式Oracle函式
- Oracle函式Oracle函式
- 【函式】Oracle函式系列(2)--數學函式及日期函式函式Oracle
- 【函式】oracle nvl2 函式函式Oracle
- Oracle 函式大全(字串函式,數學函式,日期函式,邏輯運算函式,其他函式)Oracle函式字串
- Oracle分析函式與視窗函式Oracle函式
- oracle函式大全-字串處理函式Oracle函式字串
- Oracle分析函式七——分析函式案例Oracle函式
- 7 Oracle 函式Oracle函式
- Oracle 字串函式Oracle字串函式
- Oracle 字串函式Oracle字串函式
- ORACLE USERENV函式Oracle函式
- oracle dump 函式Oracle函式
- oracle Extract 函式Oracle函式
- Oracle字串函式Oracle字串函式
- ORACLE 常用 函式Oracle函式
- oracle函式索引Oracle函式索引
- Oracle dump函式Oracle函式
- Oracle 分析函式Oracle函式
- oracle reverse函式Oracle函式
- oracle字元函式Oracle字元函式
- oracle 常用函式Oracle函式
- Oracle 函式大全Oracle函式
- oracle函式大全Oracle函式
- Oracle Trunc函式Oracle函式
- Oracle分析函式Oracle函式