级联更新数据库的所有表的主键(从1开始更新,关联关系会自动更新),sql

时间:2021-02-20 13:55:04

 

---1.删除外键的存储过程,此过程会删除所有表的关联关系   

create or replace procedure deleteFKs

is

begin

  declare

   my_sql2 clob;

   v_start2 number(10);

   v_end2 number(10);

   v_cur2 clob;

   begin

     select wmsys.wm_concat(sql2) into my_sql2 from

     (select 'alter table '||table_name||' drop constraint '||constraint_name||';' sql2 from user_constraints where constraint_type='R') A;

     my_sql2:=replace(my_sql2,',','');

    Dbms_Output.Put_Line(my_sql2);

     v_start2:=instr(my_sql2,';') ;

     while v_start2<>0 loop

         v_cur2:=substr(my_sql2,0,v_start2);

        Dbms_Output.Put_Line(replace(v_cur2,';',''));

         EXECUTE IMMEDIATE replace(v_cur2,';','');

         my_sql2:=substr(my_sql2,v_start2+1);

         v_start2:=instr(my_sql2,';') ;

     end loop;

   end;

end deleteFKs;

 

 

 ---2.将表名跟id放入临时表中做筛选

 create table temp_my_table as

 select cu.TABLE_NAME,wmsys.wm_concat(cu.COLUMN_NAME) as two_id from user_cons_columnscu, user_constraints au

 where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name in  (select table_name from user_tables)  group by cu.TABLE_NAME;  

 

 

---3.筛选出单列主键和联合主键的表,联合主键的表不做sequence更新,随后在根据其他关联表更新

create table temp_one_id as

select t.table_name,t.two_id as COLUMN_NAME from temp_my_table t where t.two_id not like '%,%';

 

create table temp_two_id as

select t.table_name,t.two_id from temp_my_table t where t.two_id  like '%,%';

 

---4.创建临时表sql,下面的sql语句会获取所有标的表名跟主键名,用于创建临时表

create table temp_table_sql

 as

 select

      'create table temp_'||t.TABLE_NAME||' as select rownumas newid,'||t.COLUMN_NAME||' as old_id from '|| t.TABLE_NAME as sql_temp

       from  temp_one_id t;

      

 ---5.根据上面临时表sql语句创建临时表

declare

   module_sql varchar(2000); 

   cursor moduleCursor is select * from temp_table_sql;

  begin

    for moduleCursorRecord in moduleCursor loop 

      Dbms_Output.Put_Line( moduleCursorRecord.sql_temp);  

        execute immediatemoduleCursorRecord.sql_temp;  

    end loop; 

    commit;

 end;     

 

---6_1.更新表数据sql,生成的sql将用于执行更新外键跟主键

create table temp_sql

 as

 select

      'update '||a.table_name||' c set c.'||m.column_name||'=(select tm.newidfrom temp_'||a.TABLE_NAME||' tm where c.'||m.column_name||'=tm.old_id)' as sql1,

      'update '||p.table_name||' c set c.'||c.column_name||'=(select tm.newidfrom temp_'||p.TABLE_NAME||' tm where c.'||c.column_name||'=tm.old_id)'as sql2,

      'update '|| a.table_name||' c set c.'||b.column_name||'=(select tm.newidfrom temp_'||p.TABLE_NAME||' tm where c.'||b.column_name||'=tm.old_id) 'as sql3,

      'alter table '||a.table_name||' add constraint '||a.constraint_name||' foreign key('||b.column_name||') references ' ||p.table_name||'('||c.column_name||') enable' as sql4

       from user_constraints a,user_cons_columns b,user_constraints p, user_cons_columns c,user_cons_columns m where a.constraint_type='R'

       and a.constraint_name=b.constraint_name and a.r_constraint_name= p.constraint_name and c.constraint_name =(select d.constraint_name from user_constraintsd  

       where d.table_name =p.table_name andd.constraint_type  ='P') and m.constraint_name =(select n.constraint_name from user_constraintsn 

       where n.table_name =a.table_name andn.constraint_type  ='P') and a.TABLE_NAME not in (select t.table_name from temp_two_id t) and p.TABLE_NAME not in (select t.table_name from temp_two_id t);

 

