使用序列的錯誤ORA-02287

今天一個開發的同僚問我一個問題，說在執行一條sql語句的時候報了ORA錯誤，腦海中删除了各種權限的問題之後，他提供給我的錯誤還是在我預料之外。

ERROR at line 1:

ORA-02287: sequence number not allowed here

這個問題看錯誤資訊是很明顯和sequence有關的。但是為什麼會報出這個錯誤呢，在幾封郵件交流之後，問題就明朗起來，

語句是類似下面這樣的結構，

insert into customer(xxxxx,xxxxx,xxx...............)

select distinct 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的時候對剛建立的臨時表和序列結合起來，一次插入。

僞代碼類似下面的形式，

create table temp_tab as select distinct xxxxxx   from xxxx  group by xxxxxx;

insert into customer(xxxxx,xxxx) select xxxx,seq_vvalue.nextval from temp_tab;

我們來簡答模拟一下這個問題。

首先為了不影響原有的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

               *

n1@TEST11G> select distinct new_seq.nextval,id1,id2,name from new_test;

select distinct new_seq.nextval,id1,id2,name from new_test

                        *

其實這個問題的原因還是很容易了解的，這種sequence值的動态不确定性，很容易出問題。其實不光使用distinct，group by 會有問題，很多相關的操作都是如此。

比如union,union all

select new_seq.nextval,id1,id2,name from new_test 

union all

minus操作。

minus

使用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的使用，大家一般都認為是取之不盡，用之不竭，感覺大量使用時應該的，在很多時候還是需要好好斟酌一下，有些更新檔或者臨時的處理是否一定需要使用到

序列，序列資源也是很寶貴的資源，如果在測試腳本中做了大量的自增處理也是很大的浪費。