oracle list agg,SQL 列转行/list agg 列超长处理方法

CREATE USER tester IDENTIFIED BY tester ;

grant   dba,resource,connect to tester;

conn tester/tester

create table t24(num number,a varchar2(30));

begin

for i in 1.. 1000

loop

insert into t24 values(1,'aaaa');

end loop;

commit;

end;

/

select num,string_agg(a) from t24 group by num  ;

CREATE OR REPLACE TYPE t_string_agg AS OBJECT

(

g_string  VARCHAR2(32767),

STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)

RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,

value  IN      VARCHAR2 )

RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,

returnValue  OUT  VARCHAR2,

flags        IN   NUMBER)

RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,

ctx2  IN      t_string_agg)

RETURN NUMBER

);

/

SHOW ERRORS

CREATE OR REPLACE TYPE BODY t_string_agg IS

STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)

RETURN NUMBER IS

BEGIN

sctx := t_string_agg(NULL);

RETURN ODCIConst.Success;

END;

MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,

value  IN      VARCHAR2 )

RETURN NUMBER IS

BEGIN

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

RETURN ODCIConst.Success;

END;

MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,

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,

ctx2  IN      t_string_agg)

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 (p_input VARCHAR2)

RETURN VARCHAR2

PARALLEL_ENABLE AGGREGATE USING t_string_agg;

/

SHOW ERRORS

SELECT num, LISTAGG(a, ',') WITHIN GROUP (ORDER BY a) AS ll FROM   t24 GROUP BY num;

select num,

to_clob(ltrim(MAX(sys_connect_by_path(a, '||''|''||')) KEEP(dense_rank last order by num), '||''|''||'))

from (select num,

row_number() OVER(PARTITION BY num ORDER BY a) curr,

row_number() OVER(PARTITION BY num ORDER BY a) - 1 prev,

a

from t24)

group by num

connect by prev = prior curr

and num = prior num

start with curr = 1;

tester@RAC11G>SELECT num, LISTAGG(a, ',') WITHIN GROUP (ORDER BY a) AS ll FROM   t24 GROUP BY num;

SELECT num, LISTAGG(a, ',') WITHIN GROUP (ORDER BY a) AS ll FROM   t24 GROUP BY num

*

ERROR at line 1:

ORA-01489: result of string concatenation is too long

tester@RAC11G>select num,

to_clob(ltrim(MAX(sys_connect_by_path(a, '||''|''||')) KEEP(dense_rank last order by num), '||''|''||'))

3    from (select num,

4                 row_number() OVER(PARTITION BY num ORDER BY a) curr,

5                 row_number() OVER(PARTITION BY num ORDER BY a) - 1 prev,

a

from t24)

8   group by num

9  connect by prev = prior curr

10         and num = prior num

11   start with curr = 1;

ERROR:

ORA-01489: result of string concatenation is too long

no rows selected

解决方案很朴实 ~

如下

create table test as select lpad('a',8000,'a') a from dual; select dbms_utility.get_hash_value( a||'_'||a,0,1073741824) from test; select  a||'_'||a  from test; alter table test add c varchar2(4000); update test set c=  lpad('a',8000,'a'); commit; set serveroutput on declare  aa varchar2(32767); cc varchar2(32767); begin select a,c into aa,cc from test; --aa :=aa||lpad('a',8000,'a'); dbms_output.put_line(dbms_utility.get_hash_value(aa||'0'||cc,0,1073741824)); dbms_output.put_line(dbms_utility.get_hash_value(aa||'1'||cc,0,1073741824)); dbms_output.put_line(dbms_utility.get_hash_value( lpad('a',3999,'a'),0,1073741824)); end; / 105111839 622607691 621653836