如何连接字符串? [英] how to concatenate strings?

问题描述

我使用的是Oracle 10g，并具有以下表结构: ID， 段落

I'm on Oracle 10g and have the following table structure: id, paragraph

我想按ID分组并连接段落.每个段落可能超过1500个字符.

I want to group by id and concatenate the paragraphs. Each paragraph maybe 1500 characters or more.

当我尝试wm_concat函数时，它抱怨字符串缓冲区太小.实际上，我在Oracle网站上尝试了许多示例，但所有示例均因字符串缓冲区太小而失败.

When I try the wm_concat function, it complains that the string buffer is too small. I actually tried many of the examples on Oracle's website and they all fail with the error the string buffer is too small.

select id, wm_concat(paragraph) from paragraphs group by id

我该如何解决?

推荐答案

因此，我猜测错误是 ORA-06502 ，我可以看到您可能会认为在这种情况下这不适用于您.

So, I'm guessing the error is ORA-06502 and I can see how you might think that this doesn't apply to you in this situation.

但是，这是wm_concat的错误.这是一个函数，并受PL \ SQL中Oracle的最大varchar长度(32,767和标准SQL中的4,000)的约束.不幸的是，由于wm_concat的工作方式或函数中的任何较低约束，或者由于您在select中使用它，您都无法获得接近上限的任何信息.

However, this is the fault of wm_concat. This is a function and is constrained by Oracle's maximum varchar length in PL\SQL of 32,767 and 4,000 in standard SQL. Unfortunately, I assume, because of the way that wm_concat works or because of any lower constraints within the function or because you're using it in a select you can't get anywhere near the upper limit.

还有另一个选项，

There is another option, stragg, Tom Kyte's string aggregate function. If we look at the following comparison between the two you'll see that they perform almost identically and that the limit of both is a length of around 4,000, i.e. the standard SQL maximum. stragg is slightly faster, probably due to caching.

SQL> set serveroutput on
SQL>
SQL> create table tmp_test ( a varchar2(30) );

Table created.

SQL> insert into tmp_test
  2   select object_name
  3     from all_objects
  4          ;

81219 rows created.

SQL>  commit ;

Commit complete.

SQL>
SQL> declare
  2
  3    i integer := 1;
  4    k number(10);
  5    v_stragg varchar2(32767);
  6    v_test varchar2(32767) := '';
  7    start_time timestamp;
  8
  9  begin
 10
 11    select count(*)
 12      into k
 13      from tmp_test;
 14
 15    for i in 1 .. k loop
 16      start_time := systimestamp;
 17      begin
 18
 19        select wm_concat(a) into v_test
 20          from tmp_test
 21         where rownum < i;
 22
 23      exception when others then
 24        dbms_output.put_line('wm_concat: ' || length(v_test));
 25        dbms_output.put_line(systimestamp - start_time);
 26        exit;
 27     end;
 28    end loop;
 29
 30    for i in 1 .. k loop
 31      start_time := systimestamp;
 32
 33      select stragg(a) into v_test
 34        from tmp_test
 35       where rownum < i;
 36
 37      if v_test = 'OVERFLOW' then
 38        dbms_output.put_line('stragg: ' || length(v_stragg));
 39        dbms_output.put_line(systimestamp - start_time);
 40        exit;
 41      else v_stragg := v_test;
 42      end if;
 43    end loop;
 44  end;
 45  /
wm_concat: 3976
+000000000 00:00:00.005886000
stragg: 3976
+000000000 00:00:00.005707000

PL/SQL procedure successfully completed.

至于解决，恐怕你做不到.一旦达到该极限就可以了.您将必须找到一种不同的汇总方法，或者问自己是否确实需要.

As for solving it, I'm afraid you can't. Once you hit that limit that's it. You'll have to find a different way of doing your aggregations or ask yourself if you really need to.

这篇关于如何连接字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！