在已往的帖子中有不少是问关于按分组进行字符串连接的问题,本人根据精华贴中的自定义聚合函数的例子创建了一个函数rawstrcat,脚本如下:
create type Rawstr as object
(
str VARCHAR2(3000), --
static function ODCIAggregateInitialize(sctx IN OUT Rawstr) return number,
member function ODCIAggregateIterate(self IN OUT Rawstr, value IN VARCHAR2) return number,
member function ODCIAggregateTerminate(self IN Rawstr, returnValue OUT VARCHAR2, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT Rawstr,ctx2 IN Rawstr) return number
);
/
--Implement the type body for Rawstr.
create or replace type body Rawstr is
static function ODCIAggregateInitialize(sctx IN OUT Rawstr)
return number is
begin
sctx := Rawstr('');
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT Rawstr, value IN varchar2) return number is
begin
self.str := self.str||value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN Rawstr, returnValue OUT varchar2, flags IN number) return number is
begin
returnValue := self.str;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT Rawstr, ctx2 IN Rawstr) return number is
begin
self.str := self.str||ctx2.str;
return ODCIConst.Success;
end;
end;
/
--Create the user-defined aggregate.
CREATE FUNCTION rawstrcat (input varchar2) RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING rawstr;
如:
SQL> desc t_test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
CH VARCHAR2(5)
SQL> select id,ch from t_test;
ID CH
---- ---
1 1
1 01
1 011
2 2
2 02
2 022
SQL> select id,rawstrcat(ch) str from t_test group by id
2 ;
ID STR
---- ------
1 101011
2 202022
然而,我的问题是,因为自定义的聚合函数rawstrcat同分组函数sum不一样的是后者用的是满足交换率的几何+法,而前者是字符串连接并不满足交换率,这样它是否会在不同的时刻运行出现不同的结果 是否可以在创建该函数是约定按照某种顺序排序后进行连接,如上述的例子若按ch的升序后进行连接这样结果应该是:
ID STR
---- ------
1 010111
2 020222
?[/COLOR]