oracle 函数connect by及一些属性（PRIOR 、lpad、rpad、concatchar、concat.....）

ORACLE 伪列（level）、rpad/lpad、connectby、父节点子节点问题送分系列（一）

这一系列问题连起来LZ通通弄不明白，问题太多，干脆组成送分系列求助了。
LZ非计算机系毕业，对树形结构从未了解过，麻烦大家用通俗易懂的语言讲解下，太专业了LZ理解起来很吃力。

首先用网上的这个例子问一下level和rpad/lpad的问题
 
创建一个部门表，这个表有三个字段，分别对应部门ID，部门名称，以及上级部门ID
建表：
create table DEP 
( 
  DEPID      number(10) not null, 
  DEPNAME    varchar2(256), 
  UPPERDEPID number(10) 
);
初始化数据：
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (0, '总经办', null); 
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (1, '开发部', 0); 
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (2, '测试部', 0);  
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (3, 'Sever开发部', 1); 
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (4, 'Client开发部', 1); 
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (5, 'TA测试部', 2);  
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (6, '项目测试部', 2); 
commit;
 
根据“CONNECT BY”来实现树状查询结果

SQL> SELECT RPAD( ' ', 2*(LEVEL-1), '-' ) || DEPNAME "DEPNAME",

CONNECT_BY_ROOT DEPNAME "ROOT",

CONNECT_BY_ISLEAF "ISLEAF",

LEVEL ,

SYS_CONNECT_BY_PATH(DEPNAME, '/') "PATH"

FROM DEP

START WITH UPPERDEPID IS NULL

CONNECT BY PRIOR DEPID = UPPERDEPID;
   
1.上面这段SQL第一行，RPAD和LEVEL结合起来我就不是很懂了

RPAD的意思应该是在右边填充，按照我的理解应该是从右边的空格处开始填充符号“-”，
长度为2*（LEVEL-1），LEVEL根据网上讲解好像是每层的意思，从哪里开始算一层呢？ 这个结合起来用的例子还很多，都是到这里就看不懂，请形象的解释一下（不懂树形结构，麻烦大虾们尽量用新手能懂的语句（例子）解释） （50分）

------------------------------------------------------------------
------------------------------------------------------------------
补充部分：

在例子下方的说明里面： 
1. CONNECT_BY_ROOT 返回当前节点的最顶端节点 
2. CONNECT_BY_ISLEAF 
判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点 
3. LEVEL 伪列表示节点深度 
4. 
SYS_CONNECT_BY_PATH函数显示详细路径，并用“/”分隔

第一条：返回当前节点的最顶端节点。
第二条：叶子节点，如果这个节点下面有子节点，则不为叶子节点。

麻烦结合这个例子说一下节点，最顶端节点..都是什么（50分）

叶子节点和非叶子节点？ 看他的这个说法，我的理解是最末层的节点就是所谓的叶子节点？那么最末层是不是对应的level最深的地方？level值最大？（50分）

2013-08-08 23:14

提问者采纳

LZ你好

这个rpad是右边填充的意思，但是填充的是

但这个RPAD( ' ', 2*(LEVEL-1), '-' )意思是在空格右侧填充2*(LEVEL-1)个长度的横杠，这个你理解的好像没问题

 

level开始是从start with开始算起的，你这里就是START WITH UPPERDEPID IS NULL，因为根据父ID，你这个为空的就是最顶层，这个你也可以改成

START WITH UPPERDEPID =0 看下效果

 

树形结构的意思，我简单给你说一下

 

家里有爷爷，爷爷有两个儿子，每个儿子又有两个儿子

那么画图出来就是这样

 

 CONNECT_BY_ROOT 从你start with的开始算起，最顶端结点就是总经办，这个你看下查询结果就能看出来

 

CONNECT_BY_ISLEAF 如果下边还有子节点，就为0，无子节点了就是1，按照我给你发的图，也就是四个孙子下边都没节点了，他们最后都显示1，上边爷爷和儿子都显示0

 

level，就是爷爷是最顶端，为1，依次类推

 

给你举例子的图画的难看了点哈，见笑了

 

晕，你补充了好多啊，你先看吧，哪不懂你再问，我再给你解释

追问：

你的第一段我懂了，RPAD那个我也基本懂了。
从第二段开始问，你的原文是“level...看下效果”这一段

这句话我理解就是start with的那一层就是你所说的最顶层，根据最后的结果来看应该就是level=1的那层。 但是你说到父ID，这个跳的快了点，我不明白什么是父ID。
另外：补充的可以不急着回答，我现在只给了50分，补充里面有100分，先把正文的解决掉，大不了补充的我弄成送分系列2,3...

追答：

就拿我画那个图说
爷爷是父亲的父ID,父亲是儿子的父ID
这个很容易理解的吧，爷爷是父亲的父亲，父亲是儿子的父亲
比如爷爷的ID是1，大儿子ID是2，二儿子ID是3，大儿子和二儿子的爹都是爷爷，也就是父ID都是1
孙子那个也是这个意思
 
还有，答你题不为分，只是想给你讲明白，即使没悬赏我估计我也会答你的

追问：

