2017年08月08日

【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;               ←A
alter sequence TEST_SEQ increment by 1;          ←B

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

もともと1ずつ採番するシーケンスを@で一時的に10万単位で採番するように変更し、
10万単位採番の状態で1回採番して(A)一気に10万分番号を飛ばして、
また1ずつ採番に戻す(B)




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

例えば、Oracleシーケンスの値を主KEYに持つテーブルがあって、
プログラム側ではそのテーブルに毎回「SELECT TEST_SEQ.NEXTVAL FROM DUAL」で返ってきた値を主KEYとして採用しているとする。
そのとき、プログラムとは別の口からOracleシーケンスを使わずに無理矢理データを作ると、
その後プログラムを呼び出したときに、採番されるOracleシーケンス値がテーブルに既存のNoとぶつかって一意制約になってしまう。
以下のようなケースである。↓
作成できるか?★No(主KEY)NAME作成契機このデータ作成直後に
NEXTVALを呼び出すと
1test1プログラム2
2test2プログラム3
3testX3テストデータ手作成3
×3test3プログラム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万回ループするのでカウントアップしたい数字が遠いほど時間がかかるし、
そこまでおすすめできるものではない。。
posted by rm_blank_slash at 09:00 | 東京 ☀ | Comment(0) | TrackBack(0) | SEメモ(ORACLE関連) | このブログの読者になる | 更新情報をチェックする
この記事へのコメント
コメントを書く
お名前:

メールアドレス:

ホームページアドレス:

コメント:


この記事へのトラックバック