Rewrite Oracle's wm_concat function, custom delimiter, sort

In Oracle, the WM_CONCAT function is an aggregate function similar to the GROUP_CONCAT function in MySQL, but the Group_concat function is powerful and can define delimiters and sorts, and of course the so-called strong is relative, If we don't know the over function in Oracle, we don't know the Listagg function.

Let's take a look at what function Wm_concat function can achieve, popular point ==> biographies line, if not understand, see below (you can see the separation defaults think ', ' order is also messy)

So, next, we're going to rewrite the Wm_concat function (which needs to be noted and put in code comments ...)

(1) Because of the need to sort, first customize a mutable array

--Define variable array, string type, length 500, hold column value create OR REPLACE TYPE Wyarray as TABLE of VARCHAR (32767);

(2) Custom sort function, delimiter function

--Define delimiter function Create or Replace function delimiter (Colvalue  in varchar2,                                     delimiter in varchar2) return VARCHAR2 Is
   rtnvalue varchar2 (32767); begin  Rtnvalue: = Colvalue | | ' Delimiter=> ' | | Delimiter | | ‘; ';  return rtnvalue;end delimiter;

　　

--Define Sort function Create or Replace function order by (Colvalue in varchar2, orders in  varchar2) return varchar2 is  RTN Value varchar2 (32767); begin  Rtnvalue: = Colvalue | | ' Orderby=> ' | | LOWER (to be) | | ‘; ';  return rtnvalue;end;

　

(3) redefine the Oracle interface functions, and the implementation body of the interface functions (the main code to implement separators and sorting)

--Use Current user permissions(using Authid current_user, define the type as the user's current user's permissions, for example: a user he can create a table, but a user in the stored procedure if you set up a table may prompt insufficient permissions, so you need to use Authid Current_ User to constrain)Create or replace type Wy_wm_concat Authid Current_User as Object (--stitching strings, storing intermediate values, of course, can also be defined as Clob,clob will use temporary segments, resulting in a rapid increase in temporary table space;  View the function under Wmsys to find the return type of Wm_concat function oracle10g to oracle11g from Clob to Varchar2 currstr VARCHAR2 (32767),--split string Delimiter VARCHAR2 (,--) Sort string (ASC, DESC) Order VARCHAR2 (),--define the string array Strarray Wyarray,--Initialize the interface function STATIC function OD     Ciaggregateinitialize (init in Out Wy_wm_concat) RETURN number,--iterative interface functions MEMBER function odciaggregateiterate (self In out Wy_wm_concat, Colvalue in VARCHAR2) RETURN number,--parallel when string merging interface function MEMBER FU Nction Odciaggregatemerge (self on Out wy_wm_concat, next wy_wm_concat) RETURN number,-                                         -Oracle Terminating interface functions MEMBER function odciaggregateterminate (self in Wy_wm_concat, ReturnValue out VARCHAR2, flags in number) RETURN number)

　　

--Customizing the body part of the Wy_wm_concat type
Create or replace type body wy_wm_concat is--initializer STATIC function odciaggregateinitialize (init in Out Wy_wm_concat)    RETURN number is begin init: = Wy_wm_concat (', ', ', ' ASC ', Wyarray ()); Return Odciconst.  Success;  END;                                       --string concatenation, self is a pointer to the current aggregate function, which is used to correlate with the previous calculation result MEMBER function odciaggregateiterate (self in Out wy_wm_concat,      Colvalue in VARCHAR2) RETURN number is TempStr varchar (500);      EXTENDSTR varchar (500);      DESTR varchar (100);      Delen int default 0;      SEGSTR varchar (500);      --Define a two-dimensional array TYPE Vararry is Varray (2) of VARCHAR2 (200);    Temparry Vararry: = Vararry (",");    Begin If InStr (Colvalue, ", 1) > 0 Then TempStr: = substr (Colvalue, 1, InStr (Colvalue, ', 1)-1);    else tempstr: = Colvalue;      End If; --Sort and delimiter extendstr: = REPLACE (colvalue, TEMPSTR | |      ‘ ‘); If InStr (Extendstr, ', 1) > 0 Then Temparry (1): = substr (Extendstr, 1, InStr (Extendstr, ", 1)-1);          Temparry (2): = substr (Extendstr, InStr (Extendstr, ", 1)); For I in 1.. Temparry.count Loop--Gets the delimiter if (Temparry (i) is not null) and (InStr (Temparry (i), ' delimiter=> '                  ) > 0) then destr: = ' delimiter=> ';                  Delen: = Length (DESTR);                  SEGSTR: = substr (Trim (Temparry (i)), InStr (Trim (Temparry (i)), destr) + Delen);        Self.delimiter: = SUBSTR (Segstr, 1, InStr (Segstr, '; ',-1)-1);              END IF;                  --Get Sort string if Temparry (i) is not null and (InStr (Temparry (i), ' orderby=> ') > 0) Then                  Destr: = ' orderby=> ';                  Delen: = Length (DESTR);                  SEGSTR: = substr (Trim (Temparry (i)), InStr (Trim (Temparry (i)), destr) + Delen);                Self.orderby: = SUBSTR (Segstr, 1, InStr (Segstr, '; ',-1)-1);            END IF;        End Loop; EndIf      --Deposit into the array self.strArray.extend;      Self.strarray (self.strArray.count): = TempStr; Return Odciconst.  Success;  END;                                     --parallel operation is the result of merging two different pointers of two aggregate functions MEMBER function odciaggregatemerge (self in Out wy_wm_concat, Next wy_wm_concat) RETURN number is begin-places the elements in the next array in the corresponding array of the self pointer for a in 1.          Next.strArray.count Loop self.strArray.extend;        Self.strarray (self.strArray.count): = Next.strarray (i);      End Loop; Return Odciconst.  Success;  END;                                         --Terminates the function, returns the result MEMBER functions odciaggregateterminate (self in Wy_wm_concat,  ReturnValue out VARCHAR2, "flags in Number") RETURN number is Temp_rtnvalue    VARCHAR2 (32767); BEGIN--Sort if INSTR (Self.orderby, ' desc ') > 0 then for x in (select Column_value from Table (Self.strarray) Order by 1 DESC) Loop Temp_rtnValue: = Temp_rtnvalue | | Self.delimiter | |            X.column_value;    End Loop; ELSE for x in (select Column_value from Table (Self.strarray) Order by 1 ASC) loop temp_rtnvalue: = Temp_ Rtnvalue | | Self.delimiter | |            X.column_value;        End Loop;      END IF;      ReturnValue: = LTrim (Temp_rtnvalue, Self.delimiter); Return Odciconst.  Success; END; END;

　

(4) Custom aggregation functions

--Define aggregation function (parallel computing is not turned on) Create or Replace function Wy_concat (colvalue  VARCHAR2) RETURN VARCHAR2  AGGREGATE USING wy_ Wm_concat;

　　

At this point, the main code has been all served to see the effect of the operation, as follows:

① See the default of the call (the delimiter is comma by default, sort by default is ascending, as defined in the initialization function)

② Custom delimiter (use delimiter function to define delimiter as *)

③ Descending sort

④ to go heavy, in order to be able to use the wm_concat comes with the de-weight function, so in the custom delimiter and sorting functions, the essence is practical string processing (if you think that handling string trouble, you can customize the type ... As object, can be very convenient when using, Children's shoes can not be used to ask privately)

Rewrite Oracle's wm_concat function, custom delimiter, sort