ITPub博客

整型序号产生器(三)

原创 Linux操作系统 作者:guoge 时间:2007-12-13 10:23:00 0 删除 编辑

CONNECT BY LEVEL 方法

这里介绍使用CONNECT BY子句来产生连续整数。这个方法是 Mikito Harakiri Ask Tom "how to display selective record twice in the query?"提出的。

使用这个方法你可以产生从1开始的整数,例如:

select  level

from    dual

connect by level <= 10 ;

 

     LEVEL

----------

         1

         2

         3

         4

         5

         6

         7

         8

         9

        10

 

没有 PRIOR的查询

这个查询有点特殊,没有PRIOR 操作符。对于一个只有"a" "b"的表,使用这个方法,我们来看看它是如何工作的。

break on level duplicates skip 1

 

column path format a10

 

select     level, sys_connect_by_path( key, '/' ) as path, key

from       t4

connect by level <= 3

order by   level, path ;

 

     LEVEL PATH       KEY

---------- ---------- ---

         1 /a         a

         1 /b         b

 

         2 /a/a       a

         2 /a/b       b

         2 /b/a       a

         2 /b/b       b

 

         3 /a/a/a     a

         3 /a/a/b     b

         3 /a/b/a     a

         3 /a/b/b     b

         3 /b/a/a     a

         3 /b/a/b     b

         3 /b/b/a     a

         3 /b/b/b     b

 

使用PRIOR的没有 CONNECT BY条件Oracle 返回所有可能的层次排列。这个可能在指数增长的数字输出是需要。

这种情况在可能需要返回多个连续整数时有用。

变量

如果要求返回的数字是硬编码且比1大,这个方法没什么问题。如果这个值是绑定变量,其值设为0,或者为负数,NULL ,这个方法可能就不行,它至少会返回一天记录。

clear breaks

 

variable v_total_rows number

 

execute :v_total_rows := 0

 

select  level

from    dual

connect by level <= :v_total_rows ;

 

     LEVEL

----------

         1

 

execute :v_total_rows := -5

 

PL/SQL procedure successfully completed.

 

/

 

     LEVEL

----------

         1

 

1 row selected.

 

 

execute :v_total_rows := null

 

PL/SQL procedure successfully completed.

 

/

 

     LEVEL

----------

         1

 

1 row selected.

 

 

加上一个简单的 WHERE子句能够解决这个问题.

execute :v_total_rows := 0

 

PL/SQL procedure successfully completed.

 

 

select  level

from    dual

WHERE   :V_TOTAL_ROWS >= 1

connect by level <= :v_total_rows ;

 

no rows selected

 

 

execute :v_total_rows := -5

 

PL/SQL procedure successfully completed.

 

/

 

no rows selected

 

 

execute :v_total_rows := null

 

PL/SQL procedure successfully completed.

 

/

 

no rows selected

 

 

execute :v_total_rows := 3

 

PL/SQL procedure successfully completed.

 

/

 

     LEVEL

----------

         1

         2

         3

 

3 rows selected.

 

 

Day of the Week 例子

select

  day_of_week ,

  t.val

from

  ( select level - 1 as day_of_week

    from dual

    connect by level <= 7

  ) i

    left outer join t using( day_of_week )

order by

  day_of_week

;

 

DAY_OF_WEEK        VAL

----------- ----------

          0

          1        100

          2

          3        300

          4        400

          5        500

          6

 
是否使用PRIOR

Laurent Schneider在他的博客 Bible of Oracle 中讨论到像CONNECT BY LEVEL <= 10 的子句是不合法的,因为它之前没有PRIOR 操作符。就像ORACLESQL Reference Manual讲到的:

"in a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row." -- Oracle® Database SQL Reference 10g Release 2 (10.2)

另外一种说法是这是文档的一个bug。事实上, 没有PRIOR 操作符的CONNECT BY子句在在Oracle 10g下没有什么问题,在某些9i版本也支持(其实我的8.1.7.4也没问题)

看下面的查询,看起来等价于 CONNECT BY LEVEL <= 10,但它不能产生所需要的0条记录。 (Oracle 10g下测试).

select  level

from    dual

connect by

  level <= 10

  AND PRIOR DUMMY = DUMMY

;

ERROR:

ORA-01436: CONNECT BY loop in user data

 

 

 

select  level

from    dual

connect by

  level <= 10

  AND PRIOR 1 = 1

;

ERROR:

ORA-01436: CONNECT BY loop in user data

 
 

下面的方法可能比开头给的方法更加“合法”,因为它给出PRIOR 条件而且不会产生CONNECT BY loop, 但是包含它的PL/SQL调用性能可能会差些。

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

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

注册时间:2007-12-12

  • 博文量
    72
  • 访问量
    208657