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;

http://s7/bmiddle/4b62e4a906de97549b496<wbr>树查询 <wbr>level用法" TITLE="oracle <wbr>树查询 <wbr>level用法" STYLE="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; list-style-type: none; list-style-position: initial; list-style-image: initial;" />

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

http://s1/bmiddle/4b62e4a944b1e94f13ec0<wbr>树查询 <wbr>level用法" TITLE="oracle <wbr>树查询 <wbr>level用法" STYLE="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; list-style-type: none; list-style-position: initial; list-style-image: initial;" />

显示出树的级别查询

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

http://s11/bmiddle/4b62e4a92aef31d10447a<wbr>树查询 <wbr>level用法" TITLE="oracle <wbr>树查询 <wbr>level用法" STYLE="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; list-style-type: none; list-style-position: initial; list-style-image: initial;" />

参阅：

http://blog.ccidnet.com/blog.php?do=showone&uid=41414&type=blog&itemid=207639

说明：我们的树状属性一般都是在一条记录中记录一个当前节点的ID和这个节点的父ID来实现。但是一旦数据中出现了循环记录，如两个节点互为对方的父结点，系统就会报 ORA-01436错误(ORA-01436: 用户数据中的CONNECT BY 循环)