Day 75: PL/SQL - 13 (How to make custom Aggregate Function in Oracle)

http://beyondrelational.com/modules/2/blogs/80/posts/10768/day-75-plsql-13-how-to-make-custom-aggregate-function-in-oracle.aspx

 

In this article, we will look into creating our own Aggregate Function in Oracle.

User-defined aggregates are a feature of the Extensibility Framework.It is possbile to make user define Aggregate function because of Oracle's Data Cartridge model which takes advantage of object types and other extensibility features. Inorder to create user-defined aggregate function, we need to implement a set of routines collectively referred to as the ODCIAggregate routines. It is of four types which are

  1. ODCIAggregateInitialize
  2. ODCIAggregateIterate
  3. ODCIAggregateMerge
  4. ODCIAggregateTerminate

Below is a brief description about the above routines

ODCIAggregate Routines Purpose
ODCIAggregateInitialize Initialize the computation of the user-defined aggregate and will be return to Oracle as an object type instance. It is mandatory
ODCIAggregateIterate This routine is repeatedly invoked by Oracle and on every invocation, a new value(s) and current aggregation context is passed as input. It processes the new value(s) and returns the updated aggregation context back to Oracle. It is mandatory
ODCIAggregateMerge Combine two aggregation contexts and returns a single aggregation context.It is optional
ODCIAggregateTerminate This routine is invoked by Oracle as the final step of aggregation which returns the resulting aggregate value. It is mandatory

It is quite simple to create user define Aggregate function in Oracle.For creating a user define aggreagte function, we need -

  1. Object type specification
  2. Object type body
  3. PL/SQL aggregate function

So armed with all these information, we can now create our own aggregate function that will perform string aggregation.

Let us first see the original source

01.SQL> Select DeptName,EmpName
02.From tblDept
03.Join tblEmployee
04.Using(DeptId)
05.Group By DeptName;
06. 
07.DEPTNAME                                           EMPNAME
08.-------------------------------------------------- ----------------------
09.IT                                                 Deepak Kumar Goyal
10.Accounts                                           Shashi Dayal
11.IT                                                 Amitav Mallik
12.Sales                                              Amit Ojha
13.Finance                                            Sumanta Manik
14.IT                                                 Deepak Singh
15.IT                                                 Shashi Bhushan
16.IT                                                 Amitav Salonki
17.Accounts                                           Deepak Singh
18.Accounts                                           Shashi Bhushan
19.Accounts                                           Amitav Salonki
20.Accounts                                           Manish Bharat
21.Accounts                                           Abhijeet Moshambique
22.Finance                                            Sarapati Babulal Apte
23.Finance                                            Shinarayan Pande
24.Finance                                            Fatima  Sarani
25. 
26.16 rows selected.

Well Oracle already has WM_Concat(undocumented function in in 10g) and ListAgg function(Oracle 11g R2) for doing so. Let us see what the built-in ListAgg/WM_Concat function is doing.

01.SQL> Select DeptName,ListAgg(EmpName,',') Within Group (Order By EmpName) Concat_Employees
02.From tblDept
03.Join tblEmployee
04.Using(DeptId)
05.Group By DeptName;
06. 
07.- OR -
08. 
09.SQL>  Select DeptName,WM_Concat(EmpName) Concat_Employees
10.From tblDept
11.Join tblEmployee
12.Using(DeptId)
13.Group By DeptName;
14. 
15.DEPTNAME                                           CONCAT_EMPLOYEES
16.-------------------------------------------------- -------------------------------------------------------
17.Accounts                                           Shashi Dayal,Deepak Singh,Amitav Salonki,Shashi Bhushan
18.,Manish Bharat,Abhijeet Moshambique
19. 
20.Finance                                            Sumanta Manik,Sarapati Babulal Apte,Fatima  Sarani
21.,Shinarayan Pande
22. 
23.IT                                                 Deepak Kumar Goyal,Deepak Singh,Amitav Salonki
24.,Shashi Bhushan,Amitav Mallik
25. 
26.Sales                                              Amit Ojha

Our aim is to make a similar kind of function.

N.B.~If someone ask me since we already have buit-in function for achieving the result then why we need to build our own? Well it is for our demonstration purpose

Now let us look into the practical aspect of doing so

Step 1: First create the Object type specification

