Oracle聚集函數排序

　　對於按照自定義聚集函數連接起來的字符串中的數據不能自動按照一定的規則進行組合解決辦法是在類型體中對數據進行排序處理

　　下邊是修改後的代碼

　　 CREATE OR REPLACE TYPE VcArrayType IS TABLE OF VARCHAR();
/

CREATE OR REPLACE TYPE STR_CONN_TYPE AS OBJECT
(
vStr VARCHAR()
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
ctx IN STR_CONN_TYPE)
RETURN NUMBER

MEMBER FUNCTION
ODCIAggregateTerminate(SELF IN STR_CONN_TYPE
returnValue OUT VARCHAR
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 ODCIConstSuccess;
END;

MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT STR_CONN_TYPE
VALUE IN NUMBER )
RETURN NUMBER
IS
BEGIN
SELFvStr := SELFvStr || ; || VALUE;
vStrEXTEND;
vStr(vStrCOUNT) := VALUE;
RETURN ODCIConstSuccess;
END;

MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT STR_CONN_TYPE
ctx IN STR_CONN_TYPE)
RETURN NUMBER
IS
BEGIN
RETURN ODCIConstSuccess;
END;

MEMBER FUNCTION ODCIAggregateTerminate(SELF IN STR_CONN_TYPE
returnValue OUT VARCHAR
flags IN NUMBER)
RETURN NUMBER
IS
tmp_vStr VARCHAR();
BEGIN
returnValue := LTRIM(SELFvStr;);
FOR rec_Value IN (SELECT column_value FROM TABLE(vStr) ORDER BY to_number(column_value)) LOOP
tmp_vStr := tmp_vStr || ; || lumn_value;
END LOOP;returnValue := LTRIM(tmp_vStr;);
RETURN ODCIConstSuccess;
END;
END;
/

CREATE OR REPLACE FUNCTION ConnStr(input VARCHAR)
RETURN VARCHAR
PARALLEL_ENABLE AGGREGATE USING STR_CONN_TYPE;
/

From:http://tw.wingwit.com/Article/program/Oracle/201311/18230.html