Oracle遞迴查詢樹形資料範例程式碼

2022-11-11 14:02:27

概述

實際生活有很多樹形結構的資料，比如公司分為多個部門，部門下分為多個組，組下分為多個員工；省市縣的歸屬；頁面選單欄等等。

如果想查詢某個節點的父節點或者子節點，一般通過表自身連線完成，但如果該節點的子節點還有多層結構，就需要使用遞迴呼叫。但如果資料量特別大，遞迴的次數指數級上升，而且查詢資料庫的次數也指數級上升，導致程式和資料庫壓力劇增，查詢時間特別長。那資料庫有沒有遞迴查詢語句呢？答案是肯定的。

start with connect by prior 遞迴查詢

1、資料準備

create table area_test(
  id         number(10) not null,
  parent_id  number(10),
  name       varchar2(255) not null
);

alter table area_test add (constraint district_pk primary key (id));

insert into area_test (ID, PARENT_ID, NAME) values (1, null, '中國');
insert into area_test (ID, PARENT_ID, NAME) values (11, 1, '河南省'); 
insert into area_test (ID, PARENT_ID, NAME) values (12, 1, '北京市');
insert into area_test (ID, PARENT_ID, NAME) values (111, 11, '鄭州市');
insert into area_test (ID, PARENT_ID, NAME) values (112, 11, '平頂山市');
insert into area_test (ID, PARENT_ID, NAME) values (113, 11, '洛陽市');
insert into area_test (ID, PARENT_ID, NAME) values (114, 11, '新鄉市');
insert into area_test (ID, PARENT_ID, NAME) values (115, 11, '南陽市');
insert into area_test (ID, PARENT_ID, NAME) values (121, 12, '朝陽區');
insert into area_test (ID, PARENT_ID, NAME) values (122, 12, '昌平區');
insert into area_test (ID, PARENT_ID, NAME) values (1111, 111, '二七區');
insert into area_test (ID, PARENT_ID, NAME) values (1112, 111, '中原區');
insert into area_test (ID, PARENT_ID, NAME) values (1113, 111, '新鄭市');
insert into area_test (ID, PARENT_ID, NAME) values (1114, 111, '經開區');
insert into area_test (ID, PARENT_ID, NAME) values (1115, 111, '金水區');
insert into area_test (ID, PARENT_ID, NAME) values (1121, 112, '湛河區');
insert into area_test (ID, PARENT_ID, NAME) values (1122, 112, '舞鋼市');
insert into area_test (ID, PARENT_ID, NAME) values (1123, 112, '寶丰市');
insert into area_test (ID, PARENT_ID, NAME) values (11221, 1122, '尚店鎮');

2 start with connect by prior遞迴查詢

start with 子句：遍歷起始條件。如果要查父結點，這裡可以用子結點的列，反之亦然。
connect by 子句：連線條件。prior 跟父節點列parentid放在一起，就是往父結點方向遍歷；prior 跟子結點列subid放在一起，則往葉子結點方向遍歷。parent_id、id兩列誰放在 “=” 前都無所謂，關鍵是prior跟誰在一起。
order by 子句：排序。

常用的select項：

LEVEL：級別
connect_by_root：根節點
sys_connect_by_path：遞迴路徑

2.1 查詢所有子節點

select t.*,LEVEL
from area_test t
start with name ='鄭州市'
connect by prior id=parent_id

其實，如果單層結構，使用表自身連線也可以實現：

select * from area_test t1,area_test t2 
where t1.PARENT_ID = t2.ID and t2.name='鄭州市';

當查詢節點下有多層資料：

select t.*,LEVEL
from area_test t
start with name ='河南省'
connect by prior id=parent_id

select * from area_test t1,area_test t2 
where t1.PARENT_ID = t2.ID and t2.name='河南省';

如果使用自身連線，也只能查到子一級節點的資料，需要遍歷子一級節點，遞迴查詢每個子一級節點下的子節點。明顯麻煩很多！！！

2.2 查詢所有父節點

select t.*,level
from area_test t
start with name ='鄭州市'
connect by prior t.parent_id=t.id
order by level asc;

2.3 查詢指定節點的根節點

select d.*,
	   connect_by_root(d.id) rootid,
	   connect_by_root(d.name) rootname
from area_test d
where name='二七區'
start with d.parent_id IS NULL
connect by prior d.id=d.parent_id

select d.*,
	   connect_by_root(d.id) rootid,
	   connect_by_root(d.name) rootname
from area_test d
start with d.parent_id IS NULL
connect by prior d.id=d.parent_id

2.4 查詢下行政組織遞迴路徑

select id, parent_id, name, sys_connect_by_path(name, '->') namepath, level
from area_test
start with name = '平頂山市'
connect by prior id = parent_id

3 with遞迴查詢

3.1 with遞迴子類

with tmp(id, parent_id, name) 
as (
	select id, parent_id, name
    from area_test
    where name = '平頂山市'
    union all
    select d.id, d.parent_id, d.name
    from tmp, area_test d
    where tmp.id = d.parent_id
   )
select * from tmp;

3.2 遞迴父類別

with tmp(id, parent_id, name) 
as
  (
   select id, parent_id, name
   from area_test
   where name = '二七區'
   union all
   select d.id, d.parent_id, d.name
   from tmp, area_test d
   where tmp.parent_id = d.id
   )
select * from tmp;