ORACLE SQL语句遍历查询表数据

注释：depid 为节点编号 upid为父节点编号 SMDEPARTMENT 表名称

1、部门3及其所有下级

SELECT lpad('', 2 * (LEVEL - 1), '') || t.depid AS depid
      ,t.*
  FROM SMDEPARTMENT t
CONNECT BY t.upid = PRIOR t.depid
 START WITH t.depid = 3

2、部门3及其所有上级

SELECT lpad('', 2 * (LEVEL - 1), '') || t.depid AS depid
      ,t.*
  FROM SMDEPARTMENT t
CONNECT BY PRIOR t.upid = t.depid
 START WITH t.depid = 3