start with ...connect by Nocycle Prior 树查询_递归查询

select branchid from branch where parentbranchid='BRA0000000000001' order by branchid

--第一句只能查询出父节点是BRA0000000000001的所有子节点

select br.branchid
  from Branch br
 start with br.branchid = 'BRA0000000000001'
connect by Nocycle Prior br.branchid = br.parentbranchid

--这一句能查出父节点是BRA0000000000001的所有子节点，然后它的子节点的--子节点都能查出来

--语法：

[ START WITH condition ] 
CONNECT BY [ NOCYCLE ] condition

 select abbrname,branchid,A.PARENTBRANCHID,branchtype,branchstatus,LEVEL from branch a
where a.branchstatus='BRANCHSTATUS_1'
AND a.branchtype='BRANCHTYPE_30'
start with a.branchid='BRA0000000000002'
connect by nocycle prior a.branchid = a.parentbranchid;

 --正向递归查询（根据id查询自己和自己一下的数据） 

Java代码  

1. select * from table   
2.     start with id=9842  
3.     connect by prior id= parentId  
4. order by id  

--反向递归查询(根据叶子ID查出自己和自己之上的根数据) 

Java代码  

1. select * from table  
2.     start with id=9842  
3.     connect by nocycle prior parentId=id  
4. order by id