rm /blog

IT系技術職のおっさんがIT技術とかライブとか日常とか雑多に語るブログです。* 本ブログに書かれている内容は個人の意見・感想であり、特定の組織に属するものではありません。/All opinions are my own.*

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

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万単位採番の状態で1回採番して(②)一気に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万回ループするのでカウントアップしたい数字が遠いほど時間がかかるし、
そこまでおすすめできるものではない。。