---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;