层次化查询,即树型结构查询,是SQL中经常用到的功能之一,通常由根节点,父节点,子节点,叶节点组成,其语法如下:
SELECT [LEVEL] ,column,expression,...
FROM table_name
[WHERE where_clause]
[[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];
LEVEL:伪列,用于表示树的层次
start_condition：层次化查询的起始条件，指定阶层的根。
prior_condition：定义父节点和子节点之间的关系，PRIOR指定父节点。作为运算符，PRIOR和加(+)减(-)运算的优先级相同。condition ... PRIOR expr = expr 或者 ... expr = PRIOR expr
例：
CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
CONNECT BY PRIOR employee_id = manager_id and PRIOR account_mgr_id = customer_id
SYS_CONNECT_BY_PATH这个函数是oracle9i才新提出来的！它一定要和connect by子句合用！第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符！
示例
SQL> select empno,mgr,ename,job,level from emp
2 start with empno = 7839
3* connect by prior empno = mgr
EMPNO MGR ENAME JOB LEVEL
---------- ---------- -------- ---------- ----------
7839 KING PRESIDENT 1
7566 7839 JONES MANAGER 2
7788 7566 SCOTT ANALYST 3
7876 7788 ADAMS CLERK 4
7902 7566 FORD ANALYST 3
7369 7902 SMITH CLERK 4
7698 7839 BLAKE MANAGER 2
7499 7698 ALLEN SALESMAN 3
7521 7698 WARD SALESMAN 3
7654 7698 MARTIN SALESMAN 3
7844 7698 TURNER SALESMAN 3
EMPNO MGR ENAME JOB LEVEL
---------- ---------- -------- ---------- ----------
7900 7698 JAMES CLERK 3
7782 7839 CLARK MANAGER 2
7934 7782 MILLER CLERK 3
SQL> select empno,max(sys_connect_by_path(ename,',')) a from emp
2 start with empno=7839
3 connect by prior empno=mgr
4* group by empno
EMPNO A
---------- ------------------------------
7839 ,KING
7844 ,KING,BLAKE,TURNER
7782 ,KING,CLARK
7698 ,KING,BLAKE
7902 ,KING,JONES,FORD
7521 ,KING,BLAKE,WARD
7566 ,KING,JONES
7788 ,KING,JONES,SCOTT
7654 ,KING,BLAKE,MARTIN
7934 ,KING,CLARK,MILLER
7499 ,KING,BLAKE,ALLEN
EMPNO A
---------- ------------------------------
7876 ,KING,JONES,SCOTT,ADAMS
7369 ,KING,JONES,FORD,SMITH
7900 ,KING,BLAKE,JAMES
14 rows selected.
树型结构遍历过程(通过上面的查询来描述)
1).从根节点开始(即where_clause中的条件,如果为非根节点则分根节点作为根节点开始遍历,如上例empno = 7839)
2).遍历根节点(得到empno = 7839记录的相关信息)
3).判断该节点是否存在子节点，如果有则访问最左侧未被访问的子节点，否则下一步。上例中prior_condition为empno = mgr，表示子节点的mgr等于父节点的empno,即下一条返回记录的mgr应当等于前一条记录的empno
4).当节点为叶节点，则访问完毕。
5).返回到该节点的父节点，直至检索完所有数据
CONNECT_BY_ISLEAF：查询节点是否是叶子节点，是则为1，不是则为0
CONNECT_BY_ROOT 查询指定根的阶层数据。