使用序列的问题ORA-02287(r5笔记第19天)

今天一个开发的同事问我一个问题，说在执行一条sql语句的时候报了ORA错误，脑海中删除了各种权限的问题之后，他提供给我的错误还是在我预料之外。 ERROR at line 1: ORA-02287: sequence number not allowed here

这个问题看错误信息是很明显和sequence有关的。但是为什么会报出这个错误呢，在几封邮件交流之后，问题就明朗起来， 语句是类似下面这样的结构， insert into customer(xxxxx,xxxxx,xxx...............) select disticnt xxxxx,seq_value.nextval,xxxx,xxxx,xxx... from new_table group by xxx,xxx,xxx,xxx; 真实的脚本洋洋洒洒一大页，各种子查询，表关联，函数处理，看起来还是需要些耐心的。简缩之后就是上面的结构， 这个Insert采用了子查询来插入数据，根据和开发的沟通，是需要提供一个补丁，做修复数据所用，所以会有大量的数据过滤操作。 插入数据的时候使用seq_value.nextval也还是很常见的，怎么会报出错误了呢，按照这个语句的结构发现还是最开头的distinct和group操作导致的，这种数据的统计分析操作让本来就不确定的sequence值更加不确定，所以斟酌再三还是建议他们先创建一个临时表，然后把数据不做distinct,group by 操作，直接插入到这个临时表中，最后在insert的时候再从临时表中distinct和group by 即可。 我们来简答模拟一下这个问题。 首先为了不影响原有的sequence，我们可以创建一个类似的sequence,然后在脚本中替换即可，这样也不会对原有环境的sequence值造成影响。 CREATE SEQUENCE "NEW_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1012852 CACHE 20 NOORDER NOCYCLE 然后我们创建一个表 create table new_test( id1 number,id2 number,name varchar2(30)); 然后尝试distinct和group by 操作，发现都不可以。 n1@TEST11G> select new_seq.nextval,id1,id2,name from new_test group by id1,id2,name; select new_seq.nextval,id1,id2,name from new_test group by id1,id2,name * ERROR at line 1: ORA-02287: sequence number not allowed here n1@TEST11G> select distinct new_seq.nextval,id1,id2,name from new_test; select distinct new_seq.nextval,id1,id2,name from new_test * ERROR at line 1: ORA-02287: sequence number not allowed here 其实这个问题的原因还是很容易理解的，这种sequence值的动态不确定性，很容易出问题。其实不光使用distinct，group by 会有问题，很多相关的操作都是如此。 比如union,union all select new_seq.nextval,id1,id2,name from new_test union all select new_seq.nextval,id1,id2,name from new_test minus操作。 select new_seq.nextval,id1,id2,name from new_test minus select new_seq.nextval,id1,id2,name from new_test 使用In子查询 select new_seq.nextval id1,id2,name from new_test where id1 in (select new_seq.nextval from new_test ) order by操作 select new_seq.nextval,id1,id2,name from new_test order by id2; 换个角度来看，对于这类问题，也可以使用临时表来处理，也可以使用pl/sql来处理，都能达到比较目的，另外一个角度来说，对于sequence的使用，大家一般都认为是取之不尽，用之不竭，感觉大量使用时应该的，在很多时候还是需要好好斟酌一下，有些补丁或者临时的处理是否一定需要使用到 序列，序列资源也是很宝贵的资源，如果在测试脚本中做了大量的自增处理也是很大的浪费。