oracle中的递归查询语句 - IT-风

oracle中的递归查询语句

　　创建一个demo表 department：

create table department (
 code VARCHAR2(10) not null,
 name VARCHAR2(50),
 pcode VARCHAR2(10),
 mgrname VARCHAR2(50)
);
insert into department values(\'ROOT\',\'*部门\', null, \'R大大\');
insert into department values(\'A\',\'A部门\', \'ROOT\', \'A老大\');
insert into department values(\'A01\',\'A01部门\', \'A\', \'A01老大\');
insert into department values(\'A02\',\'A02部门\', \'A\', \'A02老大\');
insert into department values(\'B\',\'B部门\', \'ROOT\', \'B老大\');
insert into department values(\'B01\',\'B01部门\', \'B\', \'B01老大\');
insert into department values(\'B02\',\'B02部门\', \'B\', \'B02老大\');

1.递归查询某个部门的所有子级部门：　

select t.* from department t start with t.code=\'ROOT\' connect by prior t.code = t.pcode ;

　　查询结果：

　　

 

 2.递归查询某个部门的所有上级部门：

select t.* from department t start with t.code=\'B02\' connect by t.code = prior t.pcode ;

　　查询结果：