01.SQL> CREATE OR REPLACE TYPE String_Aggregate_Type AS OBJECT(
02. 
03.str_values VARCHAR2(8000),
04. 
05.STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT String_Aggregate_Type)
06.RETURN NUMBER,
07. 
08.MEMBER FUNCTION ODCIAggregateIterate(self  IN OUT String_Aggregate_Type                
09.,value IN VARCHAR2)
10.RETURN NUMBER,
11. 
12.MEMBER FUNCTION ODCIAggregateMerge(self IN OUT String_Aggregate_Type
13.,ctx2 IN String_Aggregate_Type)
14.RETURN NUMBER,
15. 
16.MEMBER FUNCTION ODCIAggregateTerminate (self IN String_Aggregate_Type
17.,return_value OUT VARCHAR2
18.,flags IN NUMBER)              
19.RETURN NUMBER
20. 
21.);
22./
23.Type created.

To keep things simple, our type needs only the string values(str_values). If we look into the original source, it is EMPNAME values

Step 2: Second, create the Object type body

01.SQL> CREATE OR REPLACE TYPE BODY String_Aggregate_Type IS
02. 
03.STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT String_Aggregate_Type)
04.RETURN NUMBER IS
05.BEGIN
06.sctx := String_Aggregate_Type(null);
07.RETURN ODCIConst.Success;
08.END;
09. 
10.MEMBER FUNCTION ODCIAggregateIterate(self  IN OUT String_Aggregate_Type                
11.,value IN varchar2)
12.RETURN NUMBER IS
13.BEGIN
14.str_values := str_values || ',' || value;
15.str_values := LTRIM(str_values,',');
16.RETURN ODCIConst.Success;
17.END;   
18. 
19.MEMBER FUNCTION ODCIAggregateMerge(self IN OUT String_Aggregate_Type
20.,ctx2 IN String_Aggregate_Type)
21.RETURN NUMBER
22.IS
23.BEGIN
24.str_values := str_values || ctx2.str_values;
25.return ODCIConst.Success;
26.END;
27. 
28.MEMBER FUNCTION ODCIAggregateTerminate(self IN String_Aggregate_Type
29.,return_value OUT VARCHAR2
30.,flags IN NUMBER)              
31.RETURN NUMBER
32.IS
33.BEGIN
34.return_value := str_values ;
35.return ODCIConst.Success;
36.END;
37.END;
38./
39.Type body created.

In the ODCIAggregateInitialize, we are initializing str_values with null values. In ODCIAggregateIterate, the values(in this case EMPNAME) are being iterated, appended with a comma(,) delimeter and are store into the str_values.This is the method that tells Oracle on how to aggregate the individual elements that are passed to it. The ODCIAggregateMerge combines two aggregation contexts and returns a single aggregation context.Finally, ODCIAggregateTerminate returns the aggregated result

Step 3: Create the PL/SQL aggregate function

1.SQL> CREATE OR REPLACE FUNCTION Custom_String_Aggregation(inputString VARCHAR2)
2.RETURN VARCHAR2
3.PARALLEL_ENABLE AGGREGATE USING String_Aggregate_Type;
4./
5.Function created.

So our function is now ready to use and let us use it as under

01.SQL> Select DeptName,Custom_String_Aggregation(EmpName) Concat_Employees
02.From tblDept
03.Join tblEmployee
04.Using(DeptId)
05.Group By DeptName;
06. 
07.DEPTNAME                                           CONCAT_EMPLOYEES
08.-------------------------------------------------- -------------------------------------------------------
09.Accounts                                           Shashi Dayal,Deepak Singh,Amitav Salonki,Shashi Bhushan
10.,Manish Bharat,Abhijeet Moshambique
11. 
12.Finance                                            Sumanta Manik,Sarapati Babulal Apte,Fatima  Sarani
13.,Shinarayan Pande
14. 
15.IT                                                 Deepak Kumar Goyal,Deepak Singh,Amitav Salonki
16.,Shashi Bhushan,Amitav Mallik
17. 
18.Sales                                              Amit Ojha

From SQL Server 2005, we can write our own CLR Aggregate Function

Hope this helps. Thanks for reading

posted @ 2014-02-18 13:30  princessd8251  阅读(386)  评论(0)    收藏  举报