oracle中删除所有用户表外键的方法

 

CREATE OR REPLACE PACKAGE BODY AAA_DELETE_F is

 

PROCEDURE MAIN
IS
--检索用户'DWP00001'的所有外键
   CURSOR getFkList
IS
    SELECT
      T.table_name,
      T.constraint_name
    FROM
      USER_CONSTRAINTS T
    WHERE
      T.CONSTRAINT_TYPE='R'
    AND
      T.OWNER = 'DWP00001'
    AND
      T.r_owner = 'DWP00001';
BEGIN
   FOR recFk IN getFkList LOOP

 

   --删除外键
     EXECUTE IMMEDIATE 'alter table ' || recFk.table_name
       || ' drop constraint ' || recFk.constraint_name;
   END LOOP;

 

EXCEPTION
WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;
END AAA_DELETE_F;