转载自：http://community.csdn.net/Expert/topic/4998/4998917.xml?temp=.2047846
表结构如下：
该表只有两个字段，PNODE，NODE，PNODE为NODE父结点，当该节点无子节点时，PNODE=NODE
要求给定父结点，查出所有子节点，子孙节点......
如：
PNODE NODE
1 2
2 3
2 4
3 3
4 4
要求给定PNODE=1，给出结果为：
2
3
4
如果是10G以上的可以用NOCYCLE 去掉循环的影响
....
start with PNODE = 1
connect by NOCYCLE prior NODE = PNODE
....
10G以下的可以试试：
start with PNODE = 1
connect by prior NODE = PNODE
where NODE <> PNODE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> create table tab_a (pnode number(2),node number(2));
Table created
SQL> insert into tab_a values (1,2);
1 row inserted
SQL> insert into tab_a values (2,3);
1 row inserted
SQL> insert into tab_a values (2,4);
1 row inserted
SQL> insert into tab_a values (3,3);
1 row inserted
SQL> insert into tab_a values (4,4);
1 row inserted
SQL> commit;
Commit complete
----- oracle9i的: 构造tab 以避免循环
SQL> with
2 tab as (
3 select pnode,decode(node-pnode,0,null,node) node
4 from tab_a
5 )
6 select distinct pnode
7 from tab
8 where pnode >1
9 start with pnode=1
10 connect by prior node=pnode;
PNODE
-----
2
3
4