"Sum" of strings -- Example of customizing Aggregate functions in Oracle

Http://blog.itpub.net/post/468/3380

In addition:

Introduction to Oracle custom aggregate function Interfaces

A. Static function odciaggresponinitialize (sCTX in outstring_agg_type)

Return number

Set the initialization of the custom aggregate function. From here, a aggregate function is created.

B. member function odciaggregateiterate (self in out string_agg_type, value in varchar2)

Return number

The main step for customizing a clustering function is to define the specific operations of our clustering function. The following example shows whether to take the maximum value, minimum value, average value, or perform a connection operation. self is the pointer to the current aggregate function, used to associate with the previous calculation results

C. member function odciaggregatemerge (self in string_agg_type, returnvalue out varchar2, flags in number)

Return number

It is used to merge the results of two different pointers of two Aggregate functions. You can combine the data of different results, especially when processing parallel (parallel) queries of Aggregate functions.

D. member function ocdiaggresponterminate (self in string_agg_type, returnvalue out varchar2, flags in number)

Terminate the processing of the aggregate function and return the processing result of the aggregate function.

2. Implementation example.

[Code]

Create type strcat_type as object (

Cat_string varchar2 (4000 ),

Static function odciaggresponinitialize (cs_ctx in out strcat_type) return number,

Member function odciaggregateiterate (self in out strcat_type, value in varchar2) return number,

Member function odciaggregatemerge (self in out strcat_type, ctx2 in out strcat_type) return number,

Member function odciaggresponterminate (self in out strcat_type, returnvalue out varchar2, flags in number) return number

)

/

Create type body strcat_type is

Static function odciaggresponinitialize (cs_ctx in out strcat_type) return number

Is

Begin

Cs_ctx: = strcat_type (null );

Return odciconst. success;

End;

member function odciaggregateiterate (self in out strcat_type,
value in varchar2)
return number
is
begin
self. cat_string: = self. cat_string | ',' | value;
return odciconst. success;
end;

member function odciaggresponterminate (self in out strcat_type,
returnvalue out varchar2,
flags in number)
return number
is
begin
returnvalue: = ltrim (rtrim (self. cat_string, ','), ',');
return odciconst. success;
end;

Member function odciaggregatemerge (self in out strcat_type,

Ctx2 in out strcat_type)

Return number

Is

Begin

Self. cat_string: = self. cat_string | ',' | ctx2.cat _ string;

Return odciconst. success;

End;

End;

/

Create or replace

Function strcat (input varchar2)

Return varchar2

Parallel_enable aggregate using strcat_type;

/

[/Code]

3. Specific applications,

[Code]

15:16:52 SQL> select empno, ename, deptno, job from Scott. EMP;

Empno ename deptno job

---------------------------------------

7369 Smith 20 clerk

7499 Allen 30 salesman

7521 ward 30 salesman

7566 Jones 20 Manager

7654 Martin 30 salesman

7698 Blake 30 Manager

7782 Clark 10 Manager

7788 Scott 20 Analyst

7839 King 10 President

7844 Turner 30 salesman

7876 Adams 20 clerk

7900 James 30 clerk

7902 Ford 20 Analyst

7934 Miller 10 clerk

14 rows selected.

Elapsed: 00:00:00. 01

15:18:29 SQL> Col dept_emplist format A60

15:18:41 SQL> select deptno, strcat (empno | '-' | ename) dept_emplist

15:19:01 2 from Scott. EMP group by deptno;

Deptno dept_emplist

----------------------------------------------------------------------

10 7782-clarke, 7839-king, 7934-Miller

20 7369-Smith, 7902-Ford, 7876-adams, 7788-scott, 7566-jones

30 7499-allen, 7698-blke, 7654-Martin, 7844-Turner, 7900-james, 7521-ward

Elapsed: 00:00:00. 04

15:19:08 SQL> Col job_emplist format a80

15:19:23 SQL> select job, strcat (empno | '-' | ename) job_emplist

15:19:43 2 from Scott. EMP group by job;

Job job_emplist

-----------------------------------------------------------------------------------------

Analyst 7788-scott, 7902-Ford

Clerk 7369-Smith, 7900-james, 7876-adams, 7934-Miller

Manager 7566-jones, 7782-clark, 7698-blke

President 7839-king

Salesman 7499-allen, 7521-Ward, 7844-Turner, 7654-Martin

Elapsed: 00:00:00. 03

15:19:50 SQL>

[/Code]

[Last edited by jametong on at]

Jametong This article references ORACLE data mining documentation

[Url] URLs