How to write a cursor that fills out missing primary key values in a sequence?

For example, ids: 1,3,4,5,7 are present and the function will insert 2 and 6.

We know the max(id) and have a function that can find the next missing value of primary keys.

CodePudding user response：

If this is table contents:

SQL> select * from test order by id;

        ID
----------
         1
         3
         4
         5
         7

then - using one of row generator techniques - create the whole set (lines #6 - 8) and apply the MINUS set operator to find missing values:

SQL> with
  2  minimax as
  3    (select min(id) minid, max(id) maxid
  4     from test
  5    )
  6  select minid   level - 1 id
  7  from minimax
  8  connect by level <= maxid - minid   1
  9  minus
 10  select id from test;

        ID
----------
         2
         6

SQL>

Now it is simple to insert them:

SQL> insert into test (id)
  2  with
  3  minimax as
  4    (select min(id) minid, max(id) maxid
  5     from test
  6    )
  7  select minid   level - 1 id
  8  from minimax
  9  connect by level <= maxid - minid   1
 10  minus
 11  select id from test;

2 rows created.

SQL> select * from test order by id;

        ID
----------
         1
         2
         3
         4
         5
         6
         7

7 rows selected.

SQL>

CodePudding user response：

declare
    v_max number;
    v_count number;
begin
select max(id) into v_max from test_table;
for i in 1..v_max loop
    select count(*) into v_count from test_table where id=i;
    if v_count>0 then 
        null;
    else 
        insert into test_table (id) values (i);
    end if;
end loop;
end;