Oracle约束管理脚本

核心提示： 附存储过程脚本：CREATE OR REPLACE PROCEDURE P_ALTERCONS(AS_ALTER VARCHAR2)ASv_CursorID INTEGER;v_StrCon VARCHAR2(300);v_StrTri VARCHAR2(300);v_FkNum NUMB

附存储过程脚本：

CREATE OR REPLACE PROCEDURE P_ALTERCONS(
AS_ALTER VARCHAR2)
AS
v_CursorID INTEGER;
v_StrCon VARCHAR2(300);
v_StrTri VARCHAR2(300);
v_FkNum NUMBER :=0;
v_TriNum NUMBER :=0;
v_sqlcode NUMBER;
v_sqlerrm VARCHAR2(600);
CURSOR C_CON IS
SELECT * FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE='R';
R_CON C_CON%ROWTYPE;
CURSOR c_trigger IS
SELECT TRIGGER_NAME,STATUS FROM user_triggers;
notfound BOOLEAN;
BEGIN
DBMS_OUTPUT.PUT_LINE('BEGIN TIME: '||to_char(sysdate));

判断输入参数是否为DISABLE或者是ENABLE，如果是的话，就继续处理，否则退出过程，给出提示

IF (UPPER(AS_ALTER) = 'DISABLE' OR UPPER(AS_ALTER) = 'ENABLE') THEN

OPEN C_CON;

当前用户下外键的处理 ENABLE或者 DISABLE

v_CursorID := DBMS_SQL.OPEN_CURSOR;
LOOP
FETCH C_CON into R_CON;
notfound:=C_CON%NOTFOUND;
EXIT WHEN notfound;
begin
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;
DBMS_SQL.PARSE( v_CursorID, v_StrCon, DBMS_SQL.V7);
v_FkNum :=v_FkNum+1;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
v_sqlerrm := SUBSTR(SQLERRM,1,600);
DBMS_OUTPUT.PUT_LINE('ERROR: '||' '||V_SQLERRM);
-- 找出错误原因
IF (v_sqlcode = -2298) THEN
p_con_err(R_CON.CONSTRAINT_NAME);
END IF;
END;
END LOOP;
CLOSE C_CON;
DBMS_OUTPUT.PUT_LINE('====== Foreign Keys were '||as_alter||', total '||to_char(v_FkNum)||' =====');
-- 当前用户下触发器的处理 ENABLE或者 DISABLE
FOR T_TRIGGER IN C_TRIGGER LOOP
BEGIN
IF (UPPER(AS_ALTER)='DISABLE' AND T_TRIGGER.STATUS='ENABLED' OR
UPPER(AS_ALTER)='ENABLE' AND T_TRIGGER.STATUS='DISABLED') THEN
v_StrTri := 'ALTER TRIGGER '||T_TRIGGER.TRIGGER_name ||' '||UPPER(as_alter);
DBMS_SQL.PARSE( v_CursorID, v_StrTri, DBMS_SQL.V7);
v_TriNum :=v_TriNum+1;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
v_sqlerrm := SUBSTR(SQLERRM,1,600);
DBMS_OUTPUT.PUT_LINE('ERROR: '||V_SQLCODE||' '||V_SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('====== Triggers were '||as_alter||', total '||to_char(v_TriNum)||' =====');
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
ELSE -- 输入参数不正确
DBMS_OUTPUT.PUT_LINE('ERROR:输入参数不正确，参数为ENABLE或者DISABLE!');
END IF;
DBMS_OUTPUT.PUT_LINE('END TIME: '||to_char(sysdate));
END;
/