您當前位置：首頁 > 數據庫 > 數據庫應用 > Oracle 遞歸查詢詳解

Oracle 遞歸查詢詳解

來源：程序員人生發布時間：2015-02-27 07:53:43 閱讀次數：5348次

創建表和主外鍵

CREATE TABLE SC_DISTRICT ( ID NUMBER(10) NOT NULL, PARENT_ID NUMBER(10), NAME VARCHAR2(255 BYTE) NOT NULL ); ALTER TABLE SC_DISTRICT ADD ( CONSTRAINT SC_DISTRICT_PK PRIMARY KEY (ID)); ALTER TABLE SC_DISTRICT ADD ( CONSTRAINT SC_DISTRICT_R01 FOREIGN KEY (PARENT_ID) REFERENCES SC_DISTRICT (ID) );

插入數據

insert into A_DISTRICT (id, parent_id, name) values (1, null, '河南省'); insert into A_DISTRICT (id, parent_id, name) values (2, null, '山東省'); insert into A_DISTRICT (id, parent_id, name) values (3, 1, '鄭州市'); insert into A_DISTRICT (id, parent_id, name) values (4, 1, '開封市'); insert into A_DISTRICT (id, parent_id, name) values (5, 1, '商丘市'); insert into A_DISTRICT (id, parent_id, name) values (6, 3, '中原區'); insert into A_DISTRICT (id, parent_id, name) values (7, 3, '金水區'); insert into A_DISTRICT (id, parent_id, name) values (8, 3, '27區'); insert into A_DISTRICT (id, parent_id, name) values (9, 3, '城管回族區'); insert into A_DISTRICT (id, parent_id, name) values (10, 3, '上街區'); insert into A_DISTRICT (id, parent_id, name) values (11, 3, '惠濟區'); insert into A_DISTRICT (id, parent_id, name) values (12, 4, '開封縣'); insert into A_DISTRICT (id, parent_id, name) values (13, 4, '杞縣'); insert into A_DISTRICT (id, parent_id, name) values (14, 4, '蘭考縣'); insert into A_DISTRICT (id, parent_id, name) values (15, 4, '通許縣'); insert into A_DISTRICT (id, parent_id, name) values (16, 13, '城關鎮'); insert into A_DISTRICT (id, parent_id, name) values (17, 13, '5里河鎮'); insert into A_DISTRICT (id, parent_id, name) values (18, 13, '邢口鎮'); insert into A_DISTRICT (id, parent_id, name) values (19, 13, '柿園鄉'); insert into A_DISTRICT (id, parent_id, name) values (20, 13, '城郊鄉');

生成數據以下

遞歸語法和主要使用函數

select * from 表 start with 條件入口 connect by prior id = parent_id（向下）

select * from 表 start with 條件入口 connect by prior parent_id = id（向上）

如果不寫prior，表明前序遍歷未指明遍歷方向，所以不進行遞歸，習慣上會在語句后面加上order by排序和group by分組

connect_by_root(列名) 查詢根節點

connect_by_isleaf 查詢是不是葉子節點，0是，1不是

sys_connect_by_path 查詢遞歸路徑

實戰

查詢根節點

select id, parent_id, name from a_district start with parent_id is null connect by id = parent_id order by id

遞歸查詢杞縣的所有上級節點（包括當前節點）

select id, parent_id, name from a_district start with name='杞縣' connect by prior parent_id = id order by id

遞歸查詢杞縣的所有上級節點（包括當前節點和根節點）

select id, parent_id, name,connect_by_root(id) city_id, connect_by_root(name) city_name from a_district start with name='杞縣' connect by prior parent_id = id order by id

遞歸查詢杞縣的所有子城鎮鄉（包括當前節點）

select id, parent_id, name from a_district start with name='杞縣' connect by prior id = parent_id order by id

遞歸查詢開封市的所有城鎮鄉（包括當前節點和根節點）

select id, parent_id, name, connect_by_root(id) city_id, connect_by_root(name) city_name from a_district start with name='開封市' connect by prior id = parent_id order by id

上行遞歸查詢城郊鄉深度、是不是包括葉子節點

select id, name, parent_id, level, connect_by_isleaf isleaf from a_district start with name = '城郊鄉' connect by prior parent_id = id order by id

下行遞歸查詢河南省深度、是不是包括葉子節點

select id, name, parent_id, level, connect_by_isleaf isleaf from a_district start with name = '河南省' connect by prior id = parent_id order by id

上行遞歸查詢杞縣路徑

select id, name, parent_id, substr(sys_connect_by_path(name,'->'),3) name_path from a_district start with name='杞縣' connect by prior parent_id = id order by id

下行遞歸查詢河南省路徑

select id, name, parent_id, substr(sys_connect_by_path(name,'->'),3) name_path from a_district start with name='河南省' connect by prior id = parent_id order by id

綜合使用

select id, name, parent_id, level, connect_by_isleaf isleaf, substr(sys_connect_by_path(name,'->'),3) name_path from a_district start with name='河南省' connect by prior id = parent_id order by id