使用PL/SQL table 来构造连续数集合表
作者: oldwain(http://oldwain.itpub.net)发表于: 2005.05.25 16:57
分类: Oracle
出处: http://oldwain.itpub.net/post/6/30895
---------------------------------------------------------------
很多时候，我们需要在SQL查询中构造一张由连续数组成的集合，比如找出某一列中缺少某些数字，或者在不是每个日期都有记录的情况下，列出所有日期的记录情况。
典型应用的例子可参考：
http://www.itpub.net/showthread.php?s=&threadid=4724
http://www.itpub.net/showthread.php?threadid=104060&pagenumber=
http://www.itpub.net/showthread.php?s=&threadid=13618
在上面的这些例子中，为了构造连续数/日期表，几乎都使用了select * from all_objects(或其它类似的大表),但是这种做法有两个明显的弊端：
1. all_objects（或类似表)的数目未知，并且不确定。在需要大的构造表时，可能数量不够。（当然，数量的问题可以通过all_objects自身连接来避免。但自身连接会带来很大的性能问题，这使得何时使用表本身，何时使用两表/更多的表连接成为一件难以确定的事）
2. 使用all_objects，带来了更多的逻辑IO, 影响了系统性能。
下面我们采用Pl/sql table来构造这样的集合。
create or replace type serialListType Is table of Number;
create or replace function serialList( maxval Number ) return serialListType
as
v_data serialListType := serialListType();
Begin
For i In 1..maxval loop
v_data.extend;
v_data( i ) := i;
end loop;
return v_data;
end;
使用这个集合“表”的方法：
-- 构造5以内的连续数的表
scott@O9I.US.ORACLE.COM> select * from table(seriallist(5)) ;
COLUMN_VALUE
------------
1
2
3
4
5
已选择5行。
类似的，可以用这种方法构造其它与连续数相关的其它集合，例如：
10以内的所有偶数的表
select column_value*2 from table(seriallist(5))
最近5天的日期表
select sysdate+1-column_value from table(seriallist(5)) ;
实例：
http://www.itpub.net/showthread.php?s=&threadid=13618
原问题：
有表t，中只有字段c整型，
用一句SQL语句找出在表中的最大值和最小值之间的（不包括最大值和最小值）且表中不存在的值。
比如, 表中的记录为:
1
2
6
9
则查询结果应为:
3
4
5
7
8
用这个方法后的实现方式：
scott@O9I.US.ORACLE.COM> select column_value c from
2 table(Utils.seriallist((select max(c) from t))) l
3 where column_value >= (select min(c) from t)
4 minus
5 select c from t
6 /
C
----------
3
4
5
7
8
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
428 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
5 rows processed
为了对比，下面显示用类似方法通过all_objects进行的查询
scott@O9I.US.ORACLE.COM> select rn c from
2 (select rownum rn from all_objects where rownum <= (select max(c) from t))
3 where rn >= (select min(c) from t)
4 minus
5 select c from t
6 /
C
----------
3
4
5
7
8
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
80 consistent gets
0 physical reads
0 redo size
428 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
5 rows processed
结论：这种方法，由于全部构造工作都在内存中进行， 从而避免了不必要的IO开销。另外，集合的大小是通过参数来确定的，使得使用这个表的查询，消除了不确定性。
可改进之处：你可以为seriallist函数增加一个minval参数，某些情况下(比如上面的示例中)使用起来也许会更方便。
2005.07.06: 这篇文章给出另一种简洁、高效的方法: http://blog.itpub.net/post/6/34465
(需要引用, 请注明出处: http://oldwain.itpub.net)