---6_2.对于中间表先找到关联关系,最后更新

create table temp_sql_two

 as

 select

      'update '||a.table_name||' c set c.'||m.column_name||'=(select tm.newidfrom temp_'||a.TABLE_NAME||' tm where c.'||m.column_name||'=tm.old_id)' as sql1,

      'update '||p.table_name||' c set c.'||c.column_name||'=(select tm.newidfrom temp_'||p.TABLE_NAME||' tm where c.'||c.column_name||'=tm.old_id)'as sql2,

      'update '|| a.table_name||' c set c.'||b.column_name||'=(select tm.newidfrom temp_'||p.TABLE_NAME||' tm where c.'||b.column_name||'=tm.old_id) 'as sql3,

      'alter table '||a.table_name||' add constraint '||a.constraint_name||' foreign key('||b.column_name||') references ' ||p.table_name||'('||c.column_name||') enable' as sql4

       from user_constraints a,user_cons_columns b,user_constraints p, user_cons_columns c,user_cons_columns m where a.constraint_type='R'

       and a.constraint_name=b.constraint_name and a.r_constraint_name= p.constraint_name and c.constraint_name =(select d.constraint_name from user_constraintsd  

       where d.table_name =p.table_name andd.constraint_type  ='P') and m.constraint_name =(select n.constraint_name from user_constraintsn 

       where n.table_name =a.table_name andn.constraint_type  ='P') and (a.TABLE_NAME  in (select t.table_name from temp_two_id t) or p.TABLE_NAME  in (select t.table_name from temp_two_id t));

 

---7.对更新语句去重处理,这个处理防止多层外键关系

create table temp_no_replace(update_sql varchar(1000));

insert into temp_no_replace select sql1 from temp_sql;

insert into temp_no_replace select sql2 from temp_sql;

insert into temp_no_replace select sql3 from temp_sql;

commit;

 

create table temp_no_replace2(update_sql varchar(1000));

insert into temp_no_replace2 select distinct(t.update_sql) from temp_no_replace t;

commit;

create table temp_no_replace3(update_sql varchar(1000));

insert into temp_no_replace3 select sql4 from temp_sql;

commit;

 

---8_1.根据上面更新sql语句数据更新(只更新对应数据)

declare

   module_sql varchar(2000); 

   cursor moduleCursor is select update_sql from temp_no_replace2;

  begin

    deleteFKs;

    for moduleCursorRecord in moduleCursor loop 

        --Dbms_Output.Put_Line(moduleCursorRecord.update_sql);          

        execute immediatemoduleCursorRecord.update_sql;     

    end loop; 

    commit;

 end; 

 

 ---8_2.根据上面更新sql语句数据更新(重新建立关联关系)

declare

   module_sql varchar(2000); 

   cursor moduleCursor is select update_sql from temp_no_replace3;

  begin

    deleteFKs;

    for moduleCursorRecord in moduleCursor loop 

        --Dbms_Output.Put_Line(moduleCursorRecord.update_sql);          

        execute immediatemoduleCursorRecord.update_sql;     

    end loop; 

    commit;

 end; 

 

---9.更新联合主键的表数据,即中间表

create table temp_no_replace_two(update_sql_two varchar(1000));

insert into temp_no_replace_two select distinct(sql3) from temp_sql_two;

insert into temp_no_replace_two select distinct(sql4) from temp_sql_two;

commit;

 

--10.执行sql更行中间表

declare

   module_sql varchar(2000); 

   cursor moduleCursor is select update_sql_two from temp_no_replace_two;

  begin

    for moduleCursorRecord in moduleCursor loop 

        execute immediatemoduleCursorRecord.update_sql_two; 

       Dbms_Output.Put_Line( moduleCursorRecord.update_sql_two);              

    end loop; 

    commit;

 end; 

 

---11.删除所有临时表  

declare p_sql varchar2(4000);

begin

  for r in (select a.TABLE_NAME from user_tables a where a.TABLE_NAME like 'TEMP_%') loop

    p_sql:='drop table '||r.table_name;

    execute immediate p_sql;

  end loop;

end;