Explain Oracle user-defined aggregate functions with an instance

(I found it online. I cannot remember the original address. I will record it here and read it later)

 

Oracle Database customization is very powerful. Oracle not only allows users to customize their own functions, but also can customize their own Aggregate functions and analysis functions.

This article will focus on the example of creating a string "sum" using a custom aggregate function:

Code

SQL> CREATE OR REPLACE TYPE T_LINK AS OBJECT (STR VARCHAR2 (30000 ),
Static function odciaggresponinitialize (sctx in out T_LINK) return number,
Member function odciaggregateiterate (self in out T_LINK, value in VARCHAR2) return number,
Member function odciaggresponterminate (self in T_LINK, returnvalue out VARCHAR2, flags in number) return number,
Member function odciaggregatemerge (self in out T_LINK, CTX2 IN T_LINK) RETURN NUMBER
)

/

Type already created.

 

 

Code

SQL> CREATE OR REPLACE TYPE BODY T_LINK IS
Static function odciaggresponinitialize (sctx in out T_LINK) RETURN NUMBER IS
BEGIN
SCTX: = T_LINK (NULL );
Return odciconst. SUCCESS;
END;

Member function odciaggregateiterate (self in out T_LINK, value in VARCHAR2) RETURN NUMBER IS
BEGIN
SELF. STR: = SELF. STR | VALUE;
Return odciconst. SUCCESS;
END;

Member function odciaggresponterminate (self in T_LINK, returnvalue out VARCHAR2, flags in number) RETURN NUMBER IS
BEGIN
RETURNVALUE: = SELF. STR;
Return odciconst. SUCCESS;
END;

Member function odciaggregatemerge (self in out T_LINK, CTX2 IN T_LINK) RETURN NUMBER IS
BEGIN
NULL;
Return odciconst. SUCCESS;
END;

END;

/

The type subject has been created.

 

 

SQL> CREATE OR REPLACE FUNCTION F_LINK (P_STR VARCHAR2) RETURN VARCHAR2 AGGREGATE USING T_LINK;

/

The function has been created.

 

 

SQL> CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2 (20 ));

The table has been created.

 

 

SQL> INSERT INTO TEST VALUES (1, 'aaa ');

One row has been created.

 

 

Code

SQL> INSERT INTO TEST VALUES (2, 'bbb ');

One row has been created.

SQL> INSERT INTO TEST VALUES (1, 'abc ');

One row has been created.

SQL> INSERT INTO TEST VALUES (3, 'ccc ');

One row has been created.

SQL> INSERT INTO TEST VALUES (2, 'ddd ');

One row has been created.

SQL> COMMIT;

Submitted.

SQL> COL NAME FORMAT A60

SQL> SELECT ID, F_LINK (NAME) NAME FROM TEST GROUP BY ID;

ID NAME

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

1 AAAABC

2 BBBDDD

3 CCC