SYS_CONNECT_BY_PATH函数的用法

• 1、脚本

   
  1.create table T_TREE(  
  2.  S_ID   NUMBER,  
  3.  F_ID   NUMBER,  
  4.  S_NAME VARCHAR2(20)  
  5.);  
  6.comment on column T_TREE.S_ID  
  7.  is '本节点ID';  
  8.comment on column T_TREE.F_ID  
  9.  is '父节点ID';  
  10.comment on column T_TREE.S_NAME  
  11.  is '本节点名称';  
  12.insert into t_tree values(1,null,'root');  
  13.insert into t_tree values(2,1,'node-010');  
  14.insert into t_tree values(3,1,'node-020');  
  15.insert into t_tree values(4,3,'node-011');  
  16.insert into t_tree values(5,4,'node-011');  
  17.commit; 
  create table T_TREE(
    S_ID   NUMBER,
    F_ID   NUMBER,
    S_NAME VARCHAR2(20)
  );
  comment on column T_TREE.S_ID
    is '本节点ID';
  comment on column T_TREE.F_ID
    is '父节点ID';
  comment on column T_TREE.S_NAME
    is '本节点名称';
  insert into t_tree values(1,null,'root');
  insert into t_tree values(2,1,'node-010');
  insert into t_tree values(3,1,'node-020');
  insert into t_tree values(4,3,'node-011');
  insert into t_tree values(5,4,'node-011');
  commit; 2、用SYS_CONNECT_BY_PATH执行SQL语句

   
  1.SELECT CONNECT_BY_ISLEAF ISLEAF,  
  2.       S_NAME,  
  3.       LTRIM(SYS_CONNECT_BY_PATH(S_NAME, '$'), '$') PATH  
  4.  FROM T_TREE  
  5.CONNECT BY PRIOR S_ID = F_ID  
  6. START WITH F_ID IS NULL; 
  SELECT CONNECT_BY_ISLEAF ISLEAF,
         S_NAME,
         LTRIM(SYS_CONNECT_BY_PATH(S_NAME, '$'), '$') PATH
    FROM T_TREE
  CONNECT BY PRIOR S_ID = F_ID
   START WITH F_ID IS NULL;

  其实SYS_CONNECT_BY_PATH这个函数是oracle9i才新提出来的！它一定要和connect by子句合用！

  第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符！
  START WITH 代表你要开始遍历的的节点！CONNECT BY PRIOR 是标示父子关系的对应！

  其中CONNECT_BY_ISLEAF ISLEAF 是判断数据是否是叶节点，如果是的话 值是1，还有子节点的话值是0；

  3、查询结果