视图中的 Oracle 分层查询，将"开头为"参数绑定到查询

Question

我有一个列为的表

parent_key1, parent_key2, child_key1, child_key2

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

这个表相当大，它包含了数千个根对象，即不作为子对象出现的父对象；也就是说，表中没有一棵树，而是一片森林。这就是为什么这里的查询不起作用。

我想得到以top_arcestor_key1和top_arcester_key2开头的树成员。

对于一个过程，我可以定义两个参数：top_arcestor_key1和：top_acestor_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_arcestor_key1、top_arcester_key2、parent_key1、parent_key 2、child_key1和child_key2[，level]

我可以将结果用于top_arcestor_key1和top_arcester_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_arcestor_key1、top_arcester_key2链接到视图？

Answer 1

如果您使用的是19.6以后的版本，则可以使用SQL表宏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);

如果到目前为止你还没有修补/升级，那么你可以用流水线表功能自己动手做，这需要付出更多的努力：

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=流水线%20Table%20Functions%201%20Table+20Functions。%20表格%20函数，流水线%20表格%20功能。%20…%208%20改造%20管道。%20，本质上，它们只允许您从PL/SQL代码中管道输出行。按照我写它的方式，它将一次从参数化光标中提取一行，您可以付出更多的努力，并使用具有合理限制的循环bulk collect。

这不会给您带来与SQL表宏完全相同的效果。宏可以与查询的其余部分合并并优化，管道函数仅作为未合并的函数存在。