Oracle分层查询在视图中，绑定«开始»参数进行查询

Question

我有一个包含列的表

parent_key1, parent_key2, child_key1, child_key2

通过两对参数的连接来定义树。

表非常大，它包含数千个根对象，也就是不会作为子对象出现的父对象；所以可以说，表中包含的不是树，而是林。这就是查询here不起作用的原因。

我希望获得从top_ancestor_key1和top_ancestor_key2开始的树成员。

对于一个过程，我可以定义两个参数:top_ancestor_key1和:top_ancestor_key2，即代码

SELECT parent_key1, parent_key2, child_key1, child_key2, level
FROM genealogy
START WITH parent_key1 = :top_ancestor_key1, parent_key2 = :top_ancestor_key2, 
CONNECT BY parent_key1 = PRIOR child_key1 AND parent_key2 = PRIOR child_key2

效果很好。

现在，我想创建一个包含列的视图«ancestors_resolved»

top_ancestor_key1、top_ancestor_key2、parent_key1、parent_key2、child_key1、child_key2、level

我可以在top_ancestor_key1和top_ancestor_key2上使用连接结果

我试过了

--CREATE View ancestors_resolved AS
SELECT connect_by_root parent_key1 as top_ancestor_key1, connect_by_root parent_key2 as top_ancestor_key2, parent_key1, parent_key2, child_key1, child_key2, level
FROM genealogy
CONNECT BY parent_key1 = PRIOR child_key1 AND parent_key2 = PRIOR child_key2

但是，封闭的查询

SELECT * FROM 
(
SELECT connect_by_root parent_key1 as top_ancestor_key1, connect_by_root parent_key2 as top_ancestor_key2, parent_key1, parent_key2, child_key1, child_key2, level
FROM genealogy
CONNECT BY parent_key1 = PRIOR child_key1 AND parent_key2 = PRIOR child_key2
)
WHERE top_ancestor_key1='grandpa' AND top_ancestor_key2 = 5

遇到超时；这看起来像是oracle在评估参数之前尝试构建所有树。

我也试过了

WITH tmptbl (parent_key1, parent_key2, child_key1, child_key2) as (
SELECT parent_key1, parent_key2, child_key1, child_key2
FROM genealogy
  UNION ALL
  SELECT tmptbl.parent_key1, tmptbl.parent_key2, tmptbl.child_key1, tmptbl.child_key2
  FROM tmptbl
  INNER JOIN genealogy x on x.child_key1 = tmptbl.parent_key1 and x.child_key2 = tmptbl.parent_key2 and x.child_key1 != x.parent_key1 and x.child_key2 != x.parent_key2
)
SELECT *
FROM tmptbl

但它也不起作用。

如何将用于START WITH子句的参数top_ancestor_key1、top_ancestor_key2链接到视图？

Answer 1

如果您使用的是19.6版本，则可以使用SQL Table Macro https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-language-elements.html#GUID-292C3A17-2A4B-4EFB-AD38-68DF6380E5F7创建参数化视图。将初始查询与绑定变量一起使用(修复拼写错误)

create or replace function tm_genealogy (nParentKey1 number, nParentKey2 number)
return varchar2 sql_macro
is
begin
  return 'SELECT parent_key1, parent_key2, child_key1, child_key2, level
FROM genealogy
START WITH parent_key1 = nParentKey1 and parent_key2 = nParentKey2
CONNECT BY parent_key1 = PRIOR child_key1 AND parent_key2 = PRIOR child_key2';
end tm_genealogy;
/
select *
from   tm_genealogy (1,2);

如果你还没有打好补丁/升级到那个程度，那么你可以用一个流水线化的表函数来DIY它，这需要更多的努力：

create or replace package genealogy_pkg 
is
  type udt is record 
  (parent_key1 number
  ,parent_key2 number
  ,child_key1  number
  ,child_key2  number
  ,lvl         number
  );
  type udt_t is table of udt;
  
  function connect_by(nParentKey1 number, nParentKey2 number) return udt_t PIPELINED;
end genealogy_pkg;
/
show err
create or replace package body genealogy_pkg 
is
  function connect_by(nParentKey1 number, nParentKey2 number) return udt_t PIPELINED
  is
    cursor connect_by_cursor (nParentKey1 number, nParentKey2 number) 
    is SELECT parent_key1, parent_key2, child_key1, child_key2, level lvl
       FROM genealogy
       START WITH parent_key1 = nParentKey1 and parent_key2 = nParentKey2
       CONNECT BY parent_key1 = PRIOR child_key1 AND parent_key2 = PRIOR child_key2;
    temp_results   udt;
  begin 
    open connect_by_cursor (nParentKey1 , nParentKey2 ) ;
    loop
      fetch connect_by_cursor
      into  temp_results;
      exit when connect_by_cursor%notfound;
          
      pipe row (temp_results);
    end loop;
    return;
       
  end connect_by;
end genealogy_pkg;
/
show err
select * from table(genealogy_pkg.connect_by(1,2));

您可以在https://oracle-base.com/articles/misc/pipelined-table-functions#:~:text=Pipelined%20Table%20Functions%201%20Table%20Functions.%20Table%20functions,Pipelined%20Table%20Functions.%20...%208%20Transformation%20Pipelines.%20中阅读有关管道函数的内容，本质上，它们只允许您通过管道从PL/SQL代码片段中输出行。按照我写的方式，它将一次一行地从参数化游标中读取，你可以做一些进一步的工作，并使用具有合理限制的循环bulk collect。

这不会给你带来与SQL表宏相同的效果。宏可以与查询的其余部分合并并进行优化，流水线函数仅作为非合并函数存在。