PL-SQL游标函数

Question

如何编写一个光标来填充序列中缺失的主键值？

例如，ids:1,3,4,5,7存在，函数将插入2和6。

我们知道max(id(，并且有一个函数可以找到主键的下一个缺失值。

Answer 1

如果这是表内容：

SQL> select * from test order by id;
ID
----------
1
3
4
5
7

然后，使用行生成器技术之一创建整个集合(第6-8行(，并应用MINUS集合运算符来查找缺失的值：

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>

现在插入它们很简单：

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>

Answer 2

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; 

如果你想用一个程序像这样做：

exec inser_id(table_name,column_name);

那么代码是：

create or replace procedure inser_id (p_table_name varchar2, p_column_name varchar2) as
v_max number;
v_count number;
v_table_name varchar2(32):=p_table_name;
v_column_name varchar2(32):= p_column_name;
begin
EXECUTE IMMEDIATE 'select max('||v_column_name||') from '||v_table_name
into v_max;
for i in 1..v_max loop
EXECUTE IMMEDIATE 'select count(*) from ' ||v_table_name||' where id='||i
into v_count;
if v_count=0 then 
EXECUTE IMMEDIATE 'insert into ' ||v_table_name||' ('||v_column_name||') values ('||i||')';
end if; 
end loop;
end;