select * from tb_cus_area_cde
--子取父
select * from tb_cus_area_cde a
CONNECT BY PRIOR a.c_snr_area=a.c_area_cde START WITH a.c_area_cde='1040101'
--父取子
select * from tb_cus_area_cde a
CONNECT BY PRIOR a.c_area_cde=a.c_snr_area START WITH a.c_snr_area is null
注意:在用这个函数的时候,statement的参数要用 ResultSet.TYPE_SCROLL_INSENSITIVE 而不能用 ResultSet.TYPE_SCROLL_SENSITIVE,在这里再把这两个之间的区别讲讲:
1.TYPE_FORWORD_ONLY,只可向前滚动;
2.TYPE_SCROLL_INSENSITIVE,双向滚动,但不及时更新,就是如果数据库里的数据修改过,并不在ResultSet中反应出来。
3.TYPE_SCROLL_SENSITIVE,双向滚动,并及时跟踪数据库的更新,以便更改ResultSet中的数据
对于互为父子的情况,会出现死循环
SELECT
SYS_CONNECT_BY_PATH (EMPLOYEE,'/') as EMPLOYEE,
MANAGER,
CONNECT_BY_ISCYCLE
FROM
T
CONNECT BY
NOCYCLE
PRIOR EMPLOYEE = MANAGER;
这样