这就是ID字段带有实际意义产生的后遗症,设计的时候就该把ID分为3个部分: ID、DATE、TAG,如: ID = 9999 DATE = 20090628 TAG = 3302030089
declare v_count number:=0; rd varchar2(4); begin for cur1 in (select * from a)loop select count(1) into v_count from b where id=cur1.id; if v_count=0 then insert into b values(cur1.id,....); else loop rd:=to_char(round(dbms_random.value(0,9999)),'fm0000'); select count(1) into v_count from b where id=substr(cur1.id,1,18)||rd; if v_count=0 then select count(1) into v_count from a where id=substr(cur1.id,1,18)||rd; if v_count=0 then insert into b values(substr(cur1.id,1,18)||rd,....); exit; end if; end if; end loop; end if; end loop; end;
分步使用临时表方式生成新ID,简单又清晰,供参考,效率如何还待楼主测试: 1)建立A,B表字段ID的全量数据: create table id_a_b as select id from a union all select id from b; 2)统计重复的ID: create table id_a_b_repeat as select id from id_a_b group by id having count(*) > 1; 3)按前18位,取A,B表字段ID后4位的最大值: create table id_a_b_max as select substr(id,1,18) id_18,max(substr(id,19,4)) tail_max from id_a_b group by substr(id,1,18); 4)取重复ID的新ID: create table id_a_b_repeat_new as select id old_id, id_18||LPAD(tail_max+1,4,0) new_id from id_a_b_max a,id_a_b_repeat b where a.id like id_18||'%'; 5)id_a_b_repeat_new关联A表导入B表,实现方法很多。
只把id有重复的插入B表,不重复的留在a表不作处理? declare v_count number:=0; rd varchar2(4); v_id varchar2(22); begin for cur1 in (select * from a for update)loop select count(1) into v_count from b where id=cur1.id; if v_count<>0 then
loop rd:=to_char(round(dbms_random.value(0,9999)),'fm0000'); v_id:=substr(cur1.id,1,18)||rd; select count(1) into v_count from b where id=v_id; if v_count=0 then select count(1) into v_count from a where id=v_id; if v_count=0 then insert into b values(v_id....); delete from a where id=cur1.id; exit; end if; end if; end loop; end if; end loop; end;
如:max(substr(b.id,19,4))+substr(a.id,19,4)
ID、DATE、TAG,如:
ID = 9999
DATE = 20090628
TAG = 3302030089
v_count number:=0;
rd varchar2(4);
begin
for cur1 in (select * from a)loop
select count(1) into v_count from b where id=cur1.id;
if v_count=0 then
insert into b values(cur1.id,....);
else
loop
rd:=to_char(round(dbms_random.value(0,9999)),'fm0000');
select count(1) into v_count from b where id=substr(cur1.id,1,18)||rd;
if v_count=0 then
select count(1) into v_count from a where id=substr(cur1.id,1,18)||rd;
if v_count=0 then
insert into b values(substr(cur1.id,1,18)||rd,....);
exit;
end if;
end if;
end loop;
end if;
end loop;
end;
1)建立A,B表字段ID的全量数据:
create table id_a_b as select id from a union all select id from b;
2)统计重复的ID:
create table id_a_b_repeat as select id from id_a_b group by id having count(*) > 1;
3)按前18位,取A,B表字段ID后4位的最大值:
create table id_a_b_max as select substr(id,1,18) id_18,max(substr(id,19,4)) tail_max from id_a_b group by substr(id,1,18);
4)取重复ID的新ID:
create table id_a_b_repeat_new as select id old_id, id_18||LPAD(tail_max+1,4,0) new_id from id_a_b_max a,id_a_b_repeat b
where a.id like id_18||'%';
5)id_a_b_repeat_new关联A表导入B表,实现方法很多。
忘记说了,大狭可以在补充点吗???现在我只想把重复的后面4位改掉,不重复不做处理,然后只把重复的插入B表里,插入B表以后A表的此条信息删除..
declare
v_count number:=0;
rd varchar2(4);
v_id varchar2(22);
begin
for cur1 in (select * from a for update)loop
select count(1) into v_count from b where id=cur1.id;
if v_count<>0 then
loop
rd:=to_char(round(dbms_random.value(0,9999)),'fm0000');
v_id:=substr(cur1.id,1,18)||rd;
select count(1) into v_count from b where id=v_id;
if v_count=0 then
select count(1) into v_count from a where id=v_id;
if v_count=0 then
insert into b values(v_id....);
delete from a where id=cur1.id;
exit;
end if;
end if;
end loop;
end if;
end loop;
end;
兄弟,我还一个没节的贴,和这一模一样,叫/? 高分求救高手解决数据从一个表插到另一个表 [Oracle][基础和管理] / 你去随便说两句我把分都给你.