ITPub博客

首页 > 数据库 > Oracle > oracle中 connect by prior 递归算法

oracle中 connect by prior 递归算法

Oracle 作者:thinklys 时间:2013-10-26 15:44:17 0 删除 编辑

oracle中 connect by prior 递归算法

 

Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的,其基本语法是:

select ... from tablename start with 条件1
connect by 条件2
where 条件3;
例:
select * from table
start with org_id = 'HBHqfWGWPy'
connect by prior org_id = parent_id;

实例:

-- Create table                                         
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;                                                
Commit complete                                         
                                                        
SQL> SELECT * FROM DEP;                                 
                                   
      DEPID DEPNAME                        UPPERDEPID
----------- ---------------------------------------------
          0 General Deparment        
          1 Development                        0
          2 QA                                 0
          3 Server Development                 1
          4 Client Development                 1
          5 TA                                 2
          6 Porject QA                         2
                                              
7 rows selected                               
                                                          
                                                          
SQL>                                                      
SELECT RPAD( ' ', 2*(LEVEL-1), '-' ) || DEPNAME "DEPNAME",
 CONNECT_BY_ROOT DEPNAME "ROOT",                          
SELECONNECT_BY_ISLEAF "ISLEAF",                           
CT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10;              
 LEVEL ,                                                   
 CONSYS_CONNECT_BY_PATH(DEPNAME, '/') "PATH"              
 CONFROM DEP                                              
 LEVSTART WITH UPPERDEPID is null                         
 SYSCONNECT BY   PRIOR DEPID = UPPERDEPID;
                

 

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

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21742472/viewspace-1117540/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-05-25