with句の再帰問合せを利用した抜け番検索
Oracle WITH句で再帰問合せができると聞いたので
抜け番検索を作ってみました。
http://www.oracle.com/technetwork/jp/articles/otnj-sql-image7-1525406-ja.html#c
まず抜け番のあるテーブルレコードを作成。
create table test(num number); insert into test values(1); insert into test values(3); insert into test values(5); insert into test values(7); insert into test values(9); insert into test values(10); commit;
抜け番問合せ
with num_list(cnt) as ( select 1 from dual union all select 1 + cnt from num_list where cnt < 10 ) select n.cnt from n_list n where not exitst( select 1 from test t where t.num = n.cnt) ) order by n.cnt;
with句のエイリアスnum_listをwith句内で呼び出しています。
再帰問合せはUNION ALLのみ対応してるみたいですね。
union allの下のselectでwhere句にて最大件数を指定しないとサイクルしていると怒られます。
これに 12c で追加された fetch句で
fetch first 1 rows only
とかを記載すれば、空き番の一番若い番号を取得できそうですね。
蛇足ですが
抜け番を使用したinsertを行う場合は整合性を保つためにはテーブル毎ロックが必要です。
数年ぶりのブログなのでちょっと緊張