Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Oracle 12C wmsys.wm_concat () function

2025-01-25 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

For some businesses, when you need a connection function to splice the content into a text file, it is very important and reduces a lot of work with the help of the appropriate function.

At present, the commonly used connection functions are wmsys.wm_concat () and LISTAGG () functions, and of course, depending on the length of the splicing content to choose.

In the oracle database, there is another one that is selected based on version. In the latest two versions, 11G comes with two functions, but in 12C

Oracle no longer comes with wmsys.wm_concat (). If you need it in the actual business, you need to create it yourself. Of course, how to create it will be at the end of this article

Two versions of the wmsys.wm_concat () and wmsys.wm_concat () functions are provided. Many people say that in 12C,) function

It is enough, but in the applications of customers who have been served, this function is far from enough, even the version 1 wmsys.wm_concat () function is not enough.

It can not meet the use of a large number of splicing services for business applications.

-create a test table:

-- Test table structure:

SQL > set lines 80

SQL > desc suxing.WMCONCAT_TAB

Name Null? Type

-

NAME VARCHAR2 (20)

TEL VARCHAR2 (20)

INSERT_DATE DATE

-- number of test table records:

SQL > select distinct count (*) from suxing.WMCONCAT_TAB

COUNT (*)

-

262144

-- the contents of the test table:

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.

# # there are more than 26w records in this table, among which are duplicate records of the above 8 records. The three fields are the name, the 11-digit number, and the date entered.

-use different functions or versions to query the splicing table LISTAGG () function:

SELECT name, LISTAGG (TEL,',') WITHIN GROUP (ORDER BY TEL) AS pho_lists

FROM suxing.WMCONCAT_TAB

GROUP BY name

# # if an error is reported directly, the stitching content is too long and exceeds the storage length of the function varchar2 (4000).

-- use the version 1 wmsys.wm_concat () function:

SELECT name, wm_concat (TEL) AS pho_lists

FROM suxing.WMCONCAT_TAB

GROUP BY name

# # similarly, the length of the internal stitching content exceeds the storage length of the inner class pl/sql varchar2 (32767).

Wmsys.wm_concat () function:

-- wmsys.wm_concat () function:

In the revised version of the function, the spliced content is saved in the form of text of the varchar2 (32767) data type.

one。 Unlock wmsys user

Alter user wmsys identified by "XXXXXX" account unlock

-- create packages, packages, and functions

Log in to the database as wmsys, and execute the following command 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

);

/

-- define the type 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

/

-- Custom row variable column function:

CREATE OR REPLACE FUNCTION wm_concat (P1 VARCHAR2)

RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL

/

-- create synonyms and authorize:

[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

/

Version 2:

In this version of the function, the spliced content is clob (4G)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 247

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report