11GR2遞迴With子查詢

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部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1965778/，如需轉載，請註明出處，否則將追究法律責任。

11GR2遞迴With子查詢

相關文章