表usersには列id1(number型)と列id2(number型)がある。
両方とも欠番があり、列id1と列id2の欠番を探し、欠番があったら insert する。
ということをやってみた。
- テーブルを作って欠番ができるよう insert する。
SQL> create table users(id1 number(4), id2 number(4)); SQL> insert into users (id1, id2) values(2, 2); SQL> insert into users (id1, id2) values(3, 10); SQL> insert into users (id1, id2) values(5, 3); SQL> insert into users (id1, id2) values(10, 1); SQL> commit; SQL> select * from users; ID1 ID2 ---------- ---------- 2 2 3 10 5 3 10 1
- 欠番を探す SQL を実行する。
SQL> with x as (select level id from dual connect by level <= 20) select a.id id1, b.id id2 from (select rownum num, x.id from x where not exists (select * from users where id1 = x.id) and rownum <= 20) a, (select rownum num, x.id from x where not exists (select * from users where id2 = x.id) and rownum <= 20) b where a.num = b.num; ID1 ID2 ---------- ---------- 1 4 4 5 6 6 7 7 8 8 9 9 11 11 12 12 13 13 14 14 15 15 16 16 17 17 18 18 19 19 20 20 16 rows selected.
- 欠番に insert する PL/SQL を実行する。
declare cursor cu is with x as (select level id from dual connect by level <= 20) select a.id id1, b.id id2 from (select rownum num, x.id from x where not exists (select * from users where id1 = x.id) and rownum <= 20) a, (select rownum num, x.id from x where not exists (select * from users where id2 = x.id) and rownum <= 20) b where a.num = b.num; begin for rec in cu loop insert into users (id1, id2) values(rec.id1, rec.id2); commit; end loop; end; /
- 結果を確認する。
SQL> select * from users order by id1, id2; ID1 ID2 ---------- ---------- 1 4 2 2 3 10 4 5 5 3 6 6 7 7 8 8 9 9 10 1 11 11 12 12 13 13 14 14 15 15 16 16 17 17 18 18 19 19 20 20 20 rows selected.
[実行環境]
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production PL/SQL Release 9.2.0.7.0 - Production CORE 9.2.0.7.0 Production TNS for Solaris: Version 9.2.0.7.0 - Production NLSRTL Version 9.2.0.7.0 - Production