欠番を探して insert する PL/SQL

表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

ablog

不器用で落着きのない技術者のメモ

欠番を探して insert する PL/SQL