對於按照自定義聚集函式連線起來的字串中的資料不能自動按照一定的規則進行組合，解決辦法是：在型別體中對資料進行排序處理。
下邊是修改後的程式碼
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@]

oracle聚集函式排序

相關文章