oracle自定义聚集函数接口简介
a. static function ODCIAggregateInitialize(sctx IN OUTstring_agg_type )
return number
自定义聚集函数初始化设置,从这儿开始一个聚集函数
b. member function ODCIAggregateIterate(self IN OUT string_agg_type ,value IN varchar2)
return number
自定义聚集函数,最主要的步骤,这个函数定义我们的聚集函数具体做什么操作,后面的例子,是取最大值,最小值,平均值,还是做连接操作.self 为当前聚集函数的指针,用来与前面的计算结果进行关联
c. member function ODCIAggregateMerge (self IN string_agg_type,returnValue OUT varchar2,flags IN number)
return number
用来合并两个聚集函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别是处理并行(parallel)查询聚集函数的时候.
d. member function OCDIAggregateTerminate(self IN string_agg_type,returnValue OUT varchar2,flags IN number)
终止聚集函数的处理,返回聚集函数处理的结果.
a. static function ODCIAggregateInitialize(sctx IN OUTstring_agg_type )
return number
自定义聚集函数初始化设置,从这儿开始一个聚集函数
b. member function ODCIAggregateIterate(self IN OUT string_agg_type ,value IN varchar2)
return number
自定义聚集函数,最主要的步骤,这个函数定义我们的聚集函数具体做什么操作,后面的例子,是取最大值,最小值,平均值,还是做连接操作.self 为当前聚集函数的指针,用来与前面的计算结果进行关联
c. member function ODCIAggregateMerge (self IN string_agg_type,returnValue OUT varchar2,flags IN number)
return number
用来合并两个聚集函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别是处理并行(parallel)查询聚集函数的时候.
d. member function OCDIAggregateTerminate(self IN string_agg_type,returnValue OUT varchar2,flags IN number)
终止聚集函数的处理,返回聚集函数处理的结果.
字符串连接函数:
第一步:TYPE
CREATE OR REPLACE TYPE T_LINK_STR AS OBJECT (
STR VARCHAR2(30000),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK_STR) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK_STR, VALUE IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK_STR, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK_STR, CTX2 IN T_LINK_STR) RETURN NUMBER
)
第二步:TYPE BODY
CREATE OR REPLACE TYPE BODY T_LINK_STR IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK_STR) RETURN NUMBER IS
BEGIN
SCTX := T_LINK_STR(NULL);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK_STR, VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
SELF.STR := SELF.STR || VALUE||';';
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK_STR, 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_STR, CTX2 IN T_LINK_STR) RETURN NUMBER IS
BEGIN
NULL;
RETURN ODCICONST.SUCCESS;
END;
END;
第三步:FUNCTION
CREATE OR REPLACE FUNCTION STR_SUM(P_STR VARCHAR2) RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING T_LINK_STR;
函数测试:
CREATE OR REPLACE TYPE T_LINK_STR AS OBJECT (
STR VARCHAR2(30000),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK_STR) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK_STR, VALUE IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK_STR, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK_STR, CTX2 IN T_LINK_STR) RETURN NUMBER
)
第二步:TYPE BODY
CREATE OR REPLACE TYPE BODY T_LINK_STR IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK_STR) RETURN NUMBER IS
BEGIN
SCTX := T_LINK_STR(NULL);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK_STR, VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
SELF.STR := SELF.STR || VALUE||';';
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK_STR, 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_STR, CTX2 IN T_LINK_STR) RETURN NUMBER IS
BEGIN
NULL;
RETURN ODCICONST.SUCCESS;
END;
END;
第三步:FUNCTION
CREATE OR REPLACE FUNCTION STR_SUM(P_STR VARCHAR2) RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING T_LINK_STR;
函数测试:
create table TABLE1 ( ID VARCHAR2(10), T_NAME VARCHAR2(10), T_VALUE VARCHAR2(10) )
select t_name ,str_sum(t_value) from table1 group by t_name
结果如下
结果如下