--使用connect by和strart with子句 SELECT [level],column,expression, ...
FROM table [WHERE where_clause] [[START WITH start_condition] [CONNECT BY prior_condition]];SELECT empno,
mgr, ename, job FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr;--使用偽列level SELECT LEVEL, empno, mgr, ename, job FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr ORDER BY LEVEL, mgr, empno;
--格式化層次查詢的結果 SELECT LEVEL, empno, mgr, lpad(' ',2*level-1)||ename as employee, job FROM emp START WITH mgr is null CONNECT BY PRIOR empno = mgr;
--從非根節點開始遍歷 SELECT LEVEL,
empno, mgr, lpad(' ',2*level-1)||ename as employee, job FROM emp START WITH ename like 'JONES' CONNECT BY PRIOR empno = mgr;--在start with子句中使用子查詢
SELECT LEVEL, empno, mgr, lpad(' ',2*level-1)||ename as employee, job FROM emp START WITH empno=(select empno from emp where ename='CLARK') CONNECT BY PRIOR empno = mgr;--向上遍歷 SELECT LEVEL,
empno, mgr, lpad(' ',2*level-1)||ename as employee, job FROM emp START WITH ename like 'JONES' CONNECT BY PRIOR mgr=empno;--從層次化查詢中刪除節點 SELECT LEVEL, empno, mgr, lpad(' ',2*level-1)||ename as employee, job FROM emp WHERE ename !='JONES' START WITH mgr is null CONNECT BY PRIOR empno = mgr;
--上面的查詢雖然刪除了節點但是並沒有刪除該節點的分支，為了連同分支也一併刪除，在connect by子句中使用and子句
SELECT LEVEL, empno, mgr, lpad(' ',2*level-1)||ename as employee, job FROM emp START WITH mgr is null CONNECT BY PRIOR empno = mgr AND ename !='JONES';--在層次化查詢中加入其它條件 SELECT LEVEL,
empno, mgr, lpad(' ',2*level-1)||ename as employee, job, sal FROM emp WHERE sal<3000 START WITH mgr is null CONNECT BY PRIOR empno = mgr;