sql 递归 所有父
Oracle SYNONYM是一个很棒的功能。 您只需在数据库中创建SYNONYM,就可以实现各种向后兼容的调整。 考虑以下架构:
CREATE TABLE my_table (col NUMBER(7));
CREATE SYNONYM my_table_old FOR my_table;
CREATE SYNONYM my_table_bak FOR my_table_old;
现在,您可以通过三个不同的名称查询同一张旧表,这将导致相同的输出:
SELECT * FROM my_table;
-- Same thing:
SELECT * FROM my_table_old;
SELECT * FROM my_table_bak;
问题是,当您在代码中看到my_table_bak
(或什至更混淆的名称)时,您立即知道它的真正含义吗?
使用此查询找出
我们可以使用ALL_SYNONYMS表来解决这一问题。 该查询已经给出了简单的概述:
SELECT *
FROM ALL_SYNONYMS
WHERE TABLE_OWNER = 'PLAYGROUND'
输出为:
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
---------------------------------------------------
PLAYGROUND MY_TABLE_BAK PLAYGROUND MY_TABLE_OLD
PLAYGROUND MY_TABLE_OLD PLAYGROUND MY_TABLE
但是您可以看到,这很无聊,因为我们那里有可传递的同义词,我不想遍历完整的表来找出MY_TABLE_BAK -> MY_TABLE_OLD -> MY_TABLE
。
因此,让我们使用CONNECT BY!
Oracle(以及Informix和CUBRID)对于分层SQL具有出色的CONNECT BY
子句。 如果您敢的话,还可以使用功能更强大的通用表表达式来表示分层SQL 。
但是,让我们看看如何能够可传递地解析表。 这是如何做:
SELECT
s.OWNER,
s.SYNONYM_NAME,
-- Get to the root of the hierarchy
CONNECT_BY_ROOT s.TABLE_OWNER TABLE_OWNER,
CONNECT_BY_ROOT s.TABLE_NAME TABLE_NAME
FROM ALL_SYNONYMS s
WHERE s.TABLE_OWNER = 'PLAYGROUND'
-- The magic CONNECT BY clause!
CONNECT BY s.TABLE_OWNER = PRIOR s.OWNER
AND s.TABLE_NAME = PRIOR s.SYNONYM_NAME
首先,是CONNECT BY
,它允许按层次结构的前身“连接”层次结构。 在层次结构的每个级别上,我们都将TABLE_NAME
与它的先前(“ PRIOR”) SYNONYM_NAME
。 只要链没有结束(或者如果进入一个循环),这将重复进行。
有趣的是CONNECT_BY_ROOT
关键字,该关键字针对层次结构中的每个路径显示路径的根。 在我们的例子中,这就是目标TABLE_NAME
。
输出可以在这里看到:
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
---------------------------------------------------
PLAYGROUND MY_TABLE_OLD PLAYGROUND MY_TABLE
PLAYGROUND MY_TABLE_BAK PLAYGROUND MY_TABLE
PLAYGROUND MY_TABLE_BAK PLAYGROUND MY_TABLE_OLD <-- Useless
如果您对显示的记录感到困惑,只需添加LEVEL
伪列即可显示递归级别:
SELECT
-- Add level here
LEVEL,
s.OWNER,
s.SYNONYM_NAME,
CONNECT_BY_ROOT s.TABLE_OWNER TABLE_OWNER,
CONNECT_BY_ROOT s.TABLE_NAME TABLE_NAME
FROM ALL_SYNONYMS s
WHERE s.TABLE_OWNER = 'PLAYGROUND'
CONNECT BY s.TABLE_OWNER = PRIOR s.OWNER
AND s.TABLE_NAME = PRIOR s.SYNONYM_NAME
LEVEL OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
----------------------------------------------------------
1 PLAYGROUND MY_TABLE_OLD PLAYGROUND MY_TABLE
2 PLAYGROUND MY_TABLE_BAK PLAYGROUND MY_TABLE
1 PLAYGROUND MY_TABLE_BAK PLAYGROUND MY_TABLE_OLD
^^^^^^
Awesome!
使用START WITH摆脱“不良记录”
如您所见, 某些结果现在是直接指向目标表的同义词,而最后一条记录仍指向同义词路径中的中间元素。 这是因为我们从表中的每条记录,也从“中间”同义词引用(其TABLE_NAME
是另一个同义词)中,重新进入路径层次结构。
我们还使用可选的START WITH
子句来消除它们,该子句允许将树遍历限制为那些根满足给定谓词的树:
SELECT
s.OWNER,
s.SYNONYM_NAME,
CONNECT_BY_ROOT s.TABLE_OWNER TABLE_OWNER,
CONNECT_BY_ROOT s.TABLE_NAME TABLE_NAME
FROM ALL_SYNONYMS s
WHERE s.TABLE_OWNER = 'PLAYGROUND'
CONNECT BY s.TABLE_OWNER = PRIOR s.OWNER
AND s.TABLE_NAME = PRIOR s.SYNONYM_NAME
-- Start recursing only from non-synonym objects
START WITH EXISTS (
SELECT 1
FROM ALL_OBJECTS
WHERE s.TABLE_OWNER = ALL_OBJECTS.OWNER
AND s.TABLE_NAME = ALL_OBJECTS.OBJECT_NAME
AND ALL_OBJECTS.OWNER = 'PLAYGROUND'
AND ALL_OBJECTS.OBJECT_TYPE <> 'SYNONYM'
)
因此,从本质TABLE_NAME
,我们要求TABLE_NAME
必须是架构中ALL_OBJECTS
中的任何对象,而不是SYNONYM
。 (是的,同义词适用于所有对象,包括过程,程序包,类型等)
运行上面的查询将为我们提供所需的结果:
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
---------------------------------------------------
PLAYGROUND MY_TABLE_OLD PLAYGROUND MY_TABLE
PLAYGROUND MY_TABLE_BAK PLAYGROUND MY_TABLE
那PUBLIC同义词呢?
通常,您不会使用本地同义词,而会使用PUBLIC。 Oracle具有这种古怪的PUBLIC
伪模式,您不能在其中创建对象,但是可以在其中创建同义词。 因此,让我们为向后兼容的目的创建更多同义词:
CREATE PUBLIC SYNONYM my_table_bak2 FOR my_table_bak;
CREATE SYNONYM bak_backup_old FOR my_table_bak2;
不幸的是,这将打破我们的链条,因为出于某些原因,只有Oracle和Delphi的Oracle知道, PUBLIC
被很好地报告为同义词的OWNER
,而没有报告为TABLE_OWNER
。 让我们来看一些原始数据:
SELECT *
FROM ALL_SYNONYMS
WHERE TABLE_OWNER = 'PLAYGROUND'
… 因此:
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
------------------------------------------------------
PLAYGROUND MY_TABLE_OLD PLAYGROUND MY_TABLE
PLAYGROUND MY_TABLE_BAK PLAYGROUND MY_TABLE_OLD
PUBLIC MY_TABLE_BAK2 PLAYGROUND MY_TABLE_BAK
PLAYGROUND BAK_BACKUP_OLD PLAYGROUND MY_TABLE_BAK2 <-- Not PUBLIC
正如你所看到的, PUBLIC SYNONYM MY_TABLE_BAK2
据报道,在PLAYGROUND
的模式! 当然,这破坏了递归。 我们缺少一条记录:
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
------------------------------------------------------
PLAYGROUND MY_TABLE_OLD PLAYGROUND MY_TABLE
PLAYGROUND MY_TABLE_BAK PLAYGROUND MY_TABLE
PUBLIC MY_TABLE_BAK2 PLAYGROUND MY_TABLE <-- Hmm?
为了解决此问题,我们必须调整原始数据集。 (TABLE_OWNER, TABLE_NAME)
任何报告为(TABLE_OWNER, TABLE_NAME)
可能是名为('PUBLIC', TABLE_NAME)
的同义词。 因此,诀窍就是这样简单地复制所有输入数据:
SELECT
s.OWNER,
s.SYNONYM_NAME,
CONNECT_BY_ROOT s.TABLE_OWNER TABLE_OWNER,
CONNECT_BY_ROOT s.TABLE_NAME TABLE_NAME
-- Tweaked data set
FROM (
SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROM ALL_SYNONYMS
UNION ALL
SELECT OWNER, SYNONYM_NAME, 'PUBLIC', TABLE_NAME
FROM ALL_SYNONYMS
) s
-- Add the synthetic PUBLIC TABLE_OWNER as well
WHERE s.TABLE_OWNER IN (
'PLAYGROUND', 'PUBLIC'
)
CONNECT BY s.TABLE_OWNER = PRIOR s.OWNER
AND s.TABLE_NAME = PRIOR s.SYNONYM_NAME
START WITH EXISTS (
SELECT 1
FROM ALL_OBJECTS
WHERE s.TABLE_OWNER = ALL_OBJECTS.OWNER
AND s.TABLE_NAME = ALL_OBJECTS.OBJECT_NAME
AND ALL_OBJECTS.OWNER = 'PLAYGROUND'
AND ALL_OBJECTS.OBJECT_TYPE <> 'SYNONYM'
)
在那里,我们的失踪记录!
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
---------------------------------------------------
PLAYGROUND MY_TABLE_OLD PLAYGROUND MY_TABLE
PLAYGROUND MY_TABLE_BAK PLAYGROUND MY_TABLE
PUBLIC MY_TABLE_BAK2 PLAYGROUND MY_TABLE
PLAYGROUND BAK_BACKUP_OLD PLAYGROUND MY_TABLE <-- Yep!
显示层次结构
还有一个称为SYS_CONNECT_BY_PATH
的古怪功能,可用于以字符串形式(VARCHAR2,最多4000个字符!)实际显示整个层次结构。 这是如何做:
SELECT
-- Magic function
SUBSTR(
sys_connect_by_path(
s.TABLE_OWNER
|| '.'
|| s.TABLE_NAME, ' <- '
) || ' <- '
|| s.OWNER
|| '.'
|| s.SYNONYM_NAME, 5
)
FROM (
SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROM ALL_SYNONYMS
UNION ALL
SELECT OWNER, SYNONYM_NAME, 'PUBLIC', TABLE_NAME
FROM ALL_SYNONYMS
) s
WHERE s.TABLE_OWNER IN (
'PLAYGROUND', 'PUBLIC'
)
CONNECT BY s.TABLE_OWNER = PRIOR s.OWNER
AND s.TABLE_NAME = PRIOR s.SYNONYM_NAME
START WITH EXISTS (
SELECT 1
FROM ALL_OBJECTS
WHERE s.TABLE_OWNER = ALL_OBJECTS.OWNER
AND s.TABLE_NAME = ALL_OBJECTS.OBJECT_NAME
AND ALL_OBJECTS.OWNER = 'PLAYGROUND'
AND ALL_OBJECTS.OBJECT_TYPE <> 'SYNONYM'
)
上面的查询现在将输出以下记录:
PLAYGROUND.MY_TABLE <- PLAYGROUND.MY_TABLE_OLD
PLAYGROUND.MY_TABLE <- PLAYGROUND.MY_TABLE_OLD <- PLAYGROUND.MY_TABLE_BAK
PLAYGROUND.MY_TABLE <- PLAYGROUND.MY_TABLE_OLD <- PLAYGROUND.MY_TABLE_BAK <- PUBLIC.MY_TABLE_BAK2
PLAYGROUND.MY_TABLE <- PLAYGROUND.MY_TABLE_OLD <- PLAYGROUND.MY_TABLE_BAK <- PUBLIC.MY_TABLE_BAK2 <- PLAYGROUND.BAK_BACKUP_OLD
令人印象深刻,是吗?
备注:如果您有过时的同义词
如果您拥有“陈旧的”同义词,即没有指向任何地方的同义词,则Oracle可能会报告它们指向自己。 不幸的是,在CONNECT BY
创建了一个CYCLE。 为防止这种情况发生,只需添加另一个谓词,如下所示:
SELECT
SUBSTR(
sys_connect_by_path(
s.TABLE_OWNER
|| '.'
|| s.TABLE_NAME, ' <- '
) || ' <- '
|| s.OWNER
|| '.'
|| s.SYNONYM_NAME, 5
)
FROM (
SELECT * FROM (
SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROM ALL_SYNONYMS
UNION ALL
SELECT OWNER, SYNONYM_NAME, 'PUBLIC', TABLE_NAME
FROM ALL_SYNONYMS
) s
-- Add this predicate to prevent cycles
WHERE (s.OWNER , s.SYNONYM_NAME)
!= ((s.TABLE_OWNER , s.TABLE_NAME))
) s
CONNECT BY s.TABLE_OWNER = PRIOR s.OWNER
AND s.TABLE_NAME = PRIOR s.SYNONYM_NAME
START WITH EXISTS (
SELECT 1
FROM ALL_OBJECTS
WHERE s.TABLE_OWNER = ALL_OBJECTS.OWNER
AND s.TABLE_NAME = ALL_OBJECTS.OBJECT_NAME
AND ALL_OBJECTS.OWNER = 'PLAYGROUND'
AND ALL_OBJECTS.OBJECT_TYPE <> 'SYNONYM'
)
上面的查询可以用jOOQ编写吗?
当然是。 在jOOQ中 ,如果可以用SQL编写,几乎一切皆有可能。 这是我们使用与上面类似的查询来解析jOOQ代码生成器中的Oracle Synonmys的方式:
// Some reusable variables
AllObjects o = ALL_OBJECTS;
AllSynonyms s1 = ALL_SYNONYMS;
AllSynonyms s2 = ALL_SYNONYMS.as("s2");
AllSynonyms s3 = ALL_SYNONYMS.as("s3");
Field<String> dot = inline(".");
String arr = " <- ";
// The actual qeury
DSL
.using(configuration)
.select(
s3.OWNER,
s3.SYNONYM_NAME,
connectByRoot(s3.TABLE_OWNER).as("TABLE_OWNER"),
connectByRoot(s3.TABLE_NAME).as("TABLE_NAME"),
substring(
sysConnectByPath(
s3.TABLE_OWNER.concat(dot)
.concat(s3.TABLE_NAME),
arr
)
.concat(arr)
.concat(s3.OWNER)
.concat(dot)
.concat(s3.SYNONYM_NAME),
5
))
.from(
select()
.from(
select(
s1.OWNER, s1.SYNONYM_NAME,
s1.TABLE_OWNER, s1.TABLE_NAME)
.from(s1)
.union(
select(
s1.OWNER, s1.SYNONYM_NAME,
inline("PUBLIC"), s1.TABLE_NAME)
.from(s1))
.asTable("s2"))
.where(row(s2.OWNER, s2.SYNONYM_NAME)
.ne(s2.TABLE_OWNER, s2.TABLE_NAME))
.asTable("s3"))
.connectBy(s3.TABLE_OWNER.eq(prior(s3.OWNER)))
.and(s3.TABLE_NAME.eq(prior(s3.SYNONYM_NAME)))
.startWith(exists(
selectOne()
.from(o)
.where(s3.TABLE_OWNER.eq(o.OWNER))
.and(s3.TABLE_NAME.eq(o.OBJECT_NAME))
.and(o.OBJECT_TYPE.ne("SYNONYM"))
.and(o.OWNER.in(getInputSchemata()))
))
.fetch();
立即下载jOOQ并自己尝试!
结论
如果您拥有一个本质上分层的数据集,那么您将对这些简单的分层SQL功能(以及Commont表表达式)感到不满意。 它们的表现不佳,如果层次结构变得更加复杂,则很难表达。 因此,您不妨考虑使用像Neo4j这样的实际图形数据库。
但是,时不时地,一些层次结构可能会潜入您本来是“标准”的关系数据模型中。 这样做时,请确保已准备好此有用的CONNECT BY
子句以采取措施。
CONNECT BY
受(至少)支持:
- BR
- Informix
- Oracle
递归公用表表达式(至少受SQL标准支持的CONNECT BY
):
- DB2
- 火鸟
- 数据库
- Oracle
- PostgreSQL
- SQL服务器
- Sybase SQL Anywhere
和…
- H2有一些实验支持
在以后的文章中,我们将研究如何使用递归CTE做同样的事情。
翻译自: https://www.javacodegeeks.com/2014/08/all-you-ever-need-to-know-about-recursive-sql.html
sql 递归 所有父