oracle编程入门笔记2015-02-05--递归查询

应用场景

知道a部门，想找到其所有上级部门。部门间用id和父级id相互关联。

以上是用oracle递归查询实现的，当然可以用自连接，但是由于你不确定有多少层，所以自连接的次数也不好确定。

start with 条件就是整个递归的起点，connect by 是递归条件，prior从字面意思理解可以使当前记录的fparentid = 下一条的fid这样就可以根据当前记录定位下一条记录。

使用递归子查询实现递归

和connect by 效果一样虽然代码多了一点。但是据说更加灵活，由于我还没有实际用到，所以我也不知道哪里比较灵活。

递归子查询要注意的地方

1.with as 子查询在用于递归查询的时候，必须要指定列别名。

2.union all前半部分指定了原始数据

3.递归子查询有一个隐式列lvl可以标示当前层级等效于connect by 中的level

4.可以用search depth/breadth  first 来指定深度优先还是广度优先

5.4可以不指定，默认广度优先。但是如果指定了，就必须有by 来指定按照哪个字段排序，set来指定排序后的结果顺序别名

connect 其他选项，伪列和函数

level伪列：显示记录的层级，起始行属于第一层，依次递增。

sys_connect_by_path（column_name,separtor）:从根节点返回column_name指定的列，用指定的分隔符分割。

connect_by_root <column_name>:可以再每一行递归记录都返回根节点的一列值。

递归过程最容易发生的错误就是数据循环，所以为了避免和定位该类异常

首先制造循环，把fid为465375的fparentid 改成49311

解决：在connect by后面加上nocycle就可以先解决报错问题，把正常的数据先返回

再使用connect_by_iscycle 伪列标示出出错的行

最后一个比较实用的伪列是connect_by_isleaf:标示一行记录是不是这个递归的叶子节点。

总结：connect by的功能使用递归子查询一般都可以实现。但是正如例子中所示，递归子查询往往需要更多的代码。更多的代码意味着更多出错的机会。而且据别人分析connect by的效率要高于递归子查询。所以我一般倾向可以使用connect by实现的绝对不麻烦递归子查询。