Oracle11gr2新增提示CHANGE_DUPKEY_ERROR_INDEX

在11.2中，Oracle新增了一个HINT，用于改变键值重复时的报错信息。

 

 

正常情况下，如果主键冲突，则返回错误号为ORA-00001，而在11.2中，Oracle提供了改变错误号的方法，使用这个CHANGE_DUPKEY_ERROR_INDEX提示，指定的索引违反唯一约束后，返回的错误为ORA-38911。

SQL> CREATE TABLE T       
  2  (ID NUMBER PRIMARY KEY,
  3  NAME VARCHAR2(30));

表已创建。

SQL> INSERT INTO T VALUES (1, 'TEST');

已创建 1 行。

SQL> INSERT INTO T VALUES (1, 'ABC');
INSERT INTO T VALUES (1, 'ABC')
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (TEST.SYS_C0011145)

SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T, SYS_C0011145) */ INTO T
  2  VALUES (1, 'ABC');
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T, SYS_C0011145) */ INTO T
*
第 1 行出现错误:
ORA-38911: 违反唯一约束条件 (TEST.SYS_C0011145)

SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T(ID)) */ INTO T
  2  VALUES (1, 'ABC');
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T(ID)) */ INTO T
*
第 1 行出现错误:
ORA-38911: 违反唯一约束条件 (TEST.SYS_C0011145)

提示CHANGE_DUPKEY_ERROR_INDEX有两种用法，一种是指定索引的名称，另一种是指明所有构成索引的列。

需要注意的是，这个HINT只对唯一索引生效，而对唯一约束无效：

SQL> ALTER TABLE T DROP PRIMARY KEY;

表已更改。

SQL> CREATE INDEX IND_T_NON_UNI ON T(ID);

索引已创建。

SQL> ALTER TABLE T ADD PRIMARY KEY (ID);

表已更改。

SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T(ID)) */ INTO T
  2  VALUES (1, 'ABC');
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T(ID)) */ INTO T
                                                    *
第 1 行出现错误:
ORA-38913: 索引提示中指定的索引无效

SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T, IND_T_NON_UNI) */ INTO T
  2  VALUES (1, 'ABC');
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T, IND_T_NON_UNI) */ INTO T
                                                               *
第 1 行出现错误:
ORA-38913: 索引提示中指定的索引无效

SQL> SELECT CONSTRAINT_NAME
  2  FROM USER_CONSTRAINTS
  3  WHERE TABLE_NAME = 'T'
  4  AND CONSTRAINT_TYPE = 'P';

CONSTRAINT_NAME
------------------------------
SYS_C0011146

SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T, SYS_C0011146) */ INTO T
  2  VALUES (1, 'ABC');
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T, SYS_C0011146) */ INTO T
                                                              *
第 1 行出现错误:
ORA-38913: 索引提示中指定的索引无效

如果ORA-00001错误是唯一约束引发的，而不是唯一索引导致的，则执行SQL报错ORA-38913。需要注意的是，这里的错误和使用HINT不一样，使用HINT是执行的时候将碰到的ORA-00001错误转化为ORA-38911错误，而这里的错误实际上是语法错误，SQL根本就没有执行。

SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T, SYS_C0011146) */ INTO T
  2  VALUES (2, 'ABC');
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T, SYS_C0011146) */ INTO T
                                                              *
第 1 行出现错误:
ORA-38913: 索引提示中指定的索引无效

SQL> INSERT INTO T
  2  VALUES (2, 'ABC');

已创建 1 行。

如果没有HINT，则这个语句可以成功，但是由于指定了不唯一或不存在的索引，导致SQL语句直接报错。

也行有人会置疑这个HINT的作用，事实上这个HINT和前不久介绍的另一个HINT：IGNORE_ROW_ON_DUPKEY_INDEX都是Oracle为了在线版本升级而设计的。因此这里就不详细描述HINT的作用了，在描述版本升级的时候会描述如何利用这个HINT的。

这里要说明的不仅仅是这个HINT的用法，还有Oracle的可控性。连Oracle的返回错误号都可以通过HINT来修改，Oracle的灵活性可见一斑。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-625286/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-625286/