| 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; / |
关注此文的读者还看过：