Oracle Connect By用法

oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询，connect by 是结构化查询中用到的，其基本语法是：

select ... from <TableName>
where <Conditional-1>
start with <Conditional-2>
connect by <Conditional-3>
;

<Conditional-1>：过滤条件，用于对返回的所有记录进行过滤。
<Conditional-2>：查询结果重起始根结点的限定条件。
<Conditional-3>：连接条件.
如果connect by prior中的prior被省略，则查询将不进行深层递归。

在实际的应用中,在父节点下取出所有属于该节点的子节点。
以下是一个实例：

create table T_NEST_TBL
(
  PRODUCT_NAME VARCHAR2(20),
  PRODUCT_DESC VARCHAR2(100),
  CREATE_TIME  DATE default sysdate,
  UPDATE_TIME  DATE default sysdate,
  PRODUCT_ID   NUMBER(10) not null,
  PARENT_ID    NUMBER(10) not null
);

SQL> insert into T_NEST_TBL values('HEALTH PRODUCT','HEALTH PRODUCT FOR LIFE', sysdate,sysdate,1, 0 );

1 row inserted

SQL> insert into T_NEST_TBL values( 'MEDICAL PRODUCT','MEDICAL PRODUCT FOR LIFE', sysdate,sysdate,2, 0);

1 row inserted

SQL> insert into T_NEST_TBL values('GTL','GTL HEALTH PRODUCT FOR LIFE', sysdate,sysdate,3, 1);

1 row inserted

SQL> insert into T_NEST_TBL values( 'LUV','LUV HEALTH PRODUCT FOR LIFE', sysdate,sysdate,4, 1);

1 row inserted

SQL> SELECT * FROM T_NEST_TBL
  2  ;

PRODUCT_NAME         PRODUCT_DESC                                                                     CREATE_TIME UPDATE_TIME  PRODUCT_ID  PARENT_ID
-------------------- -------------------------------------------------------------------------------- ----------- ----------- ----------- ----------
HEALTH PRODUCT       HEALTH PRODUCT FOR LIFE                                                          12/18/2009  12/18/2009            1          0
MEDICAL PRODUCT      MEDICAL PRODUCT FOR LIFE                                                         12/18/2009  12/18/2009            2          0
GTL                  GTL HEALTH PRODUCT FOR LIFE                                                      12/18/2009  12/18/2009            3          1
LUV                  LUV HEALTH PRODUCT FOR LIFE                                                      12/18/2009  12/18/2009            4          1

获取某一子节点所属的父节点：

SQL> SELECT * FROM T_NEST_TBL NT START WITH NT.PRODUCT_ID = 1 CONNECT BY PRIOR NT.PARENT_ID = NT.PRODUCT_ID;

PRODUCT_NAME         PRODUCT_DESC                                                                     CREATE_TIME UPDATE_TIME  PRODUCT_ID  PARENT_ID
-------------------- -------------------------------------------------------------------------------- ----------- ----------- ----------- ----------
HEALTH PRODUCT       HEALTH PRODUCT FOR LIFE                                                          12/18/2009  12/18/2009            1          0

获取某一父节点下的所有子节点:

SQL> SELECT * FROM T_NEST_TBL NT START WITH NT.PARENT_ID = 1 CONNECT BY PRIOR NT.PRODUCT_ID = NT.PARENT_ID;

PRODUCT_NAME         PRODUCT_DESC                                                                     CREATE_TIME UPDATE_TIME  PRODUCT_ID  PARENT_ID
-------------------- -------------------------------------------------------------------------------- ----------- ----------- ----------- ----------
GTL                  GTL HEALTH PRODUCT FOR LIFE                                                      12/18/2009  12/18/2009            3          1
LUV                  LUV HEALTH PRODUCT FOR LIFE                                                      12/18/2009  12/18/2009            4          1