Oracle的递归

create table t2(
		　　t_id number,
		　  t_root_id number,
		　　t_name varchar(5),
		　　t_description varchar(10)
		 );  

Truncate Table T2;

insert into t2(t_root_id,t_id,t_name,t_description) values(0,1,'a','aaa'); 
insert into t2(t_root_id,t_id,t_name,t_description) values(1,2,'a1','aaa1');
insert into t2(t_root_id,t_id,t_name,t_description) values(1,3,'a2','aaa2');
insert into t2(t_root_id,t_id,t_name,t_description) values(0,4,'b','bbb');
insert into t2(t_root_id,t_id,t_name,t_description) values(4,5,'b1','bbb5');
insert into t2(t_root_id,t_id,t_name,t_description) values(4,6,'b2','bbb6');
insert into t2(t_root_id,t_id,t_name,t_description) values(6,8,'bbb1','bbb8');
insert into t2(t_root_id,t_id,t_name,t_description) values(6,9,'b2','bbb9');
insert into t2(t_root_id,t_id,t_name,t_description) values(5,7,'bbb','bbb7');

insert into t2(t_root_id,t_id,t_name,t_description) values(0,10,'bbb','bbb');
insert into t2(t_root_id,t_id,t_name,t_description) values(10,11,'bbb1','bbb1');
insert into t2(t_root_id,t_id,t_name,t_description) values(10,12,'b2','bbb2');

Select * From t2;

/*
   start with ... connect by prior ... 是实现递归查询，一般用来获取“树”型结构的数据。

   start with t_root_id = 0 ： 即根节点等于0的，start with条件是用来限制根节点的条件。
   
   connect by prior t_id = t_root_id : prior的意思是：上一条记录的t_id 等于这一条记录的 t_root_id
           如果省略prior，意味不递归，即：不取子节点，只取根节点。
**/

---- 全树
select * from t2 start with t_root_id = 0 connect by prior t_id = t_root_id;

---- 特定子树
select * from t2 start with t_id = 4 connect by prior t_id = t_root_id;
select * from t2 start with t_id = 1 connect by prior t_id = t_root_id;

---- 如果connect by prior中的prior被省略，则查询将不进行深层递归。
select * from t2 start with t_root_id = 0 connect by t_id = t_root_id;
select * from t2 start with t_id = 1 connect by t_id = t_root_id;

/*
   Sys_Connect_By_Path函数（Oracle9i后出来的新函数），是将父节点和子节点的指定字段拼接起来。
                                      ******************                                          
                                      *  id=4,desc=bbb *  
                                      ******************
                                        *            *
                                       *              *
                       *******************            *******************                                  
                       *  id=5,desc=bbb5 *            *  id=6,desc=bbb6 *
                       *******************            *******************    
                          *                                *          *
             ******************             ******************    ******************                                   
             *  id=7,desc=bbb7*             *  id=8,desc=bbb8*    *  id=9,desc=bbb9* 
             ******************             ******************    ******************
                                                       
     如图所示的，那么下面的sql的结果为(六条记录)：
     ,bbb            （此为根节点id=4的t_Description字段值与‘，’的拼接）
     ,bbb,bbb5       （此为左子节点id=5的t_Description字段值与父节点的t_Description的拼接）
     ,bbb,bbb5,bbb7   (此为父节点为id=5的子节点id=7的t_Description字段值与父节点的t_Description的拼接）
     ,bbb,bbb6       （此为右子节点id=6的t_Description字段值与父节点的t_Description的拼接）
     ,bbb,bbb6,bbb8  
     ,bbb,bbb6,bbb9
     
     总结：Sys_Connect_By_Path拼接函数，适用于树形结构的数据，拼接原理是：从根节点往下，每一个
     子节点的指定字段与父节点的指定字段拼接
********************************************************************************************************/
Select Sys_Connect_By_Path(t.t_Description, ',')
  From T2 t
 Start With t.t_Id = 4
Connect By Prior t.t_Id = t.t_Root_Id;

----与上面的拼接sql比较，查看拼接效果。
Select * From T2 Start With t_Id = 4 Connect By Prior t_Id = t_Root_Id;