对于按照自定义聚集函数连接起来的字符串中的数据不能自动按照一定的规则进行组合，解决办法是：在类型体中对数据进行排序处理。
下边是修改后的代码
CREATE OR REPLACE TYPE VcArrayType IS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE TYPE STR_CONN_TYPE AS OBJECT
(
--vStr VARCHAR2(4000),
vStr VcArrayType,

STATIC FUNCTION
ODCIAggregateInitialize(sctx IN OUT STR_CONN_TYPE )
RETURN NUMBER,

MEMBER FUNCTION
ODCIAggregateIterate(SELF IN OUT STR_CONN_TYPE ,
VALUE IN NUMBER )
RETURN NUMBER,

MEMBER FUNCTION
ODCIAggregateMerge(SELF IN OUT STR_CONN_TYPE,
ctx2 IN STR_CONN_TYPE)
RETURN NUMBER,

MEMBER FUNCTION
ODCIAggregateTerminate(SELF IN STR_CONN_TYPE,
returnValue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY STR_CONN_TYPE
IS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT STR_CONN_TYPE)
RETURN NUMBER
IS
BEGIN
--sctx := STR_CONN_TYPE(NULL);
sctx := STR_CONN_TYPE(VcArrayType());
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT STR_CONN_TYPE,
VALUE IN NUMBER )
RETURN NUMBER
IS
BEGIN
--SELF.vStr := SELF.vStr || ';' || VALUE;
vStr.EXTEND;
vStr(vStr.COUNT) := VALUE;
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT STR_CONN_TYPE,
ctx2 IN STR_CONN_TYPE)
RETURN NUMBER
IS
BEGIN
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate(SELF IN STR_CONN_TYPE,
returnValue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER
IS
tmp_vStr VARCHAR2(4000);
BEGIN
--returnValue := LTRIM(SELF.vStr,';');
FOR rec_Value IN (SELECT column_value FROM TABLE(vStr) ORDER BY to_number(column_value)) LOOP
tmp_vStr := tmp_vStr || ';' || rec_Value.column_value;
END LOOP;returnValue := LTRIM(tmp_vStr,';');
RETURN ODCIConst.Success;
END;
END;
/

CREATE OR REPLACE FUNCTION ConnStr(input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING STR_CONN_TYPE;
/[@more@]

ITPub博客

oracle聚集函数排序

最新文章