您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫相關知識

Oracle 約束與觸發器批量啟用/停用存儲過程：資料遷移合併的利器

編輯：Oracle 資料庫相關知識

業務場景：跨庫合併與代碼表整理

銀行從市級集中升級為省級/國家級資料集中，DBA 核心任務是將分散資料庫合併、統一代碼表。合併前必須**停用所有外鍵與觸發器**避免約束衝突，資料整理完成後再**批量啟用**。本文提供生產級存儲過程 P_ALTERCONS 實現一鍵操作。

存儲過程規格

SQL> SET SERVEROUTPUT ON;
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SQL> EXEC P_ALTERCONS('ENABLE');  -- 或 'DISABLE'

參數僅接受 ENABLE / DISABLE，大小寫不敏感
處理當前 Schema 下所有 CONSTRAINT_TYPE='R'（外鍵）與所有觸發器
不處理 PK/UK，建議勿隨意停用主鍵/唯一約束
輸出詳細日誌：開始/結束時間、處理筆數、錯誤明細

核心邏輯解析

CREATE OR REPLACE PROCEDURE P_ALTERCONS(AS_ALTER VARCHAR2) AS
    v_CursorID INTEGER;
    v_StrCon   VARCHAR2(300);
    v_FkNum    NUMBER := 0;
    v_TriNum   NUMBER := 0;
    CURSOR C_CON IS SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='R';
    CURSOR C_TRIGGER IS SELECT TRIGGER_NAME, STATUS FROM USER_TRIGGERS;
BEGIN
    DBMS_OUTPUT.PUT_LINE('BEGIN TIME: '||TO_CHAR(SYSDATE));
    
    IF UPPER(AS_ALTER) NOT IN ('ENABLE','DISABLE') THEN
        DBMS_OUTPUT.PUT_LINE('ERROR: 參數必須為 ENABLE 或 DISABLE!');
        RETURN;
    END IF;

    -- 處理外鍵
    v_CursorID := DBMS_SQL.OPEN_CURSOR;
    FOR R_CON IN C_CON LOOP
        IF (UPPER(AS_ALTER)='DISABLE' AND R_CON.STATUS='ENABLED') OR
           (UPPER(AS_ALTER)='ENABLE'  AND R_CON.STATUS='DISABLED') THEN
            v_StrCon := 'ALTER TABLE '||R_CON.OWNER||'.'||R_CON.TABLE_NAME||
                        ' '||UPPER(AS_ALTER)||' CONSTRAINT '||R_CON.CONSTRAINT_NAME;
            BEGIN
                DBMS_SQL.PARSE(v_CursorID, v_StrCon, DBMS_SQL.NATIVE);
                v_FkNum := v_FkNum + 1;
            EXCEPTION
                WHEN OTHERS THEN
                    IF SQLCODE = -2298 THEN  -- 父鍵不存在等
                        P_CON_ERR(R_CON.CONSTRAINT_NAME);  -- 自訂錯誤處理
                    END IF;
                    DBMS_OUTPUT.PUT_LINE('ERROR: '||SQLERRM);
            END;
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('====== Foreign Keys were '||AS_ALTER||', total '||v_FkNum||' ======');

    -- 處理觸發器
    FOR T_TRIGGER IN C_TRIGGER LOOP
        IF (UPPER(AS_ALTER)='DISABLE' AND T_TRIGGER.STATUS='ENABLED') OR
           (UPPER(AS_ALTER)='ENABLE'  AND T_TRIGGER.STATUS='DISABLED') THEN
            v_StrCon := 'ALTER TRIGGER '||T_TRIGGER.TRIGGER_NAME||' '||UPPER(AS_ALTER);
            BEGIN
                DBMS_SQL.PARSE(v_CursorID, v_StrCon, DBMS_SQL.NATIVE);
                v_TriNum := v_TriNum + 1;
            EXCEPTION
                WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE('ERROR: '||SQLCODE||' '||SQLERRM);
            END;
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('====== Triggers were '||AS_ALTER||', total '||v_TriNum||' ======');
    
    DBMS_SQL.CLOSE_CURSOR(v_CursorID);
    DBMS_OUTPUT.PUT_LINE('END TIME: '||TO_CHAR(SYSDATE));
END;
/