1.基本语法是：

select ... from 
where 
start with 
connect by ;
//：过滤条件，用于对返回的所有记录进行过滤。
//：查询结果重起始根结点的限定条件。
//：连接条件
//如果connect by prior中的prior被省略，则查询将不进行深层递归。

2.向上查询当前菜单及所有上级菜单

select t.* from s_menu t start with t.id='510' connect by prior t.fid = t.id

3.向上查询所有上级菜单(不包含当前菜单)

select t.* from s_menu t start with t.id='510' connect by prior t.fid = t.id

4.向下查询当前菜单及所有下级菜单

select t.* from s_menu t start with t.id='001' connect by prior t.id = t.fid 

5.向下查询所有下级菜单(不包含当前菜单)

select t.* from s_menu t start with t.id='001' connect by prior t.id = t.fid

6.查询递归路径

select t.id,t.name,fid,substr(sys_connect_by_path(NAME,'->'),3) menu_path
from s_menu t start with t.name = '系统功能' connect by prior t.id = t.fid order by t.id

7.分层次显示

select t.id,lpad('|-',(level-1)*4,'|-')||lpad('『',2)||t.name||rpad('』',2) as newname
from s_menu t connect by prior t.id=t.fid start with t.id='-1'

总结

向上递归和向下递归的关键就是最后一个条件，父ID等于ID则向上递归，反之向下递归。

参考：http://www.cnblogs.com/wanghonghu/archive/2012/08/31/2665945.html