Ну раз на коне тут уже влетали, въеду ка я на сером ослике... при том что 99% кода подсмотрел в Oracle Magazine
| Код |
CREATE OR REPLACE TYPE strsum_t AS OBJECT (
ls_sum VARCHAR2(32767),
MEMBER FUNCTION get_separator RETURN VARCHAR2,
STATIC FUNCTION ODCIAggregateInitialize (ctx IN OUT strsum_t) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate (self IN OUT strsum_t, value IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge (self IN OUT strsum_t, ctx IN strsum_t) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate (self IN strsum_t, value OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY strsum_t
AS
MEMBER FUNCTION get_separator RETURN VARCHAR2
IS
ls_separator CONSTANT CHAR(1) := ' ';
BEGIN
RETURN ls_separator;
END get_separator;
STATIC FUNCTION ODCIAggregateInitialize (ctx IN OUT strsum_t) RETURN NUMBER
IS
BEGIN
ctx := strsum_t(NULL);
RETURN ODCIConst.Success;
END ODCIAggregateInitialize;
MEMBER FUNCTION ODCIAggregateIterate (self IN OUT strsum_t, value IN VARCHAR2) RETURN NUMBER
IS
BEGIN
self.ls_sum := self.ls_sum || self.get_separator || value;
RETURN ODCIConst.Success;
END ODCIAggregateIterate;
MEMBER FUNCTION ODCIAggregateMerge (self IN OUT strsum_t, ctx IN strsum_t) RETURN NUMBER
IS
BEGIN
self.ls_sum := LTRIM(self.ls_sum, self.get_separator)|| self.get_separator|| LTRIM(ctx.ls_sum, self.get_separator);
RETURN ODCIConst.Success;
END ODCIAggregateMerge;
MEMBER FUNCTION ODCIAggregateTerminate (self IN strsum_t, value OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER
IS
BEGIN
value := LTRIM(self.ls_sum, self.get_separator);
RETURN ODCIConst.Success;
END ODCIAggregateTerminate;
END;
/
CREATE OR REPLACE FUNCTION strsum (as_str_i VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE
AGGREGATE USING
strsum_t;
/
with c as (
Select
'1' id, 'Степанов' sname from dual
union all
Select '1','Фёдоров' from dual
union all
Select '1','Иванов' from dual
union all
Select '2','Пивоваров' from dual
union all
Select '3','Сидоров' from dual
union all
Select '3','Коновалов' from dual)
Select
c.id,
count(*),
STRSUM(sname)
From c
Group by c.id;
|
| Код |
SQL>
Type created
Type body created
Function created
ID COUNT(*) STRSUM(SNAME)
-- ---------- --------------------------------------------------------------------------------
1 3 Степанов Фёдоров Иванов
2 1 Пивоваров
3 2 Сидоров Коновалов
|
|
