解决方案 »
- oracle里用数字证书?安全登录?
- windows xp 安装oracle9i 出现“OracleOraHomePagingServer已经存在”错误?
- 索引和数据库
- 安装Oracle817时候出现的问题
- 请教个关于merge into问题,请用过的人帮忙下,谢谢~~~
- 在window2000装的oracle9i服务器,Start HTTP Server powered by Apache失败?
- 一个update语句,请大家看看
- hello,hello,大家好,大家真的很好哈。请允许我问个傻问题
- 怎樣啟動OMS
- 大神请进
- 请教如何合并两张表相同数据,不同数据分别展示
- ORA-01655和ORA-06512错误,求指点
select t1.rn
from (select level rn from dual connect by level < 1501) t1
where not exists (select 1 from table t2 where t1.rn = t2.id);
declare
i number;
nums number;
begin
for i in 1..1500 loop
select count(*) into nums from address_list where id = i;
if nums = 0 then
dbms_output.put_line(i);
end if;
end loop;
end;
select a.rn from (select rownum rn from dual connect by rownum < =1500) a
where not exists(select 1 from 表名 b where a.rn=b.id) order by a.rn
2 I INTEGER;
3 BEGIN
4 FOR I IN 1 .. 1500
5 LOOP
6 INSERT INTO T_NO VALUES (I);
7 END LOOP;
8 COMMIT;
9 END;
10 /PL/SQL procedure successfully completed.scott@PROD>DELETE FROM t_no WHERE cno IN (6, 100, 101, 1000, 1001, 1200, 1400, 1403, 1480, 1481);10 rows deleted.scott@PROD>commit;Commit complete.scott@PROD>SELECT CNO, NEXT_CNO
2 FROM (SELECT CNO, LEAD(CNO, 1) OVER(ORDER BY CNO) AS NEXT_CNO FROM T_NO) T
3 WHERE T.NEXT_CNO - T.CNO > 1; CNO NEXT_CNO
---------- ----------
5 7
99 102
999 1002
1199 1201
1399 1401
1402 1404
1479 1482 7 rows selected.
查询出来的行次,NEXT_CNO - CNO 就是缺的。
select rownum from dual connect by rownum<=1500
minus
select id from T
minus 比较好,清晰,简单。
select t1.rn
from (select level rn from dual connect by level < 1501) t1
where not exists (select 1 from table t2 where t1.rn = t2.id);
这个也不错