Oracle中的递归查询

转载自：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