Oracle子查询因子化（公共表表达式）及递归

原SQL：

select *
  from (select product, country, year, week, inventory, sale, receipts
          from sales_fact model return updated rows partition by(product, country) dimension by(year, week) measures(0 inventory, sale, receipts) rules automatic
         order(inventory [ year, week ] = nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ]))
 where country in ('Australia')
   and product = 'Xtend Memory'
 order by product, country, year, week;

使用with子句建立因子化子查询（便于阅读、理解）：

with model_subquery as
(select product, country, year, week, inventory, sale, receipts
          from sales_fact model return updated rows partition by(product, country) dimension by(year, week) measures(0 inventory, sale, receipts) rules automatic
         order(inventory [ year, week ] = nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ]))
         select * from model_subquery where country in ('Australia')
   and product = 'Xtend Memory'
 order by product, country, year, week;

Oracle 12c可以使用with子句声明并定义PL/SQL函数和存储过程。在定义之后，可以在声明这个子句的查询（包括它的任何子查询）中引用该PL/SQL函数。

with function calc_a(pn number,pm number) return number
is
begin
  return pn*pm;
  end;
  select calc_a(10,10) a10 from dual;
/

如果不是在最顶层查询之前先声明with子句，需要使用with_plsql提示。with_plsql提示并不是优化器提示。

select /*+ with_plsql */* from  
(with function calc_a(pn number,pm number) return number
is
begin
  return pn*pm;
  end;
  select calc_a(10,10) a10 from dual);
/

在测试子查询因子化的性能时，可以在因子化子查询中分别使用inline提示和materialize提示。

with model_subquery as
(select /*+ materialize */ product, country, year, week, inventory, sale, receipts
          from sales_fact model return updated rows partition by(product, country) dimension by(year, week) measures(0 inventory, sale, receipts) rules automatic
         order(inventory [ year, week ] = nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ]))

materialize提示会将子查询当作临时表物化。inline提示不使用临时表转换。

递归子查询因子化（递归公共表表达式）：
connect by：
select level,last_name,employee_id,manager_id from hr.employees start with manager_id is null connect by prior employee_id = manager_id order siblings by last_name;
start with a.a1 is null connect by prior a.a1 = a.a2 start with指定定位点成员（最上层），connect by指定递归成员，prior运算符将当前的a1与另一行的a2列值匹配。反复这么做就建立了一个递归查询。
level伪列保存了递归的深度值。
order siblings by针对树状SQL，按照层次结构排序。
connect by函数、运算符和伪列：

• sys_connect_by_path 返回当前数据行的所有祖先（函数）
• connect_by_root 返回根数据行的值（运算符）
• prior 用来表明层级型查询，在递归子查询中不需要（运算符）
• connect_by_iscycle 在层级中检查循环（伪列）
• nocycle connect by的参数，与connect_by_iscycle一起使用（参数）
• connect_by_isleaf 标识叶子数据行（伪列）
• level 表明层级中的深度（伪列）
  level伪列经常被用来实现输出缩进。
  sys_connect_by_path返回组成层级的直到当前行的值。sys_connect_by_path(last_name,':')建立一个冒号分隔的从根到节点的层级。
  connect_by_root返回当前行的根节点。
  select level,last_name,employee_id,manager_id,connect_by_root last_name root from hr.employees start with manager_id is null connect by prior employee_id = manager_id order siblings by last_name;
  nocycle参数可以阻止发生ora-01436错误（connect by loop in user data），connect_by_iscycle显示导致错误发生的行（错误行的值为1）。
  select level,last_name,employee_id,manager_id,connect_by_iscycle from hr.employees start with manager_id is null connect by nocycle prior employee_id = manager_id order siblings by last_name;
  connect_by_isleaf用来在层级数据中识别叶子节点（没有子节点的分层树形结构中的节点）。
  RSF：

  with rsf_emp (lv,last_name,employee_id,manager_id) as
  (select 1 lv,last_name,employee_id,manager_id
  from hr.employees
  where manager_id is null
  union all
  select rsf_emp.lv+1 lv,hr.employees.last_name,hr.employees.employee_id,hr.employees.manager_id
  from hr.employees
  join rsf_emp on rsf_emp.employee_id = hr.employees.manager_id)
  search depth first by last_name set order1
  select * from rsf_emp;
  

  递归的with子句需要两个查询块：定位点成员和递归成员。这两个子查询块必须通过集合运算符union all结合在一起。定位点成员是union all之前的查询，递归成员是其后面的查询。递归子查询必须引用定义子查询--这样就进行了递归。
  不能在RSF的递归成员中使用：
• distinct关键字或group by子句；
• model子句；
• 聚合函数，但在select列表中可以使用分析函数；
• 引用query_name的子查询；
• 引用query_name作为右表的外联结。
  RSF查询返回的列必须在with rsf_emp (lv,last_name,employee_id,manager_id)定义中声明。
  RSF查询默认情况下是search breadth first：在返回任何子数据行之前返回每一层级上的兄弟数据行。而search depth first会按照层级的顺序返回数据行。

  with rsf_emp (lv,last_name,employee_id,manager_id) as
  (select 1 lv,last_name,employee_id,manager_id
  from hr.employees
  where manager_id is null
  union all
  select rsf_emp.lv+1 lv,hr.employees.last_name,hr.employees.employee_id,hr.employees.manager_id
  from hr.employees
  join rsf_emp on rsf_emp.employee_id = hr.employees.manager_id)
  search breadth first by last_name set order1
  select * from rsf_emp;
  

  search子句中的set order1部分将order1伪列的值设置为数据行返回的顺序值。
  search子句中指定了按照last_name进行搜索，也可以是其他列、列表。这样做控制了每个层级中各行的顺序。
  RSF中可通过cycle子句实现connect by中的nocycle与connect_by_iscycle组合：

  with rsf_emp (lv,last_name,employee_id,manager_id) as
  (select 1 lv,last_name,employee_id,manager_id
  from hr.employees 
  where manager_id is null
  union all
  select rsf_emp.lv+1 lv,hr.employees.last_name,hr.employees.employee_id,hr.employees.manager_id
  from hr.employees
  join rsf_emp on rsf_emp.employee_id = hr.employees.manager_id)
  search depth first by last_name set order1
  cycle employee_id set is_cycle to 1 default 0
  select * from rsf_emp;
  

  cycle employee_id set is_cycle to 1 default 0：is_cycle是自定义列名，1是允许设置的单值字符。