Oracle递归查询connectby用法-木庄网络博客

本文整理自网络，侵删。

一、概述

Oracle中可以通过START WITH . . . CONNECT BY . . .子句来实现SQL的层次查询.
自从Oracle 9i开始，可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。
自从Oracle 10g 中，还有其他更多关于层次查询的新特性。例如，有的时候用户更关心的是每个层次分支中等级最低的内容。
那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”，
如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示“0”。
在Oracle 10g 之前的版本中，如果在你的树中出现了环状循环（如一个孩子节点引用一个父亲节点），
Oracle 就会报出一个错误提示：“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。
而在 Oracle 10g 中，只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE，
如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示“1”，否则就显示“0”。

1、层级查询的基本语法：

select [level],*　　feom table_name 　
start with 条件1
connect by [ nocycle ]  prior 条件2
where 条件3　ORDER　BY [ sibilings ]　排序字段

说明：

start with condition1 是用来限制第一层的数据，或者叫根节点数据；以这部分数据为基础来查找第二层数据，然后以第二层数据查找第三层数据以此类推。
connect by [prior] id=parentid 连接条件，目的就是给出父子之间的关系是什么，根据这个关系进行递归查询
where 条件3---过滤条件，对所有返回的记录进行过滤。
order by 排序字段---对所有返回记录进行排序
对prior说明：
- connect by prior dept_id=par_dept_id ：采用自上而下的搜索方式（先找父节点然后找叶子节点），比如说查找第二层的数据时用第一层数据的id去跟表里面记录的parentid字段进行匹配，匹配成功那么查找出来的就是第二层数据；
- connect by dept_id=prior par_dept_id：采用自下而上的搜索方式（先找叶子节点然后找父节点）。比如说用第一层数据的parentid去跟表记录里面的id进行匹配，匹配成功那么查找出来的就是第二层数据；
level关键字，LEVEL---伪列，用于表示树的层次，第一层是数字1，第二层数字2，依次递增。
CONNECT_BY_ROOT方法，能够获取第一层集结点结果集中的任意字段的值；例CONNECT_BY_ROOT（字段名）。

二、使用

1、基本用法

例1、查询Raphaely及其的所有下属

select *  from employees
 start with last_name = 'Raphaely'
   connect by prior employee_id = manager_id;  --找下属
-- connect by employee_id = prior manager_id;  --找上司，第一种,修改prior关键字位置
-- connect by prior manager_id = employee_id;  --找上司，第二种,prior关键字不动 调换后面的 employee_id = manager_id 逻辑关系的顺序

例2、查询除了Raphaely和他下属的所有员工

select * from employees
 start with manager_id is null
 connect by prior employee_id = manager_id and last_name <> 'Raphaely';

例3、统计树形的层数

select count(distinct LEVEL)   from EMPLOYEES
 start with MANAGER_ID is null
 connect by prior EMPLOYEE_ID = MANAGER_ID;

例4、过滤某些结果集，注意：where子句比connect by后执行。

查询Kochhar的所有下属中lastname为 Mavris雇员。

SELECT * FROM employees 
 WHERE  last_name = 'Mavris' 
 START WITH last_name = 'Kochhar' --Kochhar的所有雇员
 CONNECT BY PRIOR employee_id = manager_id;

例5、level伪列的使用，格式化层级

select lpad(' ',level*2,' ')||emp_name as name,emp_id,manager_id,salary,level from employee
  start with manager_id=0
  connect by prior emp_id=manager_id

2、SYS_CONNECT_BY_PATH() 函数

作用: 将父节点到当前节点的路径按照指定的模式展现出来，把一个父节点下的所有节点通过某个字符区分，然后链接在一个列中显示。

格式:

sys_connect_by_path(<列名>,<连接串>)

select sys_connect_by_path(t.dept_name,'-->'),t.dept_id, t.dept_name, t.dept_code,t.par_dept_id, level  from SYS_DEPT t  
start with t.dept_id = 'e01d6' 
connect by prior t.dept_id = t.par_dept_id
order by level, t.dept_code