禁用Schema下面表的外键约束

有时候需要禁用表之间的外键约束，然后重建表或者导入数据，下面share一下两个procedure用来做外键禁用。
-- 禁用schema下面所有外键
create or replace procedure p_fk_status( an_status number)
as 
cur_handle number;
cur_result number;
s_sql varchar2(4000);
s_error varchar2(4000);
s_ip varchar2(20);
/*
CURSOR cur_fk IS  
select a1.table_name,a1.CONSTRAINT_NAME 
from user_constraints  a1 
where  a1.constraint_type = 'R';
*/


CURSOR cur_fk IS  
select a1.constraint_name,a3.table_name as table_name,a3.column_name as column_name,a4.table_name as p_table_name ,a4.column_name as p_column_name
from user_constraints a1
join user_constraints a2
on (a2.constraint_type in( 'P','U') and a1.R_CONSTRAINT_NAME = a2.constraint_name)
join user_cons_columns a3
on (a1.constraint_name = a3.constraint_name) 
join user_cons_columns a4
on (a2.constraint_name = a4.constraint_name); 

begin
  dbms_output.enable(100000);
  s_error:='coming in ...';
 
    
if an_status = 0 then     
    cur_handle := DBMS_SQL.OPEN_CURSOR ;
    s_error:= 'alter table disable fk begin';
    FOR cur_1 IN cur_fk LOOP
  BEGIN 
    s_sql:='ALTER TABLE '||cur_1.TABLE_NAME||' DISABLE CONSTRAINT '||cur_1.CONSTRAINT_NAME;
    DBMS_SQL.PARSE(cur_handle,s_sql,DBMS_SQL.V7) ;
    cur_result:= DBMS_SQL.EXECUTE(cur_handle);
  END ;
END LOOP ;
s_error:= 'alter table disable fk over';
    DBMS_SQL.CLOSE_CURSOR(cur_handle) ;
end if;
if an_status = 1 then     
    cur_handle := DBMS_SQL.OPEN_CURSOR ;
    s_error:= 'alter table enable begin';
    FOR cur_1 IN cur_fk LOOP
  BEGIN 
    s_sql:='ALTER TABLE '||cur_1.TABLE_NAME||' enable CONSTRAINT '||cur_1.CONSTRAINT_NAME;
begin
    DBMS_SQL.PARSE(cur_handle,s_sql,DBMS_SQL.V7) ;
    cur_result:= DBMS_SQL.EXECUTE(cur_handle);
EXCEPTION WHEN OTHERS THEN
s_error := 'select distinct '||cur_1.column_name ||' from '||cur_1.table_name||' where ' ||cur_1.column_name ||' not in (select '|| cur_1.p_column_name||' from '||cur_1.p_table_name ||');';
DBMS_OUTPUT.PUT_LINE(s_error);
s_error := 'update '|| cur_1.table_name ||' set ' || cur_1.column_name ||' = where ' ||cur_1.column_name ||' not in (select '|| cur_1.p_column_name||' from '||cur_1.p_table_name ||');';
DBMS_OUTPUT.PUT_LINE(s_error);
DBMS_OUTPUT.PUT_LINE(sqlerrm);
end;
  END ;
END LOOP ;
    DBMS_SQL.CLOSE_CURSOR(cur_handle) ;
    s_error:= 'alter table enable end.';
    commit;
end if;    
    EXCEPTION WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(s_error||sqlerrm);
     --raise_application_error(-20000,sqlerrm);
end;
/

-- 禁用某个表相关的外键约束
create or replace procedure p_fk_status_by_table( as_table_name varchar2,an_status number)
as 
cur_handle number;
cur_result number;
s_sql varchar2(4000);
s_error varchar2(4000);
s_ip varchar2(20);
CURSOR cur_fk IS  
select a1.constraint_name,a3.table_name as table_name,a3.column_name as column_name,a4.table_name as p_table_name ,a4.column_name as p_column_name
from user_constraints a1
join user_constraints a2
on (a2.constraint_type in( 'P','U')  and a2.table_name =upper(as_table_name) and a1.R_CONSTRAINT_NAME = a2.constraint_name)
join user_cons_columns a3
on (a1.constraint_name = a3.constraint_name) 
join user_cons_columns a4
on (a2.constraint_name = a4.constraint_name); 


begin


  dbms_output.enable(100000);
  s_error:='coming in ...';
 
    
if an_status = 0 then     
    cur_handle := DBMS_SQL.OPEN_CURSOR ;
    s_error:= 'alter table disable fk begin';
    FOR cur_1 IN cur_fk LOOP
  BEGIN 
    s_sql:='ALTER TABLE '||cur_1.TABLE_NAME||' DISABLE CONSTRAINT '||cur_1.CONSTRAINT_NAME;
    DBMS_SQL.PARSE(cur_handle,s_sql,DBMS_SQL.V7) ;
    cur_result:= DBMS_SQL.EXECUTE(cur_handle);
  END ;
END LOOP ;
s_error:= 'alter table disable fk over';
    DBMS_SQL.CLOSE_CURSOR(cur_handle) ;
end if;
if an_status = 1 then     
    cur_handle := DBMS_SQL.OPEN_CURSOR ;
    s_error:= 'alter table enable begin';
    FOR cur_1 IN cur_fk LOOP
  BEGIN 
    s_sql:='ALTER TABLE '||cur_1.TABLE_NAME||' ENABLE CONSTRAINT '||cur_1.CONSTRAINT_NAME;
    begin
DBMS_SQL.PARSE(cur_handle,s_sql,DBMS_SQL.V7) ;
    cur_result:= DBMS_SQL.EXECUTE(cur_handle);
EXCEPTION WHEN OTHERS THEN
s_error := 'select distinct '||cur_1.column_name ||' from '||cur_1.table_name||' where ' ||cur_1.column_name ||' not in (select '|| cur_1.p_column_name||' from '||cur_1.p_table_name ||');';
DBMS_OUTPUT.PUT_LINE(s_error);
s_error := 'update '|| cur_1.table_name ||' set ' || cur_1.column_name ||' = where ' ||cur_1.column_name ||' not in (select '|| cur_1.p_column_name||' from '||cur_1.p_table_name ||');';
DBMS_OUTPUT.PUT_LINE(s_error);
DBMS_OUTPUT.PUT_LINE(sqlerrm);
end;
  END ;
END LOOP ;
    DBMS_SQL.CLOSE_CURSOR(cur_handle) ;
    s_error:= 'alter table enable end.';
    commit;
end if;    
    EXCEPTION WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(s_error||sqlerrm);
     --raise_application_error(-20000,sqlerrm);
end p_fk_status_by_table;
/