Create and call Aggregate functions on your own in Oracle

When you are working on a project, you may need to merge multiple records into one record, then combine different fields into one field for display, and separate them with commas (,). After you use them, it solves a lot of inconvenience.

When you are working on a project, you may need to merge multiple records into one record, then combine different fields into one field for display, and separate them with commas (,). After you use them, it solves a lot of inconvenience.

When you are working on a project, you may need to merge multiple records into one record, then combine different fields into one field for display, and separate them with commas, it solves a lot of problems that are inconvenient to solve. We will sort them out as follows for your reference!

Create as follows: First open SQL _plus:

Mandatory class used to create a function:

-- Oracle9i aggregate function (used for query)
Create type CatStringImpl as object (
Catstring VARCHAR2 (4000), -- the returned string
Static function odciaggresponinitialize (sctx in out CatStringImpl)
Return number,
Member function ODCIAggregateIterate (self in out CatStringImpl, value IN varchar2)
Return number,
Member function odciaggresponterminate (self IN CatStringImpl, returnValue OUT varchar2, flags IN number)
Return number,
Member function ODCIAggregateMerge (self in out CatStringImpl, ctx2 IN CatStringImpl)
Return number
);

Then create the function method, that is, the function:

Create or replace type body CatStringImpl
Static function odciaggresponinitialize (sctx in out CatStringImpl) return number is
Begin
Sctx: = CatStringImpl ('');
Return ODCIConst. Success;
End;
Member function ODCIAggregateIterate (self in out CatStringImpl, value IN varchar2) return number is
Begin
Self. catstring: = self. catstring | ',' | value;
Return ODCIConst. Success;
End;
Member function odciaggresponterminate (self IN CatStringImpl, returnValue OUT varchar2, flags IN number) return number is
Begin
ReturnValue: = self. catstring;
Return ODCIConst. Success;
End;
Member function ODCIAggregateMerge (self in out CatStringImpl, ctx2 IN CatStringImpl) return number is
Begin
Self. catstring: = self. catstring | ',' | ctx2.catstring;
Return ODCIConst. Success;
End;
End;

Finally, create a function:

Create function catstr (input varchar2) return varchar2 PARALLEL_ENABLE aggregate using CatStringImpl;

In this way, the target function is created. The query call is as follows:

Select catstr (a), B, c from tb group by B, c can combine strings by code.

Catstr is the function name, where a is the same field and should have different values, B, c is the same field and the same value.

With this method, it will be much easier to use in the future!