注意:一旦数据中出现了循环记录，如两个节点互为对方的父结点，系统就会报 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;
原文：https://www.cnblogs.com/liuys635/p/12346465.html