Oracle custom wmsys. Wm_concat avoid length limit search to a similar code but the length of the process or not, after I modify, kiss can be measured,
The CREATE OR REPLACE TYPE ZH_CONCAT_IM
AUTHID CURRENT_USER AS OBJECT
(
CURR_STR CLOB,
The STATIC FUNCTION ODCIAGGREGATEINITIALIZE (SCTX OUT ZH_CONCAT_IM) IN RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT ZH_CONCAT_IM,
P1 VARCHAR2) IN RETURN NUMBER,
The MEMBER FUNCTION ODCIAGGREGATETERMINATE (SELF IN ZH_CONCAT_IM,
The RETURNVALUE OUT CLOB,
FLAGS IN NUMBER)
The RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT ZH_CONCAT_IM,
SCTX2 ZH_CONCAT_IM) IN RETURN NUMBER
)
GO

The CREATE OR REPLACE TYPE BODY ZH_CONCAT_IM
IS
The STATIC FUNCTION ODCIAGGREGATEINITIALIZE (SCTX IN OUT ZH_CONCAT_IM)
RETURN the NUMBER
IS
The BEGIN
SCTX:=ZH_CONCAT_IM (NULL);
RETURN ODCICONST. SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT ZH_CONCAT_IM,
P1 IN VARCHAR2)
RETURN the NUMBER
IS
The BEGIN
IF (CURR_STR IS NOT NULL) THEN
CURR_STR:=CURR_STR | | ', '| | P1.
The ELSE
CURR_STR:=p;
END the IF;
RETURN ODCICONST. SUCCESS;
END;
The MEMBER FUNCTION ODCIAGGREGATETERMINATE (SELF IN ZH_CONCAT_IM,
The RETURNVALUE OUT CLOB,
FLAGS IN NUMBER)
RETURN the NUMBER
IS
The BEGIN
The RETURNVALUE:=CURR_STR;
RETURN ODCICONST. SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT ZH_CONCAT_IM,
SCTX2 IN ZH_CONCAT_IM)
RETURN the NUMBER
IS
The BEGIN
IF (SCTX2. CURR_STR IS NOT NULL) THEN
SELF. CURR_STR:=SELF. CURR_STR | | ', '| | SCTX2. CURR_STR;
END the IF;
RETURN ODCICONST. SUCCESS;
END;
END;
GO
The CREATE OR REPLACE FUNCTION ZH_CONCAT (P1 VARCHAR2)
RETURN CLOB AGGREGATE USING ZH_CONCAT_IM;

CodePudding user response:

So ~ your problem is?

CodePudding user response:

Write their own custom function too much trouble, it is ok to use xmlagg, supporting the return clob and type string
The select rtrim (xmlagg (xmlparse (the content l | | ', 'wellformed)). Getclobval (),', ')/*. Getstringval () */the from
(select level as l from dual connect by level<=5000).

CodePudding user response:

reference 1st floor ghx287524027 response:

so ~ your problem is?

He had no problem, estimates is want to record, actually writing a blog better,

CodePudding user response:

the next blog, so, no problem, ha ha

CodePudding user response:

Oracle11g and 10 g return types

CodePudding user response:

Success, but exist a compiler error

CodePudding user response:

Clob at the expense of the performance, the since 10205 and 11.2 is the type, the function as oracle internal function, is not recommended, in 12 c has the function is not available, the key recommendations listagg

CodePudding user response:

This kind of writing is very common, online,
Your summary before this several kinds of usage, welcome to correct me,
If this kind of writing is the name of clob_concat, the original writing return varchar2 name is vc_concat,
Listagg too restrictive, where only 11 g has and can return only varchar2 type, vc_concat and clob_concat both internal function can't internal sorting, and internal polymerization is chaotic, such as
The select wm_concat (col1) col3, wm_concat (col2) col4 from TAB.
In return col3 col4 and aggregated data is not necessarily a one-to-one, that do not pay attention to is very easy to get wrong,
About the efficiency comparison:
Points:
1, need to return the varchar2, do not need to sort the efficiency of the contrast: [vc_concat] & gt; [listagg] & gt; [xmlagg]
2, the need to return clob, do not need to sorting efficiency comparison: [clob_concat] & gt; [xmlagg]
3, need to return varchar2, and need to sorting efficiency comparison: [listagg] & gt; [xmlagg] & gt; [vc_concat circulating on the Internet by using the method of analysis function sorted maximize]
4, need to return the clob and need to sorting efficiency contrast: [xmlagg] & gt; [clob_concat circulating on the Internet by using the method of analysis function sorted maximize]
Conclusion:
Does not involve sorting or corresponding preference vc_concat or clob_concat, a sort of preference listagg, consider xmlagg method in 2 floor,

CodePudding user response:

You this is at the cost of performance, the data once too much to do for a long time