Oracle约束管理脚本

核心提示： 二、约束错误自动查找脚本一般，数据库管理员在对数据进行维护时，Oracle约束管理脚本(3)，如新数据的导入前，首先要关闭所有的外键和触发器，在SQL/PLUS环境下：SQL> SET SERVEROUTPUT ON;SQL> EXEC P_CON_ERR('FK_SB

二、约束错误自动查找脚本

一般，数据库管理员在对数据进行维护时，如新数据的导入前，首先要关闭所有的外键和触发器，数据成功导入后，再打开导入前关闭的外键和触发器。这时经常会遇到错误号为ORA-02298的“未找到父项关键字”的错误。该错误的原因就是数据库表中出现了不能满足外键约束条件的记录。这里，另外给出了一个脚本（P_CON_ERR）用来自动查找造成这类错误的原因，也就是找出不满足外键约束条件的字段值。

该存储过程可单独运行，同时在前面介绍的存储过程P_ALTERCONS中也进行了调用，在存储过程P_ALTERCONS中，可以看到在打开外键时，如果出现错误号为ORA-02298的错误，就调用该存储过程，自动查找造成外键不能启动的原因。

下面是单独运行该存储过程的例子，在SQL/PLUS环境下：

SQL> SET SERVEROUTPUT ON;
SQL> EXEC P_CON_ERR('FK_SB_HJJL_RELATION__SB_PZXH');
外键错误情况
============
NSRNM,PZLXDM,PZXH
-----------------
190321058, 900, 13546
777777775, 108, 17526
777777775, 108, 17528
777777775, 108, 17531
777777775, 108, 17546

PL/SQL过程已成功完成。

其中，FK_SB_HJJL_RELATION__SB_PZXH为出现错误的外键名称。

附存储过程脚本：

CREATE OR REPLACE PROCEDURE P_CON_ERR(as_constraint_name varchar2)
AS
v_CursorID INTEGER;
V_CONSNAME VARCHAR2(30);
V_TABLE_NAME VARCHAR2(30);
V_RTABLE_NAME VARCHAR2(30);
V_COLUMN VARCHAR2(100);
v_Str VARCHAR2(600);
TYPE t_col_value IS TABLE OF VARCHAR2(30)
INDEX BY BINARY_INTEGER;
v_Col_Val t_col_value;
v_RET NUMBER;
v_NUM NUMBER;
i BINARY_INTEGER;
V_WHERE VARCHAR2(600);
V_CAUSE VARCHAR2(200);
CURSOR C_COL_NAME(V_CON_NAME VARCHAR2) IS
SELECT * FROM USER_CONS_COLUMNS
WHERE CONSTRAINT_NAME=V_CON_NAME;
BEGIN
V_CONSNAME := TRIM(UPPER(as_constraint_name)); -- 约束名称
v_num :=0;
FOR T_COL_NAME IN C_COL_NAME(V_CONSNAME) LOOP
IF (V_NUM = 0) THEN
V_COLUMN :=T_COL_NAME.COLUMN_NAME;
V_WHERE :='A.'||T_COL_NAME.COLUMN_NAME||'= B.'||T_COL_NAME.COLUMN_NAME;
ELSE
V_COLUMN :=V_COLUMN||','||T_COL_NAME.COLUMN_NAME;
V_WHERE :=V_WHERE||' AND '||'A.'||T_COL_NAME.COLUMN_NAME||'= B.'||T_COL_NAME.COLUMN_NAME;
END IF;
V_NUM :=V_NUM+1;
END LOOP;
FOR I IN 1..V_NUM LOOP
V_COL_VAL(I) :='';
END LOOP;
SELECT DISTINCT TABLE_NAME INTO V_TABLE_NAME FROM USER_CONS_COLUMNS
WHERE CONSTRAINT_NAME = V_CONSNAME;
-- 找到被引用的表名称
SELECT TABLE_NAME INTO V_RTABLE_NAME FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME IN (SELECT R_CONSTRAINT_NAME FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME = V_CONSNAME);
DBMS_OUTPUT.PUT_LINE('外键错误情况');
DBMS_OUTPUT.PUT_LINE('============');
DBMS_OUTPUT.PUT_LINE(v_column);
DBMS_OUTPUT.PUT_LINE(RPAD('-',LENGTH(V_COLUMN),'-'));
v_CursorID := DBMS_SQL.OPEN_CURSOR;
V_STR := 'SELECT DISTINCT '||V_COLUMN||' FROM '||V_TABLE_NAME||' A WHERE NOT EXISTS ( SELECT NULL FROM '
||V_RTABLE_NAME||' B WHERE '||V_WHERE||')';
DBMS_SQL.PARSE( v_CursorID, v_Str, DBMS_SQL.V7);
FOR I IN 1..V_NUM LOOP
DBMS_SQL.DEFINE_COLUMN(v_CursorID,I,v_COL_VAL(I),30);
END LOOP;
v_ret := DBMS_SQL.EXECUTE(v_CursorID);
WHILE DBMS_SQL.FETCH_ROWS(v_CursorID) > 0 LOOP
V_CAUSE :='';
FOR I IN 1..V_NUM LOOP
DBMS_SQL.COLUMN_VALUE(v_CursorID,I,V_COL_VAL(I));
IF (I = 1) THEN
V_CAUSE :=V_COL_VAL(I);
ELSE
V_CAUSE :=V_CAUSE||', '||V_COL_VAL(I);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_CAUSE);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
END;
/