------------------------------------------- -- Export file for user BOSS1214 -- -- Created by user on 2011-2-25, 9:34:30 -- -------------------------------------------
spool str_sum_sql.log
prompt prompt Creating type STR_SUM_OBJ prompt ========================= prompt CREATE OR REPLACE TYPE STR_SUM_OBJ AS OBJECT --聚合函数的实质就是一个对象 ( SUM_STRING VARCHAR2(4000), STATIC FUNCTION ODCIAGGREGATEINITIALIZE(V_SELF IN OUT STR_SUM_OBJ) RETURN NUMBER, --对象初始化
--聚合函数的迭代方法(这是最重要的方法) MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT STR_SUM_OBJ, VALUE IN VARCHAR2) RETURN NUMBER,
--当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT STR_SUM_OBJ, V_NEXT IN STR_SUM_OBJ) RETURN NUMBER,
--终止聚集函数的处理,返回聚集函数处理的结果. MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN STR_SUM_OBJ, RETURN_VALUE OUT VARCHAR2, V_FLAGS IN NUMBER) RETURN NUMBER
) /
prompt prompt Creating function STR_SUM prompt ========================= prompt CREATE OR REPLACE FUNCTION STR_SUM(VALUE VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING STR_SUM_OBJ; /
prompt prompt Creating type body STR_SUM_OBJ prompt ============================== prompt CREATE OR REPLACE TYPE BODY STR_SUM_OBJ IS STATIC FUNCTION ODCIAGGREGATEINITIALIZE(V_SELF IN OUT STR_SUM_OBJ) RETURN NUMBER IS BEGIN V_SELF := STR_SUM_OBJ(NULL); RETURN ODCICONST.SUCCESS; END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT STR_SUM_OBJ, VALUE IN VARCHAR2) RETURN NUMBER IS BEGIN SELF.SUM_STRING := SELF.SUM_STRING || VALUE; RETURN ODCICONST.SUCCESS; END ODCIAGGREGATEITERATE;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT STR_SUM_OBJ, V_NEXT IN STR_SUM_OBJ) RETURN NUMBER IS BEGIN SELF.SUM_STRING := SELF.SUM_STRING || V_NEXT.SUM_STRING; RETURN ODCICONST.SUCCESS; END ODCIAGGREGATEMERGE;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN STR_SUM_OBJ, RETURN_VALUE OUT VARCHAR2, V_FLAGS IN NUMBER) RETURN NUMBER IS BEGIN RETURN_VALUE := SELF.SUM_STRING; RETURN ODCICONST.SUCCESS; END ODCIAGGREGATETERMINATE; END; /
spool off
示例用法如下:
select STR_SUM(pi.productcode || ',') from t_productinfos pi where pi.producttypecode = '00';