11GR2增加了递归With子查询
详见剑破冰山 429页

树形遍历有了新方法.

原来使用sys_connect_by_path函数

select
employee_id,
first_name,
manager_id,
level,
sys_connect_by_path(employee_id,'\') as path,
connect_by_root(first_name) as top_manager
from employees
start with manager_id is null
connect by prior employee_id=manager_id;

递归With新写法.
由两个SQL union all而成.
上面的SQL是起始的条件.
下面的SQL是递归的条件.

with t(employee_id,first_name,manager_id,the_level,path,top_manager) as
(
select
employee_id,
first_name,
manager_id,
1 as the_level,
'\'||employee_id as path,
first_name as top_manager
from employees
where manager_id is null
union all
select
e.employee_id,
e.first_name,
e.manager_id,
1+t.the_level as the_level,
t.path||'\'||e.employee_id as path,
t.top_manager
from t,employees e
where t.employee_id=e.manager_id
)
select * from t;

搜索子句
SEARCH
① { DEPTH FIRST BY c_alias [, c_alias]...
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
② | BREADTH FIRST BY c_alias [, c_alias]...
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
}
③ SET ordering_column

注:
①DEPTH FIRST表示按深度优先的顺序来输出。BY后面的列名及其升降序、空值放置顺序指明了在深度优先的前提下，同一层次的数据的排序情况。
这和原来CONNECT BY查询中的ORDER SIBLING BY子句是一样的。
②BREADTH FIRST表示按广度优先的顺序来输出。BY后面的列名及其升降序、空值放置顺序指明了在广度优先的前提下，同一层次的数据的排序情况。
③列名ordering_column用于存放排序后的序号，是一个从1开始的连续正整数。后续的查询中可以利用这个列得知某行数据在整个结果集中的位置。

sys_connect_by_path结果:

深度优先遍历

with t(employee_id,first_name,manager_id,the_level,path,top_manager) as
(
select
employee_id,
first_name,
manager_id,
1 as the_level,
'\'||employee_id as path,
first_name as top_manager
from employees
where manager_id is null
union all
select
e.employee_id,
e.first_name,
e.manager_id,
1+t.the_level as the_level,
t.path||'\'||e.employee_id as path,
t.top_manager
from t,employees e
where t.employee_id=e.manager_id
)
search depth first by manager_id set seq
select * from t;

广度优先遍历

with t(employee_id,first_name,manager_id,the_level,path,top_manager) as
(
select
employee_id,
first_name,
manager_id,
1 as the_level,
'\'||employee_id as path,
first_name as top_manager
from employees
where manager_id is null
union all
select
e.employee_id,
e.first_name,
e.manager_id,
1+t.the_level as the_level,
t.path||'\'||e.employee_id as path,
t.top_manager
from t,employees e
where t.employee_id=e.manager_id
)
search BREADTH first by manager_id set seq
select * from t;

参考:
http://www.oracle.com/technetwork/cn/articles/hartley-recursive-086819-zhs.html

ITPub博客

11GR2递归With子查询

最新文章