oracle调整序号发生器,整型序号产生器(三)

CONNECT BY LEVEL方法

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

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

selectlevel

fromdual

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

selectlevel,

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

fromt4

connect by level <= 3

order bylevel,

path ;

LEVEL

PATHKEY

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

1 /aa

1 /bb

2

/a/aa

2

/a/bb

2

/b/aa

2

/b/bb

3

/a/a/aa

3

/a/a/bb

3

/a/b/aa

3

/a/b/bb

3

/b/a/aa

3

/b/a/bb

3

/b/b/aa

3

/b/b/bb

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

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

变量

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

clear breaks

variable v_total_rows number

execute :v_total_rows := 0

selectlevel

fromdual

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.

selectlevel

fromdual

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_WEEKVAL

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

0

1100

2

3300

4400

5500

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下测试).

selectlevel

fromdual

connect by

level <= 10

AND PRIOR DUMMY =

DUMMY

;

ERROR:

ORA-01436: CONNECT BY loop in user data

selectlevel

fromdual

connect by

level <= 10

AND PRIOR 1 = 1

;

ERROR:

ORA-01436: CONNECT BY loop in user data

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