【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万まで数字を進める」やり方である。

手順：INCREMENT BY を一時的に変える

一番簡単なのは、

INCREMENT 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万回ループするのでカウントアップしたい数字が遠いほど時間がかかるし、そこまでおすすめできるものではない。。