HONGYE:145@hongye > conn scott/tiger

Connected.

SCOTT:145@hongye > select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30

7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30

7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20

7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30

7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30

7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10

7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10

7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30

7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30

7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20

7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

14 rows selected.

EMP 表中包含了雇员和其上司的关系，本次实验以此关系为准

按照 “上司--下属” 之间的关系，那么EMP表就可以画出如下的树形图：

例子1：从 CLARK 开始，遍历其子节点，即找出 CLARK 和其所有下属的信息。

SCOTT:145@hongye > select level,empno,ename,job,mgr from emp start with empno=7782 connect by prior empno=mgr;

LEVEL EMPNO ENAME JOB MGR

---------- ---------- ---------- --------- ----------

1 7782 CLARK MANAGER 7839

2 7934 MILLER CLERK 7782

对应的图为：

例子2：从根节点开始，遍历子节点，找出公司里面的所有员工信息，

此处，使用 lpad 函数，格式化输出。

其输出图即为以上的大图。

SCOTT:145@hongye > col name for a30

SCOTT:145@hongye > select level,lpad(' ',2*(level-1))||ename name, empno,job,mgr from emp start with empno=7839 connect by

2 prior empno=mgr;

LEVEL NAME EMPNO JOB MGR

---------- ------------------------------ ---------- --------- ----------

1 KING 7839 PRESIDENT

2 JONES 7566 MANAGER 7839

3 SCOTT 7788 ANALYST 7566

4 ADAMS 7876 CLERK 7788

3 FORD 7902 ANALYST 7566

4 SMITH 7369 CLERK 7902

2 BLAKE 7698 MANAGER 7839

3 ALLEN 7499 SALESMAN 7698

3 WARD 7521 SALESMAN 7698

3 MARTIN 7654 SALESMAN 7698

3 TURNER 7844 SALESMAN 7698

3 JAMES 7900 CLERK 7698

2 CLARK 7782 MANAGER 7839

3 MILLER 7934 CLERK 7782

14 rows selected.

例子3：从子节点向上遍历，直到遍历到根节点。

即找出一个员工和其所有直接上司和间接上司的关系

SCOTT:145@hongye > select level,lpad(' ',2*(level-1))||ename name, empno,job,mgr from emp start with empno=7876 connect by

2 prior mgr=empno;

LEVEL NAME EMPNO JOB MGR

---------- ------------------------------ ---------- --------- ----------

1 ADAMS 7876 CLERK 7788

2 SCOTT 7788 ANALYST 7566

3 JONES 7566 MANAGER 7839

4 KING 7839 PRESIDENT

对应的图为：

例子4：在遍历过程中，剔除某一个节点的信息，使用 where 子句

SCOTT:145@hongye > select level,lpad(' ',2*(level-1))||ename name, empno,job,mgr from emp where ename!='SCOTT' start with empno=7876 connect by prior mgr=empno;

LEVEL NAME EMPNO JOB MGR

---------- ------------------------------ ---------- --------- ----------

1 ADAMS 7876 CLERK 7788

3 JONES 7566 MANAGER 7839

4 KING 7839 PRESIDENT

例子5：在例子4中，虽然删除了一个节点的信息，但是被删除节点的子节点的信息还是会存在，

如果想要删除节点和其所有子节点的信息，那么就需要在 connect by 子句中加条件了。

SCOTT:145@hongye > select level,lpad(' ',2*(level-1))||ename name, empno,job,mgr from emp where ename!='SCOTT' start with empno=7876 connect by prior mgr=empno and ename!='SCOTT';

LEVEL NAME EMPNO JOB MGR

---------- ------------------------------ ---------- --------- ----------

1 ADAMS 7876 CLERK 7788

SCOTT:145@hongye >

Untitled.jpg