The following statement returns all employees in hierarchical order. The root row is defined to be the employee whose job is 'PRESIDENT'. The child rows of a parent row are defined to be those who have the employee number of the parent row as their manager number. SELECT LPAD(' ',2*(LEVEL-1)) || ename org_chart, empno, mgr, job FROM emp START WITH job = 'PRESIDENT' CONNECT BY PRIOR empno = mgr; ORG_CHART EMPNO MGR JOB ------------ ---------- ---------- --------- KING 7839 PRESIDENT JONES 7566 7839 MANAGER SCOTT 7788 7566 ANALYST ADAMS 7876 7788 CLERK FORD 7902 7566 ANALYST SMITH 7369 7902 CLERK BLAKE 7698 7839 MANAGER ALLEN 7499 7698 SALESMAN WARD 7521 7698 SALESMAN MARTIN 7654 7698 SALESMAN TURNER 7844 7698 SALESMAN JAMES 7900 7698 CLERK CLARK 7782 7839 MANAGER MILLER 7934 7782 CLERK
START WITH condition CONNECT BY condition returns rows in a hierarchial order. For example:SELECT LEVEL, parent_name, dept_name FROM dept CONNECT BY prior dept_name = parent_name START WITH dept_name = 'Software' ORDER BY LEVEL;
select * from t_jg start with jgbh='1070000000' connect by sjjgbh=prior jgbht_jg: jgbh jgmc sjjgbh
empno, mgr, job
FROM emp
START WITH job = 'PRESIDENT'
CONNECT BY PRIOR empno = mgr; ORG_CHART EMPNO MGR JOB
------------ ---------- ---------- ---------
KING 7839 PRESIDENT
JONES 7566 7839 MANAGER
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
FORD 7902 7566 ANALYST
SMITH 7369 7902 CLERK
BLAKE 7698 7839 MANAGER
ALLEN 7499 7698 SALESMAN
WARD 7521 7698 SALESMAN
MARTIN 7654 7698 SALESMAN
TURNER 7844 7698 SALESMAN
JAMES 7900 7698 CLERK
CLARK 7782 7839 MANAGER
MILLER 7934 7782 CLERK
START WITH condition CONNECT BY condition returns rows in a hierarchial order. For example:SELECT LEVEL, parent_name, dept_name
FROM dept
CONNECT BY
prior dept_name = parent_name
START WITH
dept_name = 'Software'
ORDER BY LEVEL;
start with jgbh='1070000000'
connect by sjjgbh=prior jgbht_jg:
jgbh jgmc sjjgbh