mysql交叉表查询列转行_关于ORACLE列转行的问题

展开全部

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

希望能解决你的问题。--准备数据

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;

/