请问OracleSQL高手，如何插空

Question

can not do this to use a sql statement.
use function:...
v_i integer;
v_key a.key%type;
cursor c is select key from a order by key desc;
...
begin
  open c;
  fetch c into v_key;
  v_i:=v_key;
  fetch c into v_key;
  while c%found loop    
     if (v_i>v_key+1)
        result:=v_i-1;
     else
        v_i:=v_key;
     end if;
     fetch c into v_key;
  end loop;
  return result;
  ...

Answer 1

select to_number(key) - 1 from 
(
select key  from yourtable a where not exists ( select 1 from yourtable b where to_number(a.key) = to_number(b.key) - 1)
order by key desc 
) c
where rownum < 2;

Answer 2

有另外一个朋友提供了一个方法：
TABLE1:
KEY1:
1
2
...
9999
TABLE1中包含了全部1－9999的KEY
TABLE2:
KEY2:
3
8
100
TABLE2中是实际数据库里存放的KEY那么SQL为select max(key1)
from table1
where not exist(select key2 from TABLE2)这个方法怎么样？效率与上两种方法相比那个更快一些呢？

Answer 3

create or replace function get_id
return varchar2
as
v_mul varchar2(10);
cursor t_sor is
select lead(id,1,1) over(order by id desc) next_id,lead(id,1,1) over(order by id desc)-id mul_id from table_name;
begin
for v_sor in t_sor loop
if v_sor.mul_id=0 then
return 0;
exit;
elsif v_sor.mul_id>1 then
v_mul:=v_sor.mul_id;
end if;
end loop;
return v_mul;
end;
/create or replace trigger table_tri
before insert on table_name
for each row
begin
:new.id:=get_id;
end;
/

Answer 4

sorry,修改以下：
create or replace function get_id
return varchar2
as
v_mul varchar2(10);
cursor t_sor is
select lead(id,1,1) over(order by id desc) next_id,lead(id,1,1) over(order by id desc)-id mul_id from table_name;
begin
for v_sor in t_sor loop
if v_sor.mul_id=0 then
return 0;
exit;
elsif v_sor.mul_id>1 then
v_mul:=v_sor.mul_id-1;  --此处就是返回的最近值
end if;
end loop;
return v_mul;
end;
/

Answer 5

这样应该是效率最高的了。select max(key)-1  from yourtable a where not exists ( select 1 from yourtable b where to_number(a.key) = to_number(b.key) - 1)

Answer 6

楼上的select max(key)-1  from yourtable a where not exists ( select 1 from ...) 
                                                              ~
这个“1”是什么意思？

Answer 7

同意 jiezhi(西域浪子),怎么可能用一个sql写出来呢...写函数比较现实.

Answer 8

select 1 并没有实际意义，只是为了引入where to_number(a.key) = to_number(b.key) - 1这个条件而已。

Answer 9

找一个有10000条数据的表12:26:19 SQL> select * from tcn;AAA              BBB
---------- ---------
a                  1
a                  3
a                  5
a                  6实际：70
12:26:29 SQL> select count(*) from col; COUNT(*)
---------
      120实际：61
12:26:42 SQL> select max(id)from (
12:26:48   2  select tcn.*,t.id from tcn,(select rownum id from col) t
12:26:48   3  where t.id=tcn.bbb(+) and t.id<=(select max(bbb) from tcn)
12:26:48   4  ) tb where tb.aaa is null ;  MAX(ID)
---------
        4实际：60
12:26:48 SQL> 
12:26:48 SQL> insert into tcn 
12:28:16   2  select 'a',max(id)from (
12:28:33   3  select tcn.*,t.id from tcn,(select rownum id from col) t
12:28:35   4  where t.id=tcn.bbb(+) and t.id<=(select max(bbb) from tcn)
12:28:35   5  ) tb where tb.aaa is null ;已创建 1 行。实际：701
12:28:37 SQL> select * from tcn;AAA              BBB
---------- ---------
a                  1
a                  3
a                  5
a                  6
a                  4实际：70
12:28:42 SQL>

Answer 10

create table test1(c1 char(4));
insert into test1 values('0001');
insert into test1 values('0009');
insert into test1 values('0123');
insert into test1 values('0321');
insert into test1 values('9023');
insert into test1 values('9998');
insert into test1 values('9999');
select max(newc1) 
    from (select c1,lpad(10000-rownum,4,'0') newc1 
              from (select c1 
                        from test1 order by c1 desc))
    where c1<>newc1;