你那个图倒是很好理解，但是结合这段代码来说，父ID是指的什么？是指的顶层的哪一项？ 这个我不是很明白。

追答：

对于你的数据，你看你首列和最后一列
第一列代表各个部门的ID，最后一列就是其所对应的父ID
总经办的ID是0，无父ID
开发部的ID是1，对应的父ID是0，也就是总经办
测试部同开发部
Server开发部的ID是3，父ID是1，也就是开发部
Client开发部同Server开发部
TA测试部的ID是5，父ID是2，也就是测试部
项目测试部同TA测试部

看了上面的问题，自己测了一下

select connect_by_root(t11.BDGT_ID),t11.*
from mt_bdg_bdgt t11 
where t11.BDGT_TYPE = '0' and t11.BDGT_ID = '2111104'
start with t11.PARENT_ID is null
connect by prior t11.BDGT_ID = t11.PARENT_ID and prior t11.BDGT_TYPE = t11.BDGT_TYPE ;

connect_by_root是查找根节点    
start with 是以哪个字段为根节点
prior 是哪个字段遍历点（例：t11.BDGT_ID)

select connect_by_isleaf   ISLEAF,level,LPAD(' ', 2 * LEVEL - 1) ||t.BDGT_NAME as BDGT_NAME,t.* from mt_bdg_bdgt t start with t.PARENT_ID is null
connect by prior t.bdgt_id = t.PARENT_ID  

修改：
Lpad Function：在PL/SQL中用于往源字符串的左侧填充一些字符。
函数参数：lpad( string1, padded_length, [ pad_string ] )
其中
string1：源字符串
padded_length:最终返回的字符串的长度，如果最终返回的字符串的长度比源字符串的小，那么此函数实际上对源串进行截断处理
pad_string:用于填充的字符，可以不填，默认为空字符
下面是几个实例：
eg：

       
       

        
        

         
         

          
          [sql] 
          
          view plain
          
           copy
          
          

          
          
         
         
        
        
        
        
--返回值长度大于源字符串长度，将进行填充  
   select lpad('zhanglt',10,'z') from dual;  
   结果为：zzzzhanglt  
   --返回值长度小于源字符串长度，将进行截断  
  select lpad('zhanglt',3,'z') from dual;  
       
       
默认情况下将用空格进行填充。
Rpad函数的用法与lpad基本一致，只不过填充方向在左边，需要注意的一点是如果长度小于源串，那么仍然进行截断，并且截断时从左边第一个字符开始。
自己的理解：
在属性
BDGT_NAME左侧添加 空格，和起来总长度为3 * (LEVEL - 1)
测试：
SELECT LEVEL,
           lpad(A.BDGT_NAME,3 * (LEVEL - 1)) AS tree_id,UUID,connect_by_isleaf as leaf,
           BDGT_ID,rownum as tree_rn
    FROM MT_BDG_BDGT A 
    WHERE  BDGT_TYPE='2'
    START WITH A.PARENT_ID is null 
    CONNECT BY PRIOR A.BDGT_ID = A.PARENT_ID and PRIOR a.BDGT_TYPE = a.BDGT_TYPE and PRIOR A.BDGT_YEAR = A.BDGT_YEAR
    SELECT LEVEL,
           lpad('  ',6*3 * (LEVEL - 1)+1,'&'||'nbsp;') || A.BDGT_NAME AS tree_id,UUID,connect_by_isleaf as leaf,
           BDGT_ID,rownum as tree_rn
    FROM MT_BDG_BDGT A 
    WHERE  BDGT_TYPE='2'
    START WITH A.PARENT_ID is null 
    CONNECT BY PRIOR A.BDGT_ID = A.PARENT_ID and PRIOR a.BDGT_TYPE = a.BDGT_TYPE and PRIOR A.BDGT_YEAR = A.BDGT_YEAR
    
concatchar 和 concat区别：
CONCAT('AA','BB') 连接AA，BB字符串

concatchar(chr(38)||'nbsp;',3 * (LEVEL - 1)) || A.BDGT_NAME    根据数据树等级添加3*(Level-1)个          concatchar 不能用于表，可用于视图。
以下两种方式都可以实现：
 SELECT LEVEL,
           lpad(' ',3 * (LEVEL - 1)) || T11.fname as name,connect_by_isleaf as leaf from MT_BDG_OUTLAYTYPE T11
start with   T11.Parent_Id='003'
connect by prior T11.fcode = T11.Parent_Id;


SELECT LEVEL,
           concatchar(chr(38)||'nbsp;',3 * (LEVEL - 1)) || A.BDGT_NAME AS tree_id,UUID,connect_by_isleaf as leaf,
           BDGT_ID,rownum as tree_rn
    FROM MT_BDG_BDGT A 
    WHERE  BDGT_TYPE='2'
    START WITH A.PARENT_ID is null 
    CONNECT BY PRIOR A.BDGT_ID = A.PARENT_ID and PRIOR a.BDGT_TYPE = a.BDGT_TYPE and PRIOR A.BDGT_YEAR = A.BDGT_YEAR
        

concatchar使用：
将  3 * (LEVEL - 1)  个  chr(38)||'nbsp;'  串接

CONCAT( 'AA' , 'BB' )