Óú¯ÊýʵÏÖoracleµÄsys_connect_by_path¹¦ÄÜ

1.½¨±í£¬²åÈëÊý¾Ý
create table dept(deptno number,deptname varchar2(20),mgrno number);
insert into dept values(1,'×ܹ«Ë¾',null);
insert into dept values(2,'Õã½­·Ö¹«Ë¾',1);
insert into dept values(3,'º¼ÖÝ·Ö¹«Ë¾',2);
insert into dept values(4,'ºþ±±·Ö¹«Ë¾',1);
insert into dept values(5,'Î人·Ö¹«Ë¾',4);
2.oracle²éѯÓï¾ä
select *,sys_connect_by_path(deptname,'/') deptname  from dept
  start with  mgrno is null connect by prior deptno=mgrno;
----------------------------------------------------------------------------------------
1        ×ܹ«Ë¾        <NULL>        /×ܹ«Ë¾       
2        Õã½­·Ö¹«Ë¾        1           /×ܹ«Ë¾/Õã½­·Ö¹«Ë¾       
3        º¼ÖÝ·Ö¹«Ë¾        2           /×ܹ«Ë¾/Õã½­·Ö¹«Ë¾/º¼ÖÝ·Ö¹«Ë¾     
4        ºþ±±·Ö¹«Ë¾        1           /×ܹ«Ë¾/ºþ±±·Ö¹«Ë¾
5        Î人·Ö¹«Ë¾        4          /×ܹ«Ë¾/ºþ±±·Ö¹«Ë¾/Î人·Ö¹«Ë¾
  
----------------------------------------------------------------------------------------
3.´´½¨º¯ÊýʵÏÖÀàËƹ¦ÄÜ
CREATE or replace FUNCTION Branch
(    vdeptname    varchar(200),
     vDelimiter   IN VARCHAR(10) DEFAULT '/')
   return varchar(4000)
as
c1 cursor;
temp varchar(4000);
vename varchar(200);
begin
 temp=vDelimiter;
 open c1 for select  deptname from dept start with deptname
  =vdeptname connect by  deptno= prior mgrno;
 loop
   FETCH