oracle columnname,Oracle string_agg（distinct columnname）在pl / sql中不起作用，仅在sqldeveloper中起作用...

-- Subject: string_agg + distinct/unique

-- Function string_agg creates a comma separated list of cursor values

-- Source : http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php

-- This function has been renamed to string_agg_unique and modified to return a unique list of values (unsort)

-- (The merge part has not been modified/supported)

CREATE OR REPLACE TYPE t_string_agg_unique AS OBJECT

(

g_string VARCHAR2(32767),

STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg_unique)

RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg_unique,

value IN VARCHAR2 )

RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg_unique,

returnValue OUT VARCHAR2,

flags IN NUMBER)

RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg_unique,

ctx2 IN t_string_agg_unique)

RETURN NUMBER

);

/

SHOW ERRORS

CREATE OR REPLACE TYPE BODY t_string_agg_unique IS

STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg_unique)

RETURN NUMBER IS

BEGIN

sctx := t_string_agg_unique(NULL);

RETURN ODCIConst.Success;

END;

MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg_unique,

value IN VARCHAR2 )

RETURN NUMBER IS

BEGIN

-- Concatenate string only when not already existing in the list (=unique)

IF instr ( SELF.g_string||',' , ','||value||',' ) = 0

THEN

SELF.g_string := self.g_string || ',' || value;

END IF ;

RETURN ODCIConst.Success;

END;

MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg_unique,

returnValue OUT VARCHAR2,

flags IN NUMBER)

RETURN NUMBER IS

BEGIN

returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');

RETURN ODCIConst.Success;

END;

MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg_unique,

ctx2 IN t_string_agg_unique)

RETURN NUMBER IS

BEGIN

SELF.g_string := SELF.g_string || ',' || ctx2.g_string;

RETURN ODCIConst.Success;

END;

END;

/

SHOW ERRORS

CREATE OR REPLACE FUNCTION string_agg_unique (p_input VARCHAR2)

RETURN VARCHAR2

PARALLEL_ENABLE AGGREGATE USING t_string_agg_unique;

/

SHOW ERRORS

-- example

select string_agg_unique ( fruit ) fruit_list from (

select * from (

select 'Apple' fruit from dual

union all

select 'Cherries' fruit from dual

union all

select 'Apple' fruit from dual

union all

select 'Lemon' fruit from dual

)

) ;

FRUIT_LIST

苹果，樱桃，柠檬