In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.