ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用PL/SQL table 来构造连续数集合表

使用PL/SQL table 来构造连续数集合表

原创 Linux操作系统 作者:oldwain 时间:2019-03-03 14:09:05 0 删除 编辑

很多时候,我们需要在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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/32/viewspace-184189/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2001-09-24

  • 博文量
    14
  • 访问量
    8105