CONNECT BY LEVEL 方法

这里介绍使用CONNECT BY子句来产生连续整数。这个方法是 Mikito Harakiri 在提出的。

使用这个方法你可以产生从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在他的博客 中讨论到像CONNECT BY LEVEL <= 10 的子句是不合法的，因为它之前没有PRIOR 操作符。就像ORACLE的SQL Reference Manual讲到的：

"in a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row." --

另外一种说法是这是文档的一个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调用性能可能会差些。