select r
  from ( select r
           from ( select rownum r
                   from all_objects
                  where rownum < 50 )
          order by dbms_random.value )
  where rownum <= 6;

该查询通过内联视图生成数字1到49。我把最内层查询放到内联视图中，并利用DBMS_RANDOM.VALUE按一个随机数进行排序。再把结果集放到另一个内联视图中并只取前6行。如果我反复运行该查询，则每次都会得到不同的6行。

这类问题经常会遇到--也许不是有关如何生成6个随机数而是"如何得到N行"的问题。例如，我想得到包含2003年1月1日到2003年1月15日之间的所有日期。我可以用一个包含15行的表轻松地来生成这些日期；事实上，select to_date('01-jan-2003')+rownum-1 from all_objects where rownum <= 15就能完成，但这会带来一定数量的系统开销（ALL_OBJECTS是一个视图，而且是一个复杂的视图）。我本可以创建一个表并向其中填入一些行，但它也不总是让人喜欢。这个问题就变成了如何不用"真正的"表来完成这件事的问题，Oracle9i及其PIPELINED函数功能能够解决这个问题。我可以写一个PL/SQL函数，可以像表那样来操作它。先从一个SQL集合类型开始；它说明我的PIPELINED函数返回的是什么。这样，我选用一个数字表；所创建的虚表只简单的返回数字1、2、3、…… N：

SQL> create type array
  2  as table of number
  3  /
Type created.

接下来，创建真正的PIPELINED函数。该函数接收一个输入，用于限制返回行的数目。如果不提供输入，该函数将一直不停地生成行（所以一定要当心，确保在查询中使用ROWNUM或其他限制）。第4行的PIPELINED关键字使该函数能像表一样工作：

SQL> create function
  2  vtable( n in number default null )
  3  return array
  4  PIPELINED
  5  as

  6  begin
  7     for i in 1 .. nvl(n,999999999)
  8     loop
  9         pipe row(i);
 10     end loop;
 11     return;
 12  end;
 13  /
Function created.

假设我需要3行。我就可以用以下两种方法之一来完成这件事：

SQL> select *
  2    from TABLE(vtable(3))
  3  /

 COLUMN_VALUE
 ------------

           1
           2
           3

或者

SQL> select *
  2    from TABLE(vtable)
  3   where rownum <= 3
  4  /

 COLUMN_VALUE
 ------------
           1
           2
           3

现在我准备用下面的函数重新回答最开始的问题：

SQL> select *
  2    from (
  3  select *
  4    from (
  5  select *
  6    from table(vtable(49))
  7         )
  8   order by dbms_random.random
  9         )
 10   where rownum <= 6
 11  /

 COLUMN_VALUE
 ------------
          47
          42
          40
          15
          48
          23


6 rows selected.

我可以用这个vtable函数做很多事，如生成那些日期的范围：

SQL> select to_date('01-jan-2003')+
  2        column_value-1
  3  from TABLE(vtable(15));

TO_DATE('
---------
01-JAN-03
...
15-JAN-03

15 rows selected.

请注意我所使用的列名：COLUMN_VALUE。这是PIPELINED函数返回的默认列名。

在Oracle数据库10g中有哪些最新的东西？

你能否指出Oracle数据库10g中你最喜欢的几个重要特性吗？

我今天选出Oracle数据库10g的3个特性，它们是：

• 自动存储管理（ASM）
• 总体上的可管理性，特别是自动数据库诊断监测器（ADDM）
• 普遍适用的SQL新特性，特别是分区的"稀疏"外部连接

由于用文字很难说明ASM和ADDM，所以我希望你到Oracle Technology Network上去了解有关内容，而分区的"稀疏"外部连接很容易说明。

数据通常是以稀疏的形式存储的。也就是说，如果某一给定时间不存在值，则表中就不存在行。但是，当数据在时间维度上很"密集"时，执行时间序列计算（例如，按年计算）就非常容易。这是因为在每个时期，密集数据都充满固定数量的行，这样通过实际偏移量来使用分析窗口函数就变得很简单。举一个SALES结果集的例子它具有PRODUCT_NAME、DATE_OF_SALE和TOT_SALES几个属性。你的目标是显示每行的TOT_SALES并与一年前同一行的TOT_SALES进行对比。如果每个产品的所有月份都有数据，那么你只需用LAG()分析函数向后查看12行即可。但是如果有缺失的月份会出现什么情况呢？假设没有上一年6月份的数据。这时你就会把今年6月份和去年5月份的数据--而不是空值--进行比较（因为实际上6月份的数据缺失）。在这种情况下分区外部连接就能派上用场了。

我们举一个小例子。我想做的就是比较这个月与上个月的销售额。所以，我需要按PRODUCT_NAME划分结果集，按DATE_OF_SALE对它进行排序，并使用LAG()向回查看一行的数据。从以下数据开始：

ops$tkyte@ORA10g> select *
  2    from sales
  3   order by product_name,
  4           date_of_sale;


PRODUCT_NAME   DATE_OF_SALE   TOT_SALES
------------   ------------   ---------
TV               01-JUL-03        496
TV               01-AUG-03        993
TV               01-OCT-03        468
TV               01-NOV-03        535
TV               01-DEC-03        665
VCR              01-JUL-03        617
VCR              01-AUG-03        984
VCR              01-SEP-03        308
VCR              01-OCT-03        954
VCR              01-DEC-03        947

10 rows selected.

请注意这里出现的缺失数据。11月份没有VCR的销售额数据，9月份没有TV的销售额数据。如果我现在试着用LAG()来分析：

ops$tkyte@ORA10g> select product_name,
  2    date_of_sale,
  3    lag(date_of_sale)
  4    over (partition by product_name
  5         order by date_of_sale)

  6         last_month,
  7    tot_sales,
  8    lag(tot_sales)
  9    over (partition by product_name
 10         order by date_of_sale)
 11        last_months_sales
 12    from sales
 13  /
   

PRODUCT_NAME  DATE_OF_SALE    TOT_SALES
------------  ------------    ---------
TV              01-JUL-03         496
TV              01-AUG-03         993
TV              01-SEP-03
TV              01-OCT-03         468

TV              01-NOV-03         535
TV              01-DEC-03         665
VCR             01-JUL-03         617
VCR             01-AUG-03         984
VCR             01-SEP-03         308
VCR             01-OCT-03         954
VCR             01-NOV-03
VCR             01-DEC-03         947

12 rows selected.

也就是说，我希望结果集中填上缺失的日期。使用下面的查询可以达到这一目的：

with dates as
( select add_months
        ( to_date( '01-jul-2003' ),
          column_value-1) dt
    from table( vtable(6) )
)
select product_name,
      dt,
      tot_sales
  from dates left outer join
      sales partition by (product_name)
      on ( dates.dt =

           sales.date_of_sale )

我借用了前面讲的VTABLE的概念，因为我要做的就是构建一个包含所需要的所有日期的结果集--最近6个月的。子查询DATES返回6行：每行报告每月的日期。现在我简单地将DATES子查询与SALES数据做外部连接来合成缺失的行。但请注意在外部连接中PARTITION BY子句的使用。我把SALES表分成N个部分，每一部分与DATES的数据做外部连接。在这种方式下，SALES的每一部分都"补充"上了缺失数据以及相应的分区关键字。这就是上面的结果，其中补充上了9月份和11月份的数据，它就是用这个查询创建的。

下一步 向Tom咨询 asktom.oracle.com Oracle副总裁Tom Kyte会为你解答你所遇到的最困难的技术问题。本专栏从该论坛中精选了部分重要内容。 阅读 Tom的更多文章 asktom.oracle.com/~tkyte 《高超的设计成就了高效的Oracle产品》（Effective Oracle by Design） shop.osborne.com/cgi-bin/oraclepress/ 0072230657.html 有关Oracle Database 10g的更多文章 otn.oracle.com/products/database

在使用分区子句之前，我不得不生成了一个包含所有不同的PRODUCT_NAMES集，并取该集与DATES集的笛卡尔乘积。然后才能跟它做外部连接。一般来说，这不是我们想要的，因为这样就必须扫描所有SALES记录来生成这个惟一的PRODUCT_NAMES集。这种查询就像这样：

with dates as
( select add_months
        ( to_date( '01-jul-2003' ),
          column_value-1) dt
    from table( vtable(6) )
),
products as
( select distinct product_name
    from sales
),
dates_products as
( select * from dates, products
)
select sales.product_name,
      dates_products.dt,
      sales.tot_sales
  from dates_products left outer join
      sales
      on ( dates_products.dt =
                   sales.date_of_sale
          and
          dates_products.product_name =

                   sales.product_name)

随着要分析的数据的维数的增加，需要生成的这个笛卡尔乘积的原始大小也会增大。在外部连接上使用分区子句使这种查询的规模更易于调整，而且，在这种情况下，还不用事先生成惟一的产品名集。

为执行所需要的分析而进行的整个查询会简单地成为该分区外部连接查询的一个逻辑扩展。 一旦有了上面的数据，我就可以用内联视图轻松完成LAG()分析：

with dates as
( select add_months
        ( to_date( '01-jul-2003' ),
         column_value-1) dt
    from table( vtable(6) )
)
select product_name,
      dt,
      lag(dt)
      over (partition by product_name
            order by dt) last_dt,
      tot_sales,
      lag(tot_sales)
      over (partition by product_name
            order by dt) last_sales
  from (
select product_name,
      dt,
      tot_sales
  from dates left outer join
     sales partition by (product_name)

     on (dates.dt = sales.date_of_sale)
       )

该查询比较本月和上个月的销售额，即使上个月的销售额"缺失"也可以。