您需要了解的所有递归SQL

最新推荐文章于 2021-11-05 15:00:48 发布

danpu0978

最新推荐文章于 2021-11-05 15:00:48 发布

阅读量128

点赞数

文章标签：数据库 java mysql oracle 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
甲骨文

递归公用表表达式（至少受SQL标准支持的CONNECT BY ）：

DB2
火鸟
数据库
甲骨文
PostgreSQL的
SQL服务器
Sybase SQL Anywhere

和…

H2有一些实验支持

在以后的文章中，我们将研究如何使用递归CTE做同样的事情。

翻译自: https://www.javacodegeeks.com/2014/08/all-you-ever-need-to-know-about-recursive-sql.html

danpu0978

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
您需要了解的所有递归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; 现在...
复制链接

扫一扫