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调用性能可能会差些。