分层检索 oracle,分层检索

一、语法

SELECT *

FROM SOME_TABLE

[ WHERE 条件1 ]

CONNECT BY [ 条件2 ]

START WITH [ 条件3 ]

START WITH：指定阶层的根，也就是递归开始的地方；

CONNECT BY [NOCYCLE] [PRIOR]：指定阶层的父/子关系

NOCYCLE：存在CONNECT BY LOOP的纪录时，也返回查询结果。

PRIOR：缺省：只能查询到符合条件的起始行，并不进行递归查询；

CONNECT BY PRIOR expr = expr：前一种写法表示采用自上而下的搜索方式(先找父节点然后找子节点)

CONNECT BY expr = PRIOR expr：后一种写法表示采用自下而上的搜索方式(先找叶子节点然后找父节点)。

二、执行原理

在扫描树结构表时，需要依此访问树结构的每个节点，一个节点只能访问一次，其访问的步骤如下：

第一步：从根节点开始；

第二步：访问该节点；

第三步：判断该节点有无未被访问的子节点，若有，则转向它最左侧的未被访问的子节，并执行第二步，否则执行第四步；

第四步：若该节点为根节点，则访问完毕，否则执行第五步；

第五步：返回到该节点的父节点，并执行第三步骤。

总之：扫描整个树结构的过程也即是中序遍历树的过程。

三、相关函数

LEVEL：通过LEVEL虚拟列表示节点层级。

SYS_CONNECT_BY_PATH：函数就是从start with开始的地方开始遍历，并记下其遍历到的节点，start with开始的地方被视为根节点，将遍历到的路径根据函数中的分隔符，组成一个新的字符串，这个功能还是很强大的。

CONNECT_BY_ROOT ：函数用来列的前面，记录的是当前节点的根节点的内容。

CONNECT_IS_LEAF：函数用来判断当前节点是否包含下级节点，如果包含的话，说明不是叶子节点，这里返回0；反之，如果不包含下级节点，这里返回1

四、例句

--创建示例表：

CREATE TABLE TBL_TEST (

ID NUMBER,

NAME VARCHAR2(100 BYTE),

PID NUMBER DEFAULT 0

);

--插入测试数据：

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

--从Root往树末梢递归：

SELECT *

FROM TBL_TEST

START WITH ID = 1

CONNECT BY PRIOR ID = PID;

--从末梢往树ROOT递归：

SELECT *

FROM TBL_TEST

START WITH ID = 5

CONNECT BY PRIOR PID = ID;