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

来源：互联网发布：淘宝怎么弄淘口令编辑：程序博客网时间：2024/04/30 15:05

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

0 0