层次查询(Hierarical Retrival)

  1 基本概念

    对于层次查询需要掌握：

    1.解释层次查询的基本概念，识别需求中需要用到层次查询的能力。
    2.建立和格式化一个树形报表(tree report)。
    3.修剪树形结构的节点(node)和枝(branches)。

    关键词：tree,root(根),node,leaf(叶子),branch(树枝，分支)

    本节例子来源于表s_emp,表结构和数据如下：

    看上面的表s_emp,使用层次查询，我们可以获得一张表基于层次关系的数据集合。Oracle是一种关系型数据库，在表中不可能以层次的关系存放数据。但是我们可以通过一定的规则，使用tree walking(树的遍历或树的查找)来获得层次关系的数据。Hierarical query 是一种获得树的层析关系报表的方法。

    树形结构的数据集合，存在于我们日常生活中的很多地方，比如考虑一个家族关系，有长辈，长辈下面有子女，子女下面还可以有子女，这转化为层次或等级关系就是：根节点只有一个，下面有子节点，子节点下面还有子节点，这样组成了一棵树。(有时候，根节点root 不一定只有一个，严格意义上说，这种情况不是一个严格的树)。

    当一种层次关系是存在一个表的行中，那么这种层次是可以获得的。例如，我们看s_emp表，对于title:VP，我们知道这些行中都包含manager_id=1,也就是说，这些行属于id=1的雇员的下属雇员，那么有title=vp又可以获得一系列的层次，而这些层次的跟则是id=1这个雇员。由此，得到一棵树形结构数据集合。

    层次树或等级树，在譬如家族关系，育种关系，组织管理，产品装配，人类进化，科学研究等领广泛应用。

    下面我们就根据s_emp这张表，根据职位大小来描述一个树形结构图。如图：

    只显示部分树形结构

    树形结构的父子关系，你可以控制：

    1. 遍历树的方向，是自上而下，还是自下而上。
    2. 确定层次的开始点(root)的位置。

    层次查询语句正是从这两个方面来确定的,start with确定开始点,connect by确定遍历的方向。
 

    2.层次查询

    2.1 语法：

    层次查询是通过start with和connect by 子句标识的。

    1.其中level关键字是可选的，表示等级，表示root,2 表示root 的child,其他相同的规则。

    2.From之后可以是table,view但是只能是一个table。

    3.Where条件限制了查询返回的行，但是不影响层次关系，不满足条件的节点不返回，但是这个不满足条件的节点的下层child不受影响。

    4.Start with是表示开始节点，对于一个真实的层次关系，必须要有这个子句，但是不是必须的，后面详细介绍。

    5.connect by prior 是指定父子关系，其中prior 的位置不一定要在connect by之后，对于一个真实的层次关系，这也是必须的。

    对于from是视图的，那么这个view不能包含join。

    2.2 遍历树

    Start with子句：

    首先必须确定startpoint，通过start with子句，后面加条件，这个条件是任何合法的条件表达式。

    Start with确定将哪行作为root，如果没有start with,则每行都当作root，然后查找其后代，这不是一个真实的查询。Start with后面可以使用子查询，如果有where条件，则会截断层次中的相关满足条件的节点，但是不影响整个层次结构。可以带多个条件。

    对于s_emp,从root title=president 开始，语句如下：

    select level,id,manager_id,last_name,title from s_emp
    start with manager_id is null
    connect by prior id=manager_id;

    这个语句与下面的语句等价，将start with 后面的条件换成子查询。

    select level,id,manager_id,last_name,title from s_emp
    start with title=(select title from s_emp where manager_id is null)
    connect by prior id=manager_id;

    注意：start with 和connect by prior不是ANSI标准sql。

    Connect by子句：

    Connect by与prior 确定一个层次查询的条件和遍历的方向(prior 确定)。
    Connect by prior column_1=column_2;(其中prior表示前一个节点的意思，可以在connect by 等号的前后，列之前，也可以放到select中的列之前)。

    Connect by也可以带多个条件，比如 connect by prior id=manager_id and id>10;

    方向：

    1. 自顶向下遍历：就是先由根节点，然后遍历子节点。column_1表示父key,column_2表示子key。即这种情况下：connect by prior 父key=子key表示自顶向下，等同于connect by 子key=prior 父key.

    例如：
    select level,id,manager_id,last_name, title from s_emp
    start with manager_id=2
    connect by id=prior manager_id;--自下而上遍历

    2. 自底向上遍历：就是先由最底层的子节点，遍历一直找到根节点。与上面的相反。

    Connect by 之后不能有子查询，但是可以加其他条件,比如加上and id !=2等。这句话则会截断树枝，如果id=2 的这个节点下面有很多子孙后代，则全部截断不显示。比如下面的句子：

    select level,id,manager_id,last_name,title from s_emp
    start with title=(select title from s_emp where manager_id is null)
    connect by prior id=manager_id and id!=2;

    不来不加上id!=2，共有25 条记录，现在加上这个条件只有9 条记录了，因为id=2 的后代包括自己共有16条记录，全部被截断。

    2.3 使用level和lpad格式化报表

    Level是层次查询的一个伪列，如果有level，必须有connect by,start with可以没有。Lpad是在一个string的左边添加一定长度的字符，并且满足中间的参数长度要求，不满足自动添加。例如现在的需求是，输出s_emp等级报表，root 节点的last_name 不变，比如第2 等级，也就是level=2的前面加两个’_’符号，level=3的前面加4个。这样我们可以得到一个公式就是：

    Lpad(last_name,length(last_name)+(level*2)-2,’_’)

    可以得出下面的语句：

    select level,id,manager_id,lpad(last_name,length(last_name)+(level*2)-2,'_'),title,prior
    last_name from s_emp
    start with manager_id is null
    connect by prior id=manager_id;

    select 中的prior last_name是输出其父亲的last_name.这个语句执行的结果如下：

    2.4 修剪branches

    上面已经提到，where子句会将节点删除，但是其后代不会受到影响，connect by 中加上条件会将满足条件的整个树枝包括后代都删除。要注意，如果是connect by 之后加条件正好条件选到根，那么结果和没有加一样，如图所示：

    2.5 层次查询限制

    1.层次查询from 之后如果是table，只能是一个table，不能有join。

    2.from之后如果是view，则view不能是带join的。

    3.使用order by子句，order 子句是在等级层次做完之后开始的，所以对于层次查询来说没有什么意义，除非特别关注level，获得某行在层次中的深度，但是这两种都会破坏层次。见3 增强特性中的使用siblings排序。

    4.在start with中表达式可以有子查询，但是connect by中不能有子查询。

    以上是10g之前的限制，10g之后可以使用带join的表和视图，connect by中可以使用子查询。

    2.6 应用

    1）查询每个等级上节点的数目

    先查看总共有几个等级：

    select count(distinct level)
    from s_emp
    start with manager_id is null
    connect by prior id=manager_id;

    要查看每个等级上有多少个节点，只要按等级分组，并统计节点的数目即可，可以这样写：

    select level,count(last_name)
    from s_emp
    start with manager_id is null
    connect by prior id=manager_id
    group by level;

    2）格式化报表

    见2.3.

    3）查看等级关系

    有一个常见的需求，比如给定一个具体的emp 看是否对某个emp 有管理权，也就是从给定的节点寻找，看其子树节点中能否找到这个节点。如果找到，返回，找不到，no rows returned.

    比如对于s_emp表，从根节点，也就是manager_id is null的开始找，看first_name=’ Elena’是否被它管理，语句如下：

    select level,a.* from
    s_emp a
    where first_name='Elena' –被管理的节点
    start with manager_id is null –开始节点
    connect by prior id=manager_id;

    4）删除子树

    比如有这样的需求，现在要裁员，将某个部门的员工包括经理全部裁掉，那么可以使用树形查询作为子查询实现这个功能。将id为2 的员工管理的所有员工包括自己删除。因为要全部裁掉了。那么语句如下：

    delete from s_emp where id in(
    select id from
    s_emp a
    start with id=2 –从id=2的员工开始查找其子节点，把整棵树删除
    connect by prior id=manager_id);

    5）找出每个部门的经理

    这个需求，我们可以从树中查找，也就是对于每个部门选最高等级节点。可以使用connect by后加条件过滤branches 的方法。

    select level,a.* from
    s_emp a
    start with manager_id is null
    connect by prior id=manager_id and dept_id !=prior dept_id;--当前行的dept_id不等于前一行的dept_id，即每个子树中选最高等级节点

    6）查询一个组织中最高的几个等级

    用where level条件过滤

    select level,a.* from
    s_emp a
    where level <=2 –查找前两个等级
    start with manager_id is null
    connect by prior id=manager_id and dept_id !=prior dept_id;

    7）合计层次

    有两个需求，一是对一个指定的子树subtree 做累加计算salary，一是将每行都作为root节点，然后对属于这个节点的所有字节点累加计算salary。

    第一种很简单，求下sum就可以了，语句：

    select sum(salary) from
    s_emp a
    start with id=2—比如从id=2开始
    connect by prior id=manager_id;

    第2 个需求，需要用到第1 个，对每个root 节点求这个树的累加值，然后内部层次查询的开始节点从外层查询获得。

    select last_name,salary,(
    select sum(salary) from
    s_emp
    start with id=a.id --让每个节点都成为root
    connect by prior id=manager_id) sumsalary
    from s_emp a;

    8）找出指定层次中的叶子节点

    Leaf(叶子)就是没有子孙的孤立节点。Oracle 10g提供了一个简单的connect_by_isleaf=1,0 表示非叶子节点
    select level,id,manager_id,last_name, title from s_emp
    where connect_by_isleaf=1 –表示查询叶子节点
    start with manager_id=2
    connect by prior id=manager_id;

    也可以通过连接查询获得，方法有多种，叶子节点肯定是level最大的节点。

    select a.lev,b.id from (
    select max(level) lev from s_emp --查询最大的level
    start with manager_id=2
connect by prior id=manager_id) a,
        (select level lev,id
    from s_emp
    start with manager_id=2
    connect by prior id=manager_id) b
    where a.lev=b.lev;

    注意：level不可以前面加表名

    其他：

    Connect by 与rownum的联合使用，比如给定两个日期，查询中间所有的日期，按月递增：

    SELECT to_date('2008-10-1', 'YYYY-MM-DD') + ROWNUM - 1
    FROM dual
    CONNECT BY rownum <= to_date('2008-10-5', 'YYYY-MM-DD') -
    to_date('2008-10-1', 'YYYY-MM-DD') + 1;
    获取01到99
    select case when length(rownum)=1 then to_char('0')||rownum else to_char(rownum) end
    from dual
    connect by rownum<=99;
    select lpad(rownum,2,'0') from dual connect by rownum<=99;


 

    3 增强特性

    3.1 SYS_CONNECT_BY_PATH

    Oracle 9i提供了sys_connect_by_path(column,char),其中column 是字符型或能自动转换成字符型的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。这个函数只能使用在层次查询中。

    例如，要求将s_emp表中的层次关系展现出来，并且将last_name 按照’=>’展现。如root,则是=>root_last_name, level=2的就是=>root_last_name=>level_2_last_name,并且利用lpad格式化报表。语句是：

    select last_name,
    level,
    id,
   lpad(' ', level * 2 - 1) || sys_connect_by_path(last_name, '=>') –前面按层次加空格，--并且后面加上路径
    from s_emp
    start with manager_id is null
    connect by prior id = manager_id;

    结果如图所示：

    下面的是oracle10g新增特性

    3.2 CONNECT_BY_ISLEAF

    在oracle9i的时候，查找指定root 下的叶子节点，是很复杂的，oracle10g引入了一个新的函数，connect_by_isleaf,如果行的值为0 表示不是叶子节点，1表示是叶子节点。

    找出s_emp 中找出manager_id=2 开始的行为root，表示叶子节点和非叶子节点，那么语句如下：

    select level,
    id,
    manager_id,
    last_name,
    title,
    (case --使用case表达式判断是否是叶子节点
    when connect_by_isleaf = 1 then
    '叶子'
    else
    '不是叶子'
    end) isleaf
    from s_emp
    start with manager_id = 2
    connect by prior id = manager_id;

    3.3 CONNECT_BY_ISCYCLE和NOCYCLE关键字

    如果从root 节点开始找其子孙，找到一行，结果发生和祖先互为子孙的情况，则发生循环，oracle会报ORA-01436: CONNECT BY loop in user data，在9i 中只能将发生死循环的不加入到树中或删除，在10g中可以用nocycle 关键字加在connect by之后，避免循环的参加查询操作。并且通过connect_by_iscycle得到哪个节点发生循环。0表示未发生循环，1表示发生了循环，如：

    create table family1(
    fatherid number,
    childid number
    );
    insert into family1 values(null,1);
    insert into family1 values(1,2);--父节点为1
    insert into family1 values(1,3);
    insert into family1 values(2,4);--发生循环
    insert into family1 values(4,1);--子节点为1
    insert into family1 values(4,5);
    commit;
    select connect_by_iscycle, fatherid,childid,sys_connect_by_path(childid,'/')
    from family1
    start with fatherid is null
    connect by nocycle prior childid=fatherid;

    结果是：

    3.4 CONNECT_BY_ROOT

    Oracle10g新增connect_by_root,用在列名之前表示此行的根节点的相同列名的值。如：

    select connect_by_root last_name root_last_name, connect_by_root id root_id,
    id,last_name,manager_id
    from s_emp
    start with manager_id is null
    connect by prior id=manager_id;

    结果为：

    3.5 使用SIBLINGS关键字排序

    前面说了，对于层次查询如果用order by排序，比如order by last_name则是先做完层次获得level,然后按last_name 排序，这样破坏了层次，比如特别关注某行的深度，按level排序，也是会破坏层次的。

    在oracle10g中，增加了siblings 关键字的排序。

    语法：order siblings by <expre>

    它会保护层次，并且在每个等级中按expre排序。

    select level,
    id,last_name,manager_id
    from s_emp
    start with manager_id is null
    connect by prior id=manager_id
    order siblings by last_name;

    结果如图：

    【技术开发 技术文章】

    1 基本概念

    对于层次查询需要掌握：

    1.解释层次查询的基本概念，识别需求中需要用到层次查询的能力。
    2.建立和格式化一个树形报表(tree report)。
    3.修剪树形结构的节点(node)和枝(branches)。

    关键词：tree,root(根),node,leaf(叶子),branch(树枝，分支)

    本节例子来源于表s_emp,表结构和数据如下：

    看上面的表s_emp,使用层次查询，我们可以获得一张表基于层次关系的数据集合。Oracle是一种关系型数据库，在表中不可能以层次的关系存放数据。但是我们可以通过一定的规则，使用tree walking(树的遍历或树的查找)来获得层次关系的数据。Hierarical query 是一种获得树的层析关系报表的方法。

    树形结构的数据集合，存在于我们日常生活中的很多地方，比如考虑一个家族关系，有长辈，长辈下面有子女，子女下面还可以有子女，这转化为层次或等级关系就是：根节点只有一个，下面有子节点，子节点下面还有子节点，这样组成了一棵树。(有时候，根节点root 不一定只有一个，严格意义上说，这种情况不是一个严格的树)。

    当一种层次关系是存在一个表的行中，那么这种层次是可以获得的。例如，我们看s_emp表，对于title:VP，我们知道这些行中都包含manager_id=1,也就是说，这些行属于id=1的雇员的下属雇员，那么有title=vp又可以获得一系列的层次，而这些层次的跟则是id=1这个雇员。由此，得到一棵树形结构数据集合。

    层次树或等级树，在譬如家族关系，育种关系，组织管理，产品装配，人类进化，科学研究等领广泛应用。

    下面我们就根据s_emp这张表，根据职位大小来描述一个树形结构图。如图：

    只显示部分树形结构

    树形结构的父子关系，你可以控制：

    1. 遍历树的方向，是自上而下，还是自下而上。
    2. 确定层次的开始点(root)的位置。

    层次查询语句正是从这两个方面来确定的,start with确定开始点,connect by确定遍历的方向。
 

 

    2.层次查询

    2.1 语法：

    层次查询是通过start with和connect by 子句标识的。

    1.其中level关键字是可选的，表示等级，表示root,2 表示root 的child,其他相同的规则。

    2.From之后可以是table,view但是只能是一个table。

    3.Where条件限制了查询返回的行，但是不影响层次关系，不满足条件的节点不返回，但是这个不满足条件的节点的下层child不受影响。

    4.Start with是表示开始节点，对于一个真实的层次关系，必须要有这个子句，但是不是必须的，后面详细介绍。

    5.connect by prior 是指定父子关系，其中prior 的位置不一定要在connect by之后，对于一个真实的层次关系，这也是必须的。

    对于from是视图的，那么这个view不能包含join。

    2.2 遍历树

    Start with子句：

    首先必须确定startpoint，通过start with子句，后面加条件，这个条件是任何合法的条件表达式。

    Start with确定将哪行作为root，如果没有start with,则每行都当作root，然后查找其后代，这不是一个真实的查询。Start with后面可以使用子查询，如果有where条件，则会截断层次中的相关满足条件的节点，但是不影响整个层次结构。可以带多个条件。

    对于s_emp,从root title=president 开始，语句如下：

    select level,id,manager_id,last_name,title from s_emp
    start with manager_id is null
    connect by prior id=manager_id;

    这个语句与下面的语句等价，将start with 后面的条件换成子查询。

    select level,id,manager_id,last_name,title from s_emp
    start with title=(select title from s_emp where manager_id is null)
    connect by prior id=manager_id;

    注意：start with 和connect by prior不是ANSI标准sql。

    Connect by子句：

    Connect by与prior 确定一个层次查询的条件和遍历的方向(prior 确定)。
    Connect by prior column_1=column_2;(其中prior表示前一个节点的意思，可以在connect by 等号的前后，列之前，也可以放到select中的列之前)。

    Connect by也可以带多个条件，比如 connect by prior id=manager_id and id>10;

    方向：

    1. 自顶向下遍历：就是先由根节点，然后遍历子节点。column_1表示父key,column_2表示子key。即这种情况下：connect by prior 父key=子key表示自顶向下，等同于connect by 子key=prior 父key.

    例如：
    select level,id,manager_id,last_name, title from s_emp
    start with manager_id=2
    connect by id=prior manager_id;--自下而上遍历

    2. 自底向上遍历：就是先由最底层的子节点，遍历一直找到根节点。与上面的相反。

    Connect by 之后不能有子查询，但是可以加其他条件,比如加上and id !=2等。这句话则会截断树枝，如果id=2 的这个节点下面有很多子孙后代，则全部截断不显示。比如下面的句子：

    select level,id,manager_id,last_name,title from s_emp
    start with title=(select title from s_emp where manager_id is null)
    connect by prior id=manager_id and id!=2;

    不来不加上id!=2，共有25 条记录，现在加上这个条件只有9 条记录了，因为id=2 的后代包括自己共有16条记录，全部被截断。

    2.3 使用level和lpad格式化报表

    Level是层次查询的一个伪列，如果有level，必须有connect by,start with可以没有。Lpad是在一个string的左边添加一定长度的字符，并且满足中间的参数长度要求，不满足自动添加。例如现在的需求是，输出s_emp等级报表，root 节点的last_name 不变，比如第2 等级，也就是level=2的前面加两个’_’符号，level=3的前面加4个。这样我们可以得到一个公式就是：

    Lpad(last_name,length(last_name)+(level*2)-2,’_’)

    可以得出下面的语句：

    select level,id,manager_id,lpad(last_name,length(last_name)+(level*2)-2,'_'),title,prior
    last_name from s_emp
    start with manager_id is null
    connect by prior id=manager_id;

    select 中的prior last_name是输出其父亲的last_name.这个语句执行的结果如下：

    2.4 修剪branches

    上面已经提到，where子句会将节点删除，但是其后代不会受到影响，connect by 中加上条件会将满足条件的整个树枝包括后代都删除。要注意，如果是connect by 之后加条件正好条件选到根，那么结果和没有加一样，如图所示：

    2.5 层次查询限制

    1.层次查询from 之后如果是table，只能是一个table，不能有join。

    2.from之后如果是view，则view不能是带join的。

    3.使用order by子句，order 子句是在等级层次做完之后开始的，所以对于层次查询来说没有什么意义，除非特别关注level，获得某行在层次中的深度，但是这两种都会破坏层次。见3 增强特性中的使用siblings排序。

    4.在start with中表达式可以有子查询，但是connect by中不能有子查询。

    以上是10g之前的限制，10g之后可以使用带join的表和视图，connect by中可以使用子查询。

    2.6 应用

    1）查询每个等级上节点的数目

    先查看总共有几个等级：

    select count(distinct level)
    from s_emp
    start with manager_id is null
    connect by prior id=manager_id;

    要查看每个等级上有多少个节点，只要按等级分组，并统计节点的数目即可，可以这样写：

    select level,count(last_name)
    from s_emp
    start with manager_id is null
    connect by prior id=manager_id
    group by level;

    2）格式化报表

    见2.3.

    3）查看等级关系

    有一个常见的需求，比如给定一个具体的emp 看是否对某个emp 有管理权，也就是从给定的节点寻找，看其子树节点中能否找到这个节点。如果找到，返回，找不到，no rows returned.

    比如对于s_emp表，从根节点，也就是manager_id is null的开始找，看first_name=’ Elena’是否被它管理，语句如下：

    select level,a.* from
    s_emp a
    where first_name='Elena' –被管理的节点
    start with manager_id is null –开始节点
    connect by prior id=manager_id;

    4）删除子树

    比如有这样的需求，现在要裁员，将某个部门的员工包括经理全部裁掉，那么可以使用树形查询作为子查询实现这个功能。将id为2 的员工管理的所有员工包括自己删除。因为要全部裁掉了。那么语句如下：

    delete from s_emp where id in(
    select id from
    s_emp a
    start with id=2 –从id=2的员工开始查找其子节点，把整棵树删除
    connect by prior id=manager_id);

    5）找出每个部门的经理

    这个需求，我们可以从树中查找，也就是对于每个部门选最高等级节点。可以使用connect by后加条件过滤branches 的方法。

    select level,a.* from
    s_emp a
    start with manager_id is null
    connect by prior id=manager_id and dept_id !=prior dept_id;--当前行的dept_id不等于前一行的dept_id，即每个子树中选最高等级节点

    6）查询一个组织中最高的几个等级

    用where level条件过滤

    select level,a.* from
    s_emp a
    where level <=2 –查找前两个等级
    start with manager_id is null
    connect by prior id=manager_id and dept_id !=prior dept_id;

    7）合计层次

    有两个需求，一是对一个指定的子树subtree 做累加计算salary，一是将每行都作为root节点，然后对属于这个节点的所有字节点累加计算salary。

    第一种很简单，求下sum就可以了，语句：

    select sum(salary) from
    s_emp a
    start with id=2—比如从id=2开始
    connect by prior id=manager_id;

    第2 个需求，需要用到第1 个，对每个root 节点求这个树的累加值，然后内部层次查询的开始节点从外层查询获得。

    select last_name,salary,(
    select sum(salary) from
    s_emp
    start with id=a.id --让每个节点都成为root
    connect by prior id=manager_id) sumsalary
    from s_emp a;

    8）找出指定层次中的叶子节点

    Leaf(叶子)就是没有子孙的孤立节点。Oracle 10g提供了一个简单的connect_by_isleaf=1,0 表示非叶子节点
    select level,id,manager_id,last_name, title from s_emp
    where connect_by_isleaf=1 –表示查询叶子节点
    start with manager_id=2
    connect by prior id=manager_id;

    也可以通过连接查询获得，方法有多种，叶子节点肯定是level最大的节点。

    select a.lev,b.id from (
    select max(level) lev from s_emp --查询最大的level
    start with manager_id=2
connect by prior id=manager_id) a,
        (select level lev,id
    from s_emp
    start with manager_id=2
    connect by prior id=manager_id) b
    where a.lev=b.lev;

    注意：level不可以前面加表名

    其他：

    Connect by 与rownum的联合使用，比如给定两个日期，查询中间所有的日期，按月递增：

    SELECT to_date('2008-10-1', 'YYYY-MM-DD') + ROWNUM - 1
    FROM dual
    CONNECT BY rownum <= to_date('2008-10-5', 'YYYY-MM-DD') -
    to_date('2008-10-1', 'YYYY-MM-DD') + 1;
    获取01到99
    select case when length(rownum)=1 then to_char('0')||rownum else to_char(rownum) end
    from dual
    connect by rownum<=99;
    select lpad(rownum,2,'0') from dual connect by rownum<=99;


 

 

    3 增强特性

    3.1 SYS_CONNECT_BY_PATH

    Oracle 9i提供了sys_connect_by_path(column,char),其中column 是字符型或能自动转换成字符型的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。这个函数只能使用在层次查询中。

    例如，要求将s_emp表中的层次关系展现出来，并且将last_name 按照’=>’展现。如root,则是=>root_last_name, level=2的就是=>root_last_name=>level_2_last_name,并且利用lpad格式化报表。语句是：

    select last_name,
    level,
    id,
   lpad(' ', level * 2 - 1) || sys_connect_by_path(last_name, '=>') –前面按层次加空格，--并且后面加上路径
    from s_emp
    start with manager_id is null
    connect by prior id = manager_id;

    结果如图所示：

    下面的是oracle10g新增特性

    3.2 CONNECT_BY_ISLEAF

    在oracle9i的时候，查找指定root 下的叶子节点，是很复杂的，oracle10g引入了一个新的函数，connect_by_isleaf,如果行的值为0 表示不是叶子节点，1表示是叶子节点。

    找出s_emp 中找出manager_id=2 开始的行为root，表示叶子节点和非叶子节点，那么语句如下：

    select level,
    id,
    manager_id,
    last_name,
    title,
    (case --使用case表达式判断是否是叶子节点
    when connect_by_isleaf = 1 then
    '叶子'
    else
    '不是叶子'
    end) isleaf
    from s_emp
    start with manager_id = 2
    connect by prior id = manager_id;

    3.3 CONNECT_BY_ISCYCLE和NOCYCLE关键字

    如果从root 节点开始找其子孙，找到一行，结果发生和祖先互为子孙的情况，则发生循环，oracle会报ORA-01436: CONNECT BY loop in user data，在9i 中只能将发生死循环的不加入到树中或删除，在10g中可以用nocycle 关键字加在connect by之后，避免循环的参加查询操作。并且通过connect_by_iscycle得到哪个节点发生循环。0表示未发生循环，1表示发生了循环，如：

    create table family1(
    fatherid number,
    childid number
    );
    insert into family1 values(null,1);
    insert into family1 values(1,2);--父节点为1
    insert into family1 values(1,3);
    insert into family1 values(2,4);--发生循环
    insert into family1 values(4,1);--子节点为1
    insert into family1 values(4,5);
    commit;
    select connect_by_iscycle, fatherid,childid,sys_connect_by_path(childid,'/')
    from family1
    start with fatherid is null
    connect by nocycle prior childid=fatherid;

    结果是：

    3.4 CONNECT_BY_ROOT

    Oracle10g新增connect_by_root,用在列名之前表示此行的根节点的相同列名的值。如：

    select connect_by_root last_name root_last_name, connect_by_root id root_id,
    id,last_name,manager_id
    from s_emp
    start with manager_id is null
    connect by prior id=manager_id;

    结果为：

    3.5 使用SIBLINGS关键字排序

    前面说了，对于层次查询如果用order by排序，比如order by last_name则是先做完层次获得level,然后按last_name 排序，这样破坏了层次，比如特别关注某行的深度，按level排序，也是会破坏层次的。

    在oracle10g中，增加了siblings 关键字的排序。

    语法：order siblings by <expre>

    它会保护层次，并且在每个等级中按expre排序。

    select level,
    id,last_name,manager_id
    from s_emp
    start with manager_id is null
    connect by prior id=manager_id
    order siblings by last_name;

    结果如图：