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 操作符。就像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." -- 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调用性能可能会差些。