create or replace type CLOB_GROUP_OBJ as object (
sum_string clob,
static function ODCIAggregateInitialize(v_self in out CLOB_GROUP_OBJ) return number,
member function ODCIAggregateIterate(self in out CLOB_GROUP_OBJ, value in varchar2) return number,
member function ODCIAggregateMerge(self in out CLOB_GROUP_OBJ, v_next in CLOB_GROUP_OBJ) return number,
member function ODCIAggregateTerminate(self in CLOB_GROUP_OBJ, return_value out clob ,v_flags in number) return number
)
/
create or replace type body CLOB_GROUP_OBJ is
static function ODCIAggregateInitialize(v_self in out CLOB_GROUP_OBJ) return number is
begin
v_self := CLOB_GROUP_OBJ(null);
return ODCICONST.Success;
end;
member function ODCIAggregateIterate(self in out CLOB_GROUP_OBJ, value in varchar2) return number is
begin
self.sum_string := self.sum_string || value||'|';
return ODCICONST.Success;
if self.sum_string<value then
self.sum_string:=value;
end if; if self.sum_string>value then
self.sum_string:=value;
end if;
return ODCICONST.Success;
end;
member function ODCIAggregateMerge(self in out CLOB_GROUP_OBJ, v_next in CLOB_GROUP_OBJ) return number is
begin
self.sum_string := self.sum_string ||v_next.sum_string;
return ODCICONST.Success;
if self.sum_string<v_next.sum_string then
self.sum_string:=v_next.sum_string;
end if;
if self.sum_string>v_next.sum_string then
self.sum_string:=v_next.sum_string;
end if;
return ODCICONST.Success;
end;
member function ODCIAggregateTerminate(self in CLOB_GROUP_OBJ, return_value out clob ,v_flags in number) return number is
begin
return_value:= self.sum_string;
return ODCICONST.Success;
end;
end;
/
create or replace function GROUPCATCLOB(value varchar2) return clob
parallel_enable aggregate using CLOB_GROUP_OBJ;
oracle 自定义 group 聚合函数
最新推荐文章于 2024-07-17 15:18:27 发布