如何使用PL/SQL进行分级查询

日期： 2010-11-30 作者：Dan Clamage翻译：孙瑞 来源：TechTarget中国 英文

问：请问如何在SQL中生成数字?我见过其它的人使用DBA_OBJECTS作为一个“大”表来生成ROWNUM。但是对于使用数据字典视图来说，这看上去并不是一个合适的办法。 　　答：我很同意你的看法，使用数据字典视图仅仅生成数字是很不合适的，特别是当你的系统需要更多值而不是对象的时候。那么下面就来介绍一个我经常使用的方法，即使用一个分级查询来生成值： select rownum from dual connect by rownum <=10;     ROWNUM ——R……

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论，本站保留删除与本文无关和不雅评论的权力。

问：请问如何在SQL中生成数字?我见过其它的人使用DBA_OBJECTS作为一个“大”表来生成ROWNUM。但是对于使用数据字典视图来说，这看上去并不是一个合适的办法。

　　答：我很同意你的看法，使用数据字典视图仅仅生成数字是很不合适的，特别是当你的系统需要更多值而不是对象的时候。那么下面就来介绍一个我经常使用的方法，即使用一个分级查询来生成值：

select rownum
from dual
connect by rownum <=10;

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

　　然后可以很轻松地通过内联视图添加到你的查询中：

-- generate lots of fake data
SELECT 'CUSTOMER_' || row_num customer_name
FROM (SELECT ROWNUM row_num
      FROM DUAL
      CONNECT BY ROWNUM <= 10);

CUSTOMER_NAME
-----------------
CUSTOMER_1
CUSTOMER_2
CUSTOMER_3
CUSTOMER_4
CUSTOMER_5
CUSTOMER_6
CUSTOMER_7
CUSTOMER_8
CUSTOMER_9
CUSTOMER_10

10 rows selected.

　　注意我在内联视图中重命名了pseudocolumn ROWNUM，从而外部查询生成的ROWNUM就不会有任何疑义。

-- create types to support pipelined function 
CREATE OR REPLACE TYPE num_rec IS OBJECT
                  (num INTEGER);
/

CREATE OR REPLACE TYPE num_tab IS TABLE OF num_rec;
/

-- generate sequential numbers
-- given a starting value and total count
CREATE OR REPLACE FUNCTION numgen(
  p_start IN INTEGER
 ,p_count IN INTEGER
)
  RETURN num_tab
  PIPELINED
IS
BEGIN
  FOR rec IN (SELECT num_rec(ROWNUM + p_start - 1) r
              FROM DUAL
              CONNECT BY ROWNUM <= p_count) LOOP
    PIPE ROW (rec.r);
  END LOOP;

  RETURN;
END numgen;
/

-- generate data
SELECT 'CUSTOMER_' || num customer_name
FROM TABLE(numgen(10, 10));

CUSTOMER_NAME
-----------------
CUSTOMER_10
CUSTOMER_11
CUSTOMER_12
CUSTOMER_13
CUSTOMER_14
CUSTOMER_15
CUSTOMER_16
CUSTOMER_17
CUSTOMER_18
CUSTOMER_19

10 rows selected.

　　管道函数可是用来异步返回大数据集，你可能会不解为什么在函数中仅仅使用一个FOR循环?

-- alternate method using FOR loop
CREATE OR REPLACE FUNCTION numgen2(
  p_start IN INTEGER
 ,p_count IN INTEGER
)
  RETURN num_tab
  PIPELINED
IS
BEGIN
  FOR i IN p_start .. p_start + p_count - 1 LOOP
    PIPE ROW (num_rec(i));
  END LOOP;

  RETURN;
END numgen2;
/

SELECT 'CUSTOMER_' || num customer_name
FROM TABLE(numgen2(10, 10));

CUSTOMER_NAME
-----------------
CUSTOMER_10
CUSTOMER_11
CUSTOMER_12
CUSTOMER_13
CUSTOMER_14
CUSTOMER_15
CUSTOMER_16
CUSTOMER_17
CUSTOMER_18
CUSTOMER_19

10 rows selected.

　　你当然可以得到相同的结果，那么这个方法会比其它的快一些吗?那么我们来试一试百万级别的数据，来看看会发生什么：

　　SQL> set timing on
　　SQL> BEGIN
　　2 FOR rec IN (SELECT num
　　3 FROM TABLE(numgen(1, 1000000))) LOOP
　　4 NULL;
　　5 END LOOP;
　　6 END;
　　7 /
　　PL/SQL procedure successfully completed.
　　Elapsed: 00:00:05.32
　　SQL>
　　SQL> BEGIN
　　2 FOR rec IN (SELECT num
　　3 FROM TABLE(numgen2(1, 1000000))) LOOP
　　4 NULL;
　　5 END LOOP;
　　6 END;
　　7 /
　　PL/SQL procedure successfully completed.
　　Elapsed: 00:00:01.11
　　-- run twice to be sure
　　SQL> BEGIN
　　2 FOR rec IN (SELECT num
　　3 FROM TABLE(numgen(1, 1000000))) LOOP
　　4 NULL;
　　5 END LOOP;
　　6 END;
　　7 /
　　PL/SQL procedure successfully completed.
　　Elapsed: 00:00:05.32
　　SQL>
　　SQL> BEGIN
　　2 FOR rec IN (SELECT num
　　3 FROM TABLE(numgen2(1, 1000000))) LOOP
　　4 NULL;
　　5 END LOOP;
　　6 END;
　　7 /
　　PL/SQL procedure successfully completed.
　　Elapsed: 00:00:01.09

　　纯PL/SQL解决方案绝对要快上许多。

作者

Dan Clamage
Dan Clamage

翻译

孙瑞
孙瑞

相关推荐