树查询
使用树查询的前提条件是: 在一条记录中记录了当前节点的ID和这个节点的父ID。
注意:一旦数据中出现了循环记录,如两个节点互为对方的父结点,系统就会报 ORA-01436错误(ORA-01436: 用户数据中的CONNECT BY 循环)
第一步:创建表
-
create table MENU
-
(
-
MENU_ID NUMBER not null,
-
PARENT_ID NUMBER,
-
MENU_NAME NVARCHAR2(20)
-
);
第二步:插入数据
-
insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (1, null,'AAAA');
-
insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (2, 1, 'BBBB');
-
insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (3, 1, 'CCCC');
-
insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (4, 1, 'DDDD');
-
insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (5, 2, 'EEEE');
-
insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (6, 2, 'FFFF');
-
insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (7, 2, 'GGGG');
-
insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (8, 3, 'HHHH');
-
commit;
第三步:查询
-
select menu_id,rpad(' ',(level-1)*4)||menu_name from menu
-
connect by parent_id = prior menu_id
-
start with parent_id is null
显示出树的级别查询
-
select menu_id,rpad(' ',(level-1)*4)||menu_name,level from menu
-
connect by parent_id = prior menu_id
-
start with parent_id is null;
也可以这样写:
-
select role_id,parent_grade_id,level from audit_role
-
where level > 1
-
connect by prior role_id = parent_grade_id
-
start with role_id=1
-
order by level;