oracle 字符串 链接,字符串连接 - Oracle开发 - ITPUB论坛－中国专业的IT技术社区

yaanzy转的字符串求和函数我所知的原始出处是asktom

lastwinner的做法是利用虚拟构造树型结构, 结合oracle提供的sys_connect_by_path来达到求和的目的

上述的两个方法都不错, 但是有一个弊端, 就是会受到varchar2上限的限制, 字符串合并后的长度不能超过4000

要想超过4000这个长度限制, 我们自然就想到了clob

下面我将修改后的straggc函数提供给大家, 生成脚本见附件.

下面是三种方法的示例

另外这里先要说明一下对yaanzy转的方法中的两点看法

1. 字符串合并时的间隔符已经在type中固定为',', 这个在通用的函数不太好

2. returnValue := ltrim(self.total,','); 这句话可能会造成合并后的结果不正确,

造成的原因请详细参考ltrim函数的功能, 或是参见http://www.itpub.net/453299.html

下面的示例中使用了修改后的stragg(参见附件), 因此这两点看不到了, 大家如果有兴趣, 可以自己试试

为了简单说明问题期间, 我会把表中字段col全部合并, 大家使用时请根据自己的分组条件自行加上group by使用

[php]

测试环境

SQL> create table test_agg(col varchar2(10));

Table created

SQL> insert into test_agg select lpad(level,10,'0') from dual connect by level<=400;

400 rows inserted

SQL> commit;

Commit complete

----------------

[/php]

.首先, 我们看看原来的stragg函数的示例.

[php]

SQL> --

SQL> select stragg(','||col) from test_agg where rownum<10;

STRAGG(','||COL)

--------------------------------------------------------------------------------

,0000000001,0000000002,0000000003,0000000004,0000000005,0000000006,0000000007,00

1 row selected

SQL>--通过','||COL来实现字符串合并中间的的间隔符效果

SQL> select stragg(','||col), length(stragg(','||col)) from test_agg where rownum<10;

STRAGG(','||COL)                                                                 LENGTH(STRAGG(','||COL))

-------------------------------------------------------------------------------- ------------------------

,0000000001,0000000002,0000000003,0000000004,0000000005,0000000006,0000000007,00                       99

1 row selected

SQL> --每条记录10个字节, 400条记录, 再加上间隔符, 合并后长度超过4000, 我们来看一下

SQL> select stragg(','||col), length(stragg(','||col)) from test_agg;

select stragg(','||col), length(stragg(','||col)) from test_agg

ORA-06502: PL/SQL: 数字或值错误

ORA-06512: 在"JACKY.STRING_AGG_TYPE", line 17

ORA-06512: 在line 1

SQL> --显然, 这个报错是因为超过type中定义的4000而造成的

SQL> select stragg(col), length(stragg(col)) from test_agg;

STRAGG(COL)                                                                      LENGTH(STRAGG(COL))

-------------------------------------------------------------------------------- -------------------

00000000010000000002000000000300000000040000000005000000000600000000070000000008                4000

1 row selected

-------------

[/php]

.其次, 下面是lastwinner的实现方法, 我们仍然使用间隔符',', 并提供4000以内的成功使用和超出4000后的异常.

[php]

SQL>--

SQL> select max(sys_connect_by_path(col,',') )

2    from (select col, lag(col)over(order by col) as lagcol, rownum as rn from test_agg where rownum<=10)

3    start with col='0000000001'

4  connect by lagcol= prior col

5  /

MAX(SYS_CONNECT_BY_PATH(COL,',') )

--------------------------------------------------------------------------------

,0000000001,0000000002,0000000003,0000000004,0000000005,0000000006,0000000007,00

1 row selected

SQL>--这里补充说明一下, 由于sys_connect_by_path参数的要求, 间隔符无法省去, 大家如有兴趣可以试试

SQL>--超过4000字节的示例

SQL> select max(sys_connect_by_path(col,',') )

2    from (select col, lag(col)over(order by col) as lagcol, rownum as rn from test_agg)

3    start with col='0000000001'

4  connect by lagcol= prior col

5  /

select max(sys_connect_by_path(col,',') )

from (select col, lag(col)over(order by col) as lagcol, rownum as rn from test_agg)

start with col='0000000001'

connect by lagcol= prior col

ORA-01489: 字符串连接的结果过长

-------------------

[/php]

.最后, 下面是扩展为clob的字符串合并函数straggc的示例.

[php]

SQL> --

SQL> --同样, 也是还是使用间隔符','和4000以内的示例与超出4000的示例

SQL> select straggc(','||col), length(straggc(','||col)) from test_agg where rownum<=10;

STRAGGC(','||COL) LENGTH(STRAGGC(','||COL))

----------------- -------------------------

,0000000001,00000                       110

1 row selected

SQL> select straggc(','||col), length(straggc(','||col)) from test_agg;

STRAGGC(','||COL) LENGTH(STRAGGC(','||COL))

----------------- -------------------------

,0000000001,00000                      4400

1 row selected

SQL>

[/php]

最后, 谢谢大家的阅读.