我在Oracle中有一个表有几个约束.当我插入新记录并且并非所有约束都有效时,Oracle仅引发"第一个"错误.如何获取所有违反记录的行为?
CREATE TABLE A_TABLE_TEST ( COL_1 NUMBER NOT NULL, COL_2 NUMBER NOT NULL, COL_3 NUMBER NOT NULL, COL_4 NUMBER NOT NULL ); INSERT INTO A_TABLE_TEST values (1,null,null,2); ORA-01400: cannot insert NULL into ("USER_4_8483C"."A_TABLE_TEST"."COL_2")
我想得到这样的东西:
Column COL_2: cannot insert NULL Column COL_3: cannot insert NULL This would be also sufficient: Column COL_2: not valid Column COL_3: not valid
当然我可以编写一个触发器并单独检查每一列,但我更喜欢约束而不是触发器,它们更容易维护,不需要手动编写代码.
任何的想法?
没有直接的方法来报告所有可能的约束违规.因为当Oracle在第一次违反约束时偶然发现时,无法进一步评估,语句将失败,除非该约束被延迟,或者该log errors
子句已包含在DML语句中.但应该注意的是,log errors
子句将无法捕获所有可能的约束违规,只记录第一个.
作为可能的方法之一是:
创建exceptions
表.它可以通过执行ora_home/rdbms/admin/utlexpt.sql
脚本来完成.桌子的结构非常简单;
禁用所有表约束;
执行DML;
使用exceptions into <<exception table name>>
子句启用所有约束.如果您执行了utlexpt.sql
脚本,那么将存储表异常的名称exceptions
.
测试表:
create table t1( col1 number not null, col2 number not null, col3 number not null, col4 number not null );
尝试执行一个insert
声明:
insert into t1(col1, col2, col3, col4) values(1, null, 2, null); Error report - SQL Error: ORA-01400: cannot insert NULL into ("HR"."T1"."COL2")
禁用所有表的约束:
alter table T1 disable constraint SYS_C009951; alter table T1 disable constraint SYS_C009950; alter table T1 disable constraint SYS_C009953; alter table T1 disable constraint SYS_C009952;
尝试再次执行以前失败的insert
语句:
insert into t1(col1, col2, col3, col4) values(1, null, 2, null); 1 rows inserted. commit;
现在,启用表的约束并在exceptions
表中存储异常(如果有):
alter table T1 enable constraint SYS_C009951 exceptions into exceptions; alter table T1 enable constraint SYS_C009950 exceptions into exceptions; alter table T1 enable constraint SYS_C009953 exceptions into exceptions; alter table T1 enable constraint SYS_C009952 exceptions into exceptions;
检查exceptions
表格:
column row_id format a30; column owner format a7; column table_name format a10; column constraint format a12; select * from exceptions ROW_ID OWNER TABLE_NAME CONSTRAINT ------------------------------ ------- ------- ------------ AAAWmUAAJAAAF6WAAA HR T1 SYS_C009951 AAAWmUAAJAAAF6WAAA HR T1 SYS_C009953
违反了两个限制因素.要查找列名,只需user_cons_columns
参考数据字典视图:
column table_name format a10; column column_name format a7; column row_id format a20; select e.table_name , t.COLUMN_NAME , e.ROW_ID from user_cons_columns t join exceptions e on (e.constraint = t.constraint_name) TABLE_NAME COLUMN_NAME ROW_ID ---------- ---------- -------------------- T1 COL2 AAAWmUAAJAAAF6WAAA T1 COL4 AAAWmUAAJAAAF6WAAA
上面的查询为我们提供了列名和有问题记录的rowid.手头有rowid,找到导致约束违规的记录,修复它们,再次重新启用约束应该没有问题.
以下是用于生成alter table
用于启用和禁用约束的语句的脚本:
column cons_disable format a50 column cons_enable format a72 select 'alter table ' || t.table_name || ' disable constraint '|| t.constraint_name || ';' as cons_disable , 'alter table ' || t.table_name || ' enable constraint '|| t.constraint_name || ' exceptions into exceptions;' as cons_enable from user_constraints t where t.table_name = 'T1' order by t.constraint_type