我用的oracle的给你写出来,哎,没写过存储过程的孩子伤不起啊,真心麻烦,还是java好使,能多给点分就多给点吧。。
[code="sql"]
declare
sub_str varchar2(4000);
insert_str varchar2(4000);
seq int := 0;
pos1 int := 1;
pos2 int := 1;
cursor c is
select * from a;
r c%rowtype;
begin
open c;
--using loop to get the searching result
loop
fetch c
into r;
exit when c%notfound;
--init pos1,pos2
pos1 := 1;
pos2 := 1;
seq := 0;
while pos2 != 0 and pos1 <= length(r.result) loop
pos2 := instr(r.result, ';', pos1);
seq := seq + 1;
--judge the pos2 ,if equals zero means that the searching get end
if pos2 != 0 then
sub_str := substr(r.result, pos1, pos2 - pos1);
else
sub_str := substr(r.result, pos1);
end if;
--e.g. insert into B (id,seq,num,space) values (1,1,'a','b')
insert_str := 'insert into B (id,seq,num,space) values ('
|| r.id || ',' || seq || ',' ||chr(39)||
substr(sub_str, 1, instr(sub_str, '*')-1) ||chr(39)|| ','||chr(39) ||
substr(sub_str, instr(sub_str, '*') + 1) ||chr(39)|| ')' ;
--dbms_output.put_line(insert_str);
execute immediate insert_str;
-- next search position add 1
pos1 := pos2 + 1;
end loop;
end loop;
close c;
commit;
end;
[/code]