Oracle '批量'禁用外來鍵的儲存過程

maohaiqing0304發表於2015-06-12
Oracle儲存過程

標題：Oracle '批量'禁用外來鍵的儲存過程 

作者：ｌōττéｒｙ©版權所有[文章允許轉載,但必須以連結方式註明源地址,否則追究法律責任.]

 CREATE OR REPLACE PROCEDURE P_CONSTRAINT(V_TYPE VARCHAR2) IS
 
  ----------P_CONSTRAINT（外來鍵約束禁用、啟用 '適用於truncate table 報ORA-02266錯時用到'）-------------

  /*----------------------------------------------------------
  | AUTHOR：毛海晴                                           |          
  | USERS ：外來鍵約束禁用、啟用'清空資料時用到'               |
  | BLOG  : http://blog.itpub.net/28602568/viewspace-1696583/|
  -----------------------------------------------------------*/
  /*
  註釋：
  由於在TRUNCATE表資料時有主外來鍵約束，所以要將外來鍵約束禁用。
  判斷約束是否是外來鍵有2種：
    1、USER_CONSTRAINTS檢視中約束型別CONSTRAINT_TYPE = 'R'；
    2、R_CONSTRAINT_NAME IS NOT NULL;
  
  */
  P_SQL_ENABLE      VARCHAR2(1000);
  P_SQL_DISABLE     VARCHAR2(1000);
  P_TABLE_NAME      VARCHAR2(1000);
  P_CONSTRAINT_NAME VARCHAR2(1000);

  /*
  USER_CONSTRAINTS : 使用者的物件約束資訊
   CONSTRAINT_TYPE:
   P：主鍵；
   R：外來鍵
   C：非空約束/CHECK;
   */
  --外來鍵STATUS非DISABLED的表/約束.
  CURSOR C_CONS_DISABLE IS
    SELECT TABLE_NAME, CONSTRAINT_NAME
      FROM USER_CONSTRAINTS C
     WHERE C.CONSTRAINT_TYPE = 'R'
       AND C.STATUS != 'DISABLED';
  --外來鍵STATUS非ENABLED的表/約束.
  CURSOR C_CONS_ENABLE IS
    SELECT TABLE_NAME, CONSTRAINT_NAME
      FROM USER_CONSTRAINTS C
     WHERE C.CONSTRAINT_TYPE = 'R'
       AND C.STATUS != 'ENABLED';
BEGIN
  ---如果選擇‘禁用’則呼叫以下迴圈. 將所有查出狀態為ENABLED 改為DISABLED狀態
  IF V_TYPE = '禁用' THEN
    FOR P_C_CONS_DISABLE IN C_CONS_DISABLE LOOP
      P_TABLE_NAME      := P_C_CONS_DISABLE.TABLE_NAME;
      P_CONSTRAINT_NAME := P_C_CONS_DISABLE.CONSTRAINT_NAME;
    
      P_SQL_DISABLE := 'ALTER TABLE ' || P_TABLE_NAME ||
                       ' DISABLE CONSTRAINT ' || P_CONSTRAINT_NAME || '';
      --DBMS_OUTPUT.PUT_LINE(P_SQL_DISABLE);
      EXECUTE IMMEDIATE P_SQL_DISABLE;
    END LOOP;
  END IF;
  ---如果選擇‘啟用’則呼叫以下迴圈.將所有查出狀態為DISABLED 改為ENABLED狀態
  IF V_TYPE = '啟用' THEN
    FOR P_C_CONS_ENABLE IN C_CONS_ENABLE LOOP
      P_TABLE_NAME      := P_C_CONS_ENABLE.TABLE_NAME;
      P_CONSTRAINT_NAME := P_C_CONS_ENABLE.CONSTRAINT_NAME;
    
      P_SQL_ENABLE := 'ALTER TABLE ' || P_TABLE_NAME ||
                      ' ENABLE CONSTRAINT ' || P_CONSTRAINT_NAME || '';
      --DBMS_OUTPUT.PUT_LINE(P_SQL_ENABLE);
      EXECUTE IMMEDIATE P_SQL_ENABLE;
    END LOOP;
  END IF;
END P_CONSTRAINT;


*** 本文針對整個使用者進行全部禁用外來鍵，若有需要對錶做過濾，可在本部落格基礎上改寫..

  【源於本人筆記】 若有書寫錯誤，表達錯誤，請指正...
此條目發表在   SQL、SQL優化篇  分類目錄。將固定連線加入收藏夾。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28602568/viewspace-1696583/，如需轉載，請註明出處，否則將追究法律責任。

相關文章