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函数显示详细路径，并用“/”分隔