1. 创建表
menu
create table MENU
(
MENU_ID NUMBER
not null,
PARENT_ID NUMBER,
MENU_NAME
NVARCHAR2(20)
)
2. 插入数据
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;
3. 查询语句
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
参阅:
说明:我们的树状属性一般都是在一条记录中记录一个当前节点的ID和这个节点的父ID来实现。但是一旦数据中出现了循环记录,如两个节点互为对方的父结点,系统就会报 ORA-01436错误(ORA-01436: 用户数据中的CONNECT
BY 循环)