用一個執行個體講解Oracle的自訂聚集合函式

(網上找的，原地址不記得了，這裡記載下，以後看)

 

Oracle資料庫的定製功能十分強大。Oracle不但允許使用者定製自己的函數，還可以定製自己的聚集合函式和分析函數。 

本文將著重介紹使用自訂聚集合函式建立一個字串“sum”的樣本： 

代碼

SQL> CREATE OR REPLACE TYPE T_LINK AS OBJECT (   STR VARCHAR2(30000),  
 STATIC FUNCTION ODCIAGGREGATEINITIALIZE  (SCTX IN OUT T_LINK) RETURN NUMBER,  
 MEMBER FUNCTION ODCIAGGREGATEITERATE  (SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER,  
 MEMBER FUNCTION ODCIAGGREGATETERMINATE  (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  
 )  

/  

類型已建立。  

 

 

代碼

SQL> CREATE OR REPLACE TYPE BODY T_LINK IS  
 STATIC FUNCTION ODCIAGGREGATEINITIALIZE  (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 ODCIAGGREGATETERMINATE  (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;  

 /  

類型主體已建立。  

 

 

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

/  

函數已建立。  

 

 

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

表已建立。  

 

 

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

已建立 1 行。  

 

 

代碼

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

已建立 1 行。  

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

已建立 1 行。  

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

已建立 1 行。  

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

已建立 1 行。  

SQL> COMMIT;  

提交完成。  

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