ORACLE递归查询

一、建表
CREATE TABLE TB_GROUP(
groupid VARCHAR2(32),
groupname VARCHAR2(50),
parentid VARCHAR2(32) DEFAULT -1
　)

　INSERT INTO TB_GROUP(groupid,groupname,parentid) VALUES('9001','根节点','-1');

　INSERT INTO TB_GROUP(groupid,groupname,parentid) VALUES('10001','一级节点','9001');

　INSERT INTO TB_GROUP(groupid,groupname,parentid) VALUES('10002','一级节点','9001');

　INSERT INTO TB_GROUP(groupid,groupname,parentid) VALUES('10003','二级节点','10001');

　INSERT INTO TB_GROUP(groupid,groupname,parentid) VALUES('10004','三级节点','10003');

二、格式

　Select * from …. Where [结果过滤条件语句]

　Start with [and起始条件过滤语句]

　Connect by prior [and中间记录过滤条件语句]

三、查找所有下级
　select * from TB_GROUP start with groupid = 9001 connect by prior groupid = parentid
注意：此sql能查找groupid = 9001的数据的所有下级，写sql语句时要注意，因为是从groupid开始查找下级，所以connect by prior 子句的条件是groupid=parentid

四、查找所有上级
　select * from TB_GROUP start with groupid = 10004 connect by prior parentid = groupid
注意：因为是从groupid = 10004开始查找上级，所以connect by prior 子句的条件是parentid = groupid

五、java.sql.SQLException:ORA-01436: 用户数据中的 CONNECT BY 循环
报这个错误原因是因为产生了循环。查找数据发现，数据groupid为9001的根节点parentid为子节点groupid,而子节点parentid为9001，这样就产生了循环将groupid为9001的根节点parentid改为-1或其它值就正常了。

六、效率
　SELECT tu.userid FROM tbuser tu,tbgroup tg
　WHERE tu.groupid = tbgroup.groupid AND tg.groupid in (SELECT groupid FORM tbgroup CONNECT BY PRIOR groupid = parented START WITH groupid =<param>groupid</param>) <conf>where的其它条件</conf>
　注意：在递归数量级不多的时候可以使用，oracle IN 操作符有长度限制，且IN在数量级大的情况下的效率比较差。

　推荐使用下面的写法：
　SELECT tu.userid FROM tbuser tu,(SELECT groupid FROM tbgroup CONNECT BY PRIOR groupid = parentid START WITH groupid = <param>groupid</param>) tbgroup
　WHERE 1=1 AND tu.groupid = tbgroup.groupid <conf>where的其它条件</conf>
　注意：具体使用CONNECT BY规则ORACLE的文档说的很清楚。以上SQL是结合我们实际的表结构写的，含义是组合其它条件，查询某个机构及其子机构下的所有人员。

七、层次查询
自从Oracle 9i开始，可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”，如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示“1”，否则就显示“0”。

SELECT tu.userid，SYS_CONNECT_BY_PATH FROM tbuser tu,(SELECT groupid FROM tbgroup CONNECT BY PRIOR groupid = parentid START WITH groupid = <param>groupid</param>) tbgroup
　WHERE 1=1 AND tu.groupid = tbgroup.groupid <conf>where的其它条件</conf>