各位大虾，哪位高手能解决这道ORACLE编程题。要用到树的遍历算法，小弟谢之

Question

一张表有如下字段
CREATE TABLE EMPLOYEE
(
   EMPNO number(4) NOT NULL primary key,
   ENAME VARCHAR2(10),
JOB VARCHAR2(9),    //表示职位
     LEAF NUMBER(1),    
     PARENT NUMBER(4),//上司
     DEEP NUMBER(1)     
);其实是张公司员工的表，PARENT表示上司的员工编号，LEAF为1表示没有下属及叶子节点，为0表示有下属及有子节点，DDEP表示属于第几管理成（比如BOSS权限最高，则相当于根节点，DEEP=0，MANAGE是BOSS下属DEEP就为1）。
插入如下数据
INSERT INTO EMPLOYEE VALUES(7369,'SMITH','CLERK',1,7982,3)
 INSERT INTO EMPLOYEE VALUES(7499,'ALLEN','SALESMAN',1,7698,2)
 INSERT INTO EMPLOYEE VALUES(7521,'WARD','SALESMAN',1,7698,2)
 INSERT INTO EMPLOYEE VALUES(7566,'JONES','MANAGER',0,7893,1)
 INSERT INTO EMPLOYEE VALUES(7654,'MARTIN','SALESMAN',1,7698,2)
 INSERT INTO EMPLOYEE VALUES(7698,'BLAKE','MANAGER',0,7839,1)
 INSERT INTO EMPLOYEE VALUES(7782,'CLARK','MANAGER',0,7839,1)
 INSERT INTO EMPLOYEE VALUES(7788,'SCOTT','ANALYST',0,7566,2)
 INSERT INTO EMPLOYEE VALUES(7839,'KING','PRESIDENT',0,0,0)
 INSERT INTO EMPLOYEE VALUES(7844,'TURNER','SALESMAN',1,7698,2)
 INSERT INTO EMPLOYEE VALUES(7876,'ADAMS','CLERK',1,7788,3)
 INSERT INTO EMPLOYEE VALUES(7900,'JAMES','CLERK',1,7698,2)
 INSERT INTO EMPLOYEE VALUES(7902,'FORD','ANALYST',0,7566,2)
 INSERT INTO EMPLOYEE VALUES(7934,'MILLER','CLERK',1,7782,2)
用存储过程实现以下输出结果：
7839 KING
========7566 JONES
===============7788 SCOTT
========================7876 ADAMS
===============7902 FORD
========================7369 SMITH
========7698 BLAKE
===============7499 ALLEN
===============7521 WARD
===============7654 MARTIN
===============7844 TURNER
===============7900 JAMES
========7782 CLARK
===============7934 MILLER

Answer 1

此有何难，用start with
connecy by 来遍历树

Answer 2

仍然有疑问，具体怎么实现！··
小弟是新手start with 
connecy by没听说过·，用一般的SQL语句编写的存储过程怎么写，用递归算法的··谢之

Answer 3

select rpad('=',deep*3,'=') deepstr,ename,empno from employee connect by prior empno=employee.parent ;

Answer 4

oracle存储过程不支持递归，你的用迭代的方法做，最快的是4楼说的 方法。自己写的话建个临时表，先吧boss插进去，然后依次把下一层的插进去

Answer 5

SQL> select lpad('=',level*6,'=')||EMPNO||ENAME
  2    from quanc.EMPLOYEE
  3   start with DEEP=0
  4   connect by prior EMPNO=PARENT
  5  ;LPAD('=',LEVEL*6,'=')||EMPNO||ENAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
======7839KING
============7698BLAKE
==================7499ALLEN
==================7521WARD
==================7654MARTIN
==================7844TURNER
==================7900JAMES
============7782CLARK
==================7934MILLER

Answer 6

递归这么做,不过比较慢就是了
/*
过程说明：二分法遍历树。
参数说明：pPARENT:父节点
          pDeep:第几层
          ptagLen:修饰参数，打印时候‘=’的数目倍数
*/
create or replace procedure sp_emp_visit
(
  pPARENT number,
  pDeep number,
  ptagLen number
)
as
  strLine varchar2(200);
  v_child_count number;
  v_empno employee.empno%type;
  v_ename employee.ename%type;
  v_leaf employee.leaf%type;
  cursor cur_emp is select empno,ename,leaf from employee where employee.parent=pParent and deep=pDeep;
begin
  open cur_emp;
  loop
    fetch cur_emp into v_empno,v_ename,v_leaf;
    exit when cur_emp%NOTFOUND;
    strLine:=rpad('=',pDeep*ptagLen,'=')||to_char(v_empno)||' '||v_ename;
    DBMS_OUTPUT.PUT_LINE(strLine);
    --判断其叶子节点是否存在,如果存在，递归调用
    if v_leaf=0 then
      sp_emp_visit(v_empno,pDeep+1,ptagLen);
    end if;
  end loop;
  close cur_emp;
exception
  when others then
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
end sp_emp_visit;测试:
set serveroutput on;
begin
sp_emp_visit(0,0,6);
end;结果
7839 KING
======7566 JONES
============7788 SCOTT
==================7876 ADAMS
============7902 FORD
======7698 BLAKE
============7499 ALLEN
============7521 WARD
============7654 MARTIN
============7844 TURNER
============7900 JAMES
======7782 CLARK
============7934 MILLER说明:你的测试数据有问题,有两条数据你写错了.
INSERT INTO EMPLOYEE VALUES(7369,'SMITH','CLERK',1,7982,3) ????parent多少?打印不出你列出的效果.