ORACLE中的LEVEL递归查询

ORACLE中的LEVEL递归查询

1、oracle中level关键字是什么和使用场景

1、level关键字

level仅仅用于在对表执行层次树遍历的select语句中，他是数据所在位置的层级

• connect by prior 子句定义表中的数据是如何相互联系的
• start with 子句定义树查询的初始起点

level： 表示查询深度(level 表示递归的层次) ，prior 和 start with 关键字是可选项。
prior： 运算符必须放置在连接关系的两列中某一个的前面(递归的作用就是由子查父，由父查子)。对于节点间的父子关系， prior运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是 自顶向下 还是 自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式，后面我会用案例说明。
start with 子句为 可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。

2、使用场景

connect by prior和level都是为了快速的查询层级关系的关键字，在代理关系中，或者权限关系中，经常会有层层嵌套的场景，比如查某个字段向上查询对应的父级，向下查询对应的子级，以及均分某天，均分某月等等情况，再正常情况下我们获取一般是通过自连接for循环，这些都会导致实现较麻烦和代码冗余，有些场景实现效率很低。oracle提供level相关的语法进行快速查询，用递归的方式实现。
案例：

  /* select …,level from tablename   start with 条件a   connect by   prior 子字段编码 = 父字段编码 where 条件b;
   条件  (一般是写子字段编码的关系表达式)
   子字段编码 = 父字段编码 向下查询
   父字段编码= 子字段编码 向上查询
  */
  
  -- 假设员工表  employee_table  员工编号：emp_no  领导编号：agent_no
  /* 
     1、level = 1 查询员工编号 = 'A10000'的员工表数据信息此时层级数为1
        emp_no = 'A10000'
     2、level = 2 查询领导编号 = 'A10000'的员工表数据信息此时层级数据为2
        agent_no = 'A10000' 时的emp_no数据
    -- 此时emp_no的数据就是员工编号= 'A10000' + 领导编号 = 'A10000'下的emp_no数据之和
  */
  select emp_no from  employee_table start with emp_no = 'A10000' connect by prior emp_no   = agent_no order by level ;
  /*
  	1、level = 1 查询领导编号 = 'A10000'的员工表数据信息此时层级数为1
  	   emp_no = 'A10000'
  	2、查询 员工编号 = (员工编号 = 'A10000'的领导编号)员工表数据信息此时层级数为2
  	  员工编号 = (员工编号 = 'A10000'的领导编号)的emp_no
    -- 此时emp_no的数据就是员工编号= 'A10000' + 员工编号 = (员工编号 = 'A10000'的领导编号)的emp_no  
  */
  select emp_no from  employee_table start with emp_no = 'A10000' connect by prior agent_no = emp_no   ;

2、使用MATERIALIZED VIEW 优化查询

materialized view 是一种允许预计算结果并将其存储在磁盘上的数据库对象。通过使用materialized view存储递归查询的结果，可以有效地提高查询速度，可以将它相当于视图理解，递归查询的结果放在该视图之中。
例如：

 create materialized view  v_emp as  select …,level from tablename   start with 条件a   connect by   prior 子字段编码 = 父字段编码 where 条件b;
 select * from v_emp ;