Oracle中的listagg函数和Mysql中group_concat的用法

使用 listagg() WITHIN GROUP () 将多行合并成一行(比较常用)

···

SELECT

T .DEPTNO,

listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME) names

FROM

SCOTT.EMP T

WHERE

T .DEPTNO = '20'

GROUP BY

T .DEPTNO

···

效果：

先将varchar2转换为clob再转换为blob。

http://blog.csdn.net/wbo112/article/details/9041575。

希望能解决你的问题。

--准备数据

create table a(bh number, sj date, sl number)

create table b(bh number, zj blob)

insert into a values(1001, sysdate, 30)

insert into a values(1002, sysdate, 31)

insert into a values(1001, sysdate, 32)

insert into a values(1003, sysdate, 34)

insert into a values(1003, sysdate, 35)

insert into a values(1004, sysdate, 36)

insert into a values(1004, sysdate, 38)

insert into a values(1005, sysdate, 23)

insert into a values(1005, sysdate, 30)

insert into a values(1006, sysdate, 35)

insert into a values(1006, sysdate, 30)

insert into a values(1001, sysdate, 30)

--这个方法不知道能否解决你这个4k限制的问题

create or replace procedure insert_b is

  cursor a_cursor is select * from a order by bh

  a_record a%rowtype

  temp_bh a.bh%type := 0

  temp_zj varchar2(32767)

begin

  open a_cursor

  loop

    fetch a_cursor into a_record

    --插入最后的一条记录

    if a_cursor%notfound then

      insert into b values(temp_bh, c2b(to_clob(temp_zj)))

      exit

    end if

    if temp_bh != a_record.bh then

      --插入上一条的记录值

      if temp_bh != 0 then

        insert into b values(temp_bh, c2b(to_clob(temp_zj)))

      end if

      temp_bh := a_record.bh

      --temp_zj := concat(concat(concat(concat(concat(concat('编号：', a_record.bh), '，时间：'), to_char(a_record.sj, 'yyyy-mm-dd')), '，数量：'), a_record.sl), '；')

      temp_zj := '编号：' || a_record.bh || '，时间：' || to_char(a_record.sj, 'yyyy-mm-dd') || '，数量：' || a_record.sl || '；'

    elsif temp_bh = a_record.bh then

      --temp_zj := concat(concat(concat(concat(concat(temp_zj, '时间：'), to_char(a_record.sj, 'yyyy-mm-dd')), '，数量：'), a_record.sl), '；')

      temp_zj := temp_zj || '时间：' || to_char(a_record.sj, 'yyyy-mm-dd') || '，数量：' || a_record.sl || '；'

    end if

  end loop

  close a_cursor

end

/

--了解了下listagg函数，这个比较简洁，不知道会不会出现你所说的4k限制问题。由你这个问题也让我学到了11g的新函数

create or replace procedure insert_b is

  cursor a_cursor is

    select bh,

           '编号：' || bh || '，' ||

           listagg('时间：' || to_char(sj, 'yyyy-mm-dd') || '，数量：' || sl,

                   '；') within group(order by bh) || '。' as res

      from a

     group by bh

  v_bh  number

  v_res varchar2(32767)

begin

  open a_cursor

  loop

    fetch a_cursor

      into v_bh, v_res

    exit when a_cursor%notfound

    --insert into b values (v_bh, c2b(to_clob(v_res)))

    insert into b values(v_bh, to_blob(rawtohex(v_res)))

  end loop

  close a_cursor

end

/

--更新数据

begin

  insert_b

end

/

--查询结果

select bh, to_char(b2c(zj)) from b

--将clob类型转换为blob类型（二进制转换）

create or replace function c2b(src clob default empty_clob()) return blob is

  dest          blob

  src_len       number := dbms_lob.getlength(src)

  dest_offset   number := 1

  src_offset    number := 1

  amount_c      integer := dbms_lob.lobmaxsize

  blob_csid     number := dbms_lob.default_csid

  lang_ctx      integer := dbms_lob.default_lang_ctx

  warning       integer

begin

  if src_len > 0 then

    --将dest建立在用户的临时表空间中，true表示将dest读到缓冲区。此处相当于初始化dest

    dbms_lob.createtemporary(dest, true)

    --以readwrite模式打开dest

    dbms_lob.open(dest, dbms_lob.lob_readwrite)

    --读取src，转换字符数据为特定字符集格式，并将转换后的数据写入dest中

    dbms_lob.converttoblob(dest,         --目标blob

                           src,          --源clob

                           amount_c,     --指定要转换的字节数

                           dest_offset,  --指定目标lob的偏移位置（字节或字符）

                           src_offset,   --指定源lob的偏移位置（字节或字符）

                           blob_csid,    --指定字符集标识号

                           lang_ctx,     --指定语言上下文

                           warning)     --存放警告信息

  else

    select empty_blob() into dest from dual

  end if

  return dest

end c2b

/

                           

--将blob类型转换为clob类型

--varchar2类型可直接转换为clob类型

create or replace function b2c(src blob) return clob is

  dest varchar2(32767)

  temp varchar2(32767)

  v_start pls_integer := 1

  v_buffer pls_integer := 4000

begin

  if dbms_lob.getlength(src) is null then

    return ''

  end if

  dest := ''

  for i in 1..ceil(dbms_lob.getlength(src) / v_buffer) loop

    --当转换出来的字符串乱码时，可尝试使用注释掉的函数

    --temp := utl_raw.cast_to_varchar2(utl_raw.convert(dbms_lob.substr(src, v_buffer, v_start), 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK', 'AMERICAN_THE NETHERLANDS.UTF8'))

    temp := utl_raw.cast_to_varchar2(dbms_lob.substr(src, v_buffer, v_start))

    dest := dest || temp

    v_start := v_start + v_buffer

  end loop

  return dest

end b2c

/

用cast就可,但要填适当的类型,示例:

select cast(11 as unsigned int) /*整型*/

select cast(11 as decimal(10,2)) /*浮点型*/

---