两表关联更新

环境介绍：

根据业务表的object_id 和 对照表中的dm_old的对照关系，更新业务表的object_id为 对照表中的dm_new

--1. 对照表
1.1 drop table hy0921_tmp purge;
1.2 create table hy0921_tmp(dm_old number,dm_new number,dm_name varchar2(50)); 
1.3 insert into hy0921_tmp(dm_old,dm_name) select t1.OBJECT_ID,t1.OBJECT_NAME from dba_objects t1 where  t1.owner='SCOTT' ;

1.4 对照表中数据如下：

select t1.dm_old,t1.dm_new,t1.dm_name from hy0921_tmp t1  ;

1.5 更新对照表中的字段 dm_new

update hy0921_tmp set dm_new=trunc(dbms_random.value(1,100));

1.6 再次确认对照表的数据

select t1.dm_old,t1.dm_new,t1.dm_name from hy0921_tmp t1  ;

 

 --2. 业务表

2.1 drop table hy0921 purge;

2.2 insert into hy0921 select * from dba_objects t1  where  t1.owner='SCOTT' ;

2.3 select t1.object_id,t1.object_name from hy0921 t1

--3 关联对照表和业务表，确认数据

select distinct 'hy0921',
       t1.object_id,
       t1.object_name,
       'hy0921_tmp',
       t2.dm_old,
       t2.dm_new,
       t2.dm_name
  from hy0921 t1, hy0921_tmp t2
 where t1.object_id = t2.dm_old --and t1.object_name='SYS_LOG'
 order by t1.object_id asc;

--4 对照表和业务表关联更新
update hy0921 t1
   set t1.object_id =
       (select dm_new from hy0921_tmp where dm_old = t1.object_id)
 where exists (select 1 from hy0921_tmp where dm_old = t1.object_id);

--5 再次关联对照表和业务表，确认数据

select distinct 'hy0921',
       t1.object_id,
       t1.object_name,
       'hy0921_tmp',
       t2.dm_old,
       t2.dm_new,
       t2.dm_name
  from hy0921 t1, hy0921_tmp t2
 where t1.object_id = t2.dm_old      --此处还用的是  对照表中的 dm_old ，肯定没数据
 order by t1.object_id asc;

 select distinct 'hy0921',
       t1.object_id,
       t1.object_name,
       'hy0921_tmp',
       t2.dm_old,
       t2.dm_new,
       t2.dm_name
  from hy0921 t1, hy0921_tmp t2
 where t1.object_id = t2.dm_new   --此处还用的是  对照表中的 dm_new ，数据正常
 order by t1.object_id asc;

 

 以下sql效率很好，但是 重复和执行 结果会变！！！

declare
  v_rowid varchar2(50);
  type ridarray is table of rowid;
  type obj#array is table of hy0921_tmp.dm_old%type;
  type objnamearray is table of hy0921_tmp.dm_name%type;

  my_rids    ridarray;
  my_obj#    obj#array;
  my_objname objnamearray;

  cursor my_cur is
    select t2.rowid, t1.dm_new, t2.object_name
      from hy0921_tmp t1, hy0921 t2
     where t1.dm_old = t2.object_id; --两表的关联条件

begin

  open my_cur;

  loop
    fetch my_cur bulk collect
      into my_rids, my_obj#, my_objname limit 100;
 
    forall i in 1 .. my_rids.count
    
      update hy0921 t2
         set t2.object_id = my_obj#(i)
       where rowid = my_rids(i);
 
    commit;
 
    exit when my_cur%notfound;
  end loop;

  close my_cur;

end;
--已完成，耗时 261.38 秒

 

over