Oracle 树操作、递归查询(select…start with…connect by…prio
--菜单目录结构表
create
?
table
?
tb_menu(
id number(10)
not
?
null
,
--主键id
title varchar2(50),
--标题
parent number(10)
--parent id
)
?
--父菜单
insert
?
into
?
tb_menu(id,title,parent)
values
(1,
‘父菜单1‘
,
null
);
insert
?
into
?
tb_menu(id,parent)
values
(2,
‘父菜单2‘
,
null
);
insert
?
into
?
tb_menu(id,parent)
values
(3,
‘父菜单3‘
,
null
);
insert
?
into
?
tb_menu(id,parent)
values
(4,
‘父菜单4‘
,
null
);
insert
?
into
?
tb_menu(id,parent)
values
(5,
‘父菜单5‘
,
null
);
--一级菜单
insert
?
into
?
tb_menu(id,parent)
values
(6,
‘一级菜单6‘
,1);
insert
?
into
?
tb_menu(id,parent)
values
(7,
‘一级菜单7‘
,1);
insert
?
into
?
tb_menu(id,parent)
values
(8,
‘一级菜单8‘
,1);
insert
?
into
?
tb_menu(id,parent)
values
(9,
‘一级菜单9‘
,2);
insert
?
into
?
tb_menu(id,parent)
values
(10,
‘一级菜单10‘
,2);
insert
?
into
?
tb_menu(id,parent)
values
(11,
‘一级菜单11‘
,2);
insert
?
into
?
tb_menu(id,parent)
values
(12,
‘一级菜单12‘
,3);
insert
?
into
?
tb_menu(id,parent)
values
(13,
‘一级菜单13‘
,3);
insert
?
into
?
tb_menu(id,parent)
values
(14,
‘一级菜单14‘
,3);
insert
?
into
?
tb_menu(id,parent)
values
(15,
‘一级菜单15‘
,4);
insert
?
into
?
tb_menu(id,parent)
values
(16,
‘一级菜单16‘
,4);
insert
?
into
?
tb_menu(id,parent)
values
(17,
‘一级菜单17‘
,4);
insert
?
into
?
tb_menu(id,parent)
values
(18,
‘一级菜单18‘
,5);
insert
?
into
?
tb_menu(id,parent)
values
(19,
‘一级菜单19‘
,5);
insert
?
into
?
tb_menu(id,parent)
values
(20,
‘一级菜单20‘
,5);
--二级菜单
insert
?
into
?
tb_menu(id,parent)
values
(21,
‘二级菜单21‘
,6);
insert
?
into
?
tb_menu(id,parent)
values
(22,
‘二级菜单22‘
,6);
insert
?
into
?
tb_menu(id,parent)
values
(23,
‘二级菜单23‘
,7);
insert
?
into
?
tb_menu(id,parent)
values
(24,
‘二级菜单24‘
,7);
insert
?
into
?
tb_menu(id,parent)
values
(25,
‘二级菜单25‘
,8);
insert
?
into
?
tb_menu(id,parent)
values
(26,
‘二级菜单26‘
,9);
insert
?
into
?
tb_menu(id,parent)
values
(27,
‘二级菜单27‘
,10);
insert
?
into
?
tb_menu(id,parent)
values
(28,
‘二级菜单28‘
,11);
insert
?
into
?
tb_menu(id,parent)
values
(29,
‘二级菜单29‘
,12);
insert
?
into
?
tb_menu(id,parent)
values
(30,
‘二级菜单30‘
,13);
insert
?
into
?
tb_menu(id,parent)
values
(31,
‘二级菜单31‘
,14);
insert
?
into
?
tb_menu(id,parent)
values
(32,
‘二级菜单32‘
,15);
insert
?
into
?
tb_menu(id,parent)
values
(33,
‘二级菜单33‘
,16);
insert
?
into
?
tb_menu(id,parent)
values
(34,
‘二级菜单34‘
,17);
insert
?
into
?
tb_menu(id,parent)
values
(35,
‘二级菜单35‘
,18);
insert
?
into
?
tb_menu(id,parent)
values
(36,
‘二级菜单36‘
,19);
insert
?
into
?
tb_menu(id,parent)
values
(37,
‘二级菜单37‘
,20);
--三级菜单
insert
?
into
?
tb_menu(id,parent)
values
(38,
‘三级菜单38‘
,21);
insert
?
into
?
tb_menu(id,parent)
values
(39,
‘三级菜单39‘
,22);
insert
?
into
?
tb_menu(id,parent)
values
(40,
‘三级菜单40‘
,23);
insert
?
into
?
tb_menu(id,parent)
values
(41,
‘三级菜单41‘
,24);
insert
?
into
?
tb_menu(id,parent)
values
(42,
‘三级菜单42‘
,25);
insert
?
into
?
tb_menu(id,parent)
values
(43,
‘三级菜单43‘
,26);
insert
?
into
?
tb_menu(id,parent)
values
(44,
‘三级菜单44‘
,27);
insert
?
into
?
tb_menu(id,parent)
values
(45,
‘三级菜单45‘
,28);
insert
?
into
?
tb_menu(id,parent)
values
(46,
‘三级菜单46‘
,28);
insert
?
into
?
tb_menu(id,parent)
values
(47,
‘三级菜单47‘
,29);
insert
?
into
?
tb_menu(id,parent)
values
(48,
‘三级菜单48‘
,30);
insert
?
into
?
tb_menu(id,parent)
values
(49,
‘三级菜单49‘
,31);
insert
?
into
?
tb_menu(id,parent)
values
(50,
‘三级菜单50‘
,31);
commit
;
?
select
?
*
from
?
tb_menu;
parent字段存储的是上级id,如果是顶级父节点,该parent为null(得补充一句,当初的确是这样设计的,不过现在知道,表中最好别有null记录,这会引起全文扫描,建议改成0代替)。
2、树操作
我们从最基本的操作,逐步列出树查询中常见的操作,所有查询出来的节点以家族中的辈份作比方。
(编辑:沈阳站长网)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
副标题[/!--empirenews.page--]
一、Oracle中start with…connect by prior子句用法
1、准备测试表和测试数据 |