【Oracle】Oracleシーケンスの採番値を一気にカウントアップする方法

2017-08-08

Oracleシーケンスの現在値を一気に何番もカウントアップする方法のメモ
個人的に、基本INCREMENT BYが「1」の（＝1ずつ採番していく）シーケンスをよく使うのでたまにこういうのが必要になるのである。

「SELECT TEST_SEQ.NEXTVAL FROM DUAL」を呼び出すたびにシーケンス値が1ずつカウントアップされていくため、
「10まで進めたい」と思ったらsqlplusとかでDBに接続した後「SELECT TEST_SEQ.NEXTVAL FROM DUAL」を10回投げればいいだけである。
ただこれが「100,000(10万)まで進めたい」だとそうはいかない。
そんなときに「一気に10万まで数字を進める」やり方である。

一番簡単なのは、
INCREMNT BY値を一時的にすげーでかい値に変える⇒一回だけNEXTVALを呼び出す⇒元に戻す

例えば以下のようなINCREMENT BYが1のシーケンス「TEST_SEQ」があったとする

create sequence TEST_SEQ   
   increment by 1  
   start with 1  
   maxvalue 99999999  
   minvalue 0  
   NOCYCLE  
   ORDER  
   NOCACHE

作った直後はまだ「1」から採番してる状況である。

このシーケンスの現在値を「100,000（10万）番まで一気にカウントあげたい」という場合、

  
alter sequence TEST_SEQ increment by 100000;     ←①  
select TEST_SEQ.nextval from dual;               ←②  
alter sequence TEST_SEQ increment by 1;          ←③

とすれば次のNEXTVALが一気に10万1番になる。

もともと1ずつ採番するシーケンスを①で一時的に10万単位で採番するように変更し、
10万単位採番の状態で１回採番して（②）一気に10万分番号を飛ばして、
また1ずつ採番に戻す（③）

「こんなことするくらいなら最初から10万単位採番でシーケンス作ればいいじゃん」と思うかもしれないが、
取得したシーケンスをテーブルのKEYにしてるようなケースで、
無理矢理テストデータ突っ込んだりとかしてテーブルだけが先に進んでしまい、
シーケンスは置いてけぼりにしてしまった場合等で、
上記のような対処が必要になるときがある。
（というか実際、あったのである）

例えば、Oracleシーケンスの値を主KEYに持つテーブルがあって、
プログラム側ではそのテーブルに毎回「SELECT TEST_SEQ.NEXTVAL FROM DUAL」で返ってきた値を主KEYとして採用しているとする。
そのとき、プログラムとは別の口からOracleシーケンスを使わずに無理矢理データを作ると、
その後プログラムを呼び出したときに、採番されるOracleシーケンス値がテーブルに既存のNoとぶつかって一意制約になってしまう。
以下のようなケースである。↓

作成できるか？★No(主KEY)NAME作成契機このデータ作成直後に
NEXTVALを呼び出すと

○	1	test1	プログラム	2
○	2	test2	プログラム	3
○	3	testX3	テストデータ手作成	3
×	3	test3	プログラム	4

のように、
No.3をOracleシーケンスを使わずに手で無理やりつくったので、
テーブルの最大Noとシーケンスの現在値がずれて、
次にプログラムからレコード作ろうとしたときに一意制約違反になって落ちてしまう。
こういうとき、TEST_SEQを3まで進めて採番値が被らないよう細工する必要があり、
↑に書いたような対処をする。

ただまあこの程度（せいぜい10くらい数字が乖離している程度）なら、
「SELECT TEST_SEQ.NEXTVAL FROM DUAL」を乖離してる数分、投げる方が手っ取り早い。
（このケースだとわずか1しかズレていないから、1回「SELECT TEST_SEQ.NEXTVAL FROM DUAL」投げるだけでいい）
数字のズレ方によって、使う手段を切り分けるべきであろう。

ちなみに愚直（？）にやるやり方として以下のようなのもある

declare  
   nmb_test number(10) := 0;  
begin  
  
   for i in 1..100000 loop  
       SELECT TEST_SEQ.NEXTVAL INTO nmb_test FROM DUAL;  
   end loop;  
  
DBMS_OUTPUT.PUT_LINE(‘SEQUENCE_NO=’||trim(to_char(nmb_test)));

end;

/

要するに10万回「SELECT TEST_SEQ.NEXTVAL FROM DUAL」を投げるだけのもんであるが、
まじで10万回ループするのでカウントアップしたい数字が遠いほど時間がかかるし、
そこまでおすすめできるものではない。。