生成指令碼，得到所有表的外來鍵約束，然後刪除並重建這些約束

僅做記錄，可以得到所有的外來鍵約束，生成建立和刪除這些約束的指令碼，在資料庫管理中有點用處

[@more@]CREATE OR REPLACE PROCEDURE GENERATE_FK IS
CURSOR C IS
SELECT TABLE_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME IN
(SELECT DISTINCT R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE R_CONSTRAINT_NAME IS NOT NULL)
ORDER BY TABLE_NAME;
BEGIN
FOR R IN C LOOP
DBMS_OUTPUT.PUT_LINE('table_name : ' || R.TABLE_NAME);
DBMS_OUTPUT.PUT_LINE('drop script : ');
FOR R1 IN (SELECT 'alter table ' || TABLE_NAME || ' drop constraint ' ||
CONSTRAINT_NAME || ';' AS FK_SQL
FROM USER_CONSTRAINTS
WHERE R_CONSTRAINT_NAME IN
(SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P'
AND TABLE_NAME = R.TABLE_NAME)) LOOP
DBMS_OUTPUT.PUT_LINE(R1.FK_SQL);
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('create script : ');
FOR R2 IN (SELECT 'alter table ' || C.TABLE_NAME || ' add constraint ' ||
C.CONSTRAINT_NAME || ' foreign key (' || A.COLUMN_NAME ||
') REFERENCES ' || B.TABLE_NAME || '(' ||
D.COLUMN_NAME || ');' AS FK_SQL
FROM USER_CONS_COLUMNS A,
USER_CONSTRAINTS B,
USER_CONSTRAINTS C,
USER_CONS_COLUMNS D
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_NAME = C.R_CONSTRAINT_NAME
AND C.CONSTRAINT_NAME = D.CONSTRAINT_NAME
AND B.TABLE_NAME = R.TABLE_NAME) LOOP
DBMS_OUTPUT.PUT_LINE(R2.FK_SQL);
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
END GENERATE_FK;