Oracle（旧？）连接 - 用于转换的工具/脚本？

发布于 2024-08-25 03:49:31 字数 537 浏览 6 评论 0原文

我一直在移植 oracle selects，并且运行过很多查询，如下所示：

SELECT e.last_name,
       d.department_name
  FROM employees e,
      departments d
WHERE e.department_id(+) = d.department_id;

...并且：

SELECT last_name, 
       d.department_id
  FROM employees e, 
       departments d
 WHERE e.department_id = d.department_id(+);

是否有任何指南/教程用于转换 (+) 语法的所有变体？该语法甚至被称为什么（这样我就可以搜索谷歌）？

更好..是否有一个工具/脚本可以为我进行此转换（首选免费）？某种优化器？我有大约 500 个要移植的查询。

这个标准何时被淘汰？任何信息表示赞赏。

原文

I have been porting oracle selects, and I have been running across a lot of queries like so:

SELECT e.last_name,
       d.department_name
  FROM employees e,
      departments d
WHERE e.department_id(+) = d.department_id;

...and:

SELECT last_name, 
       d.department_id
  FROM employees e, 
       departments d
 WHERE e.department_id = d.department_id(+);

Are there any guides/tutorials for converting all of the variants of the (+) syntax? What is that syntax even called (so I can scour google)?

Even better.. Is there a tool/script that will do this conversion for me (Preferred Free)? An optimizer of some sort? I have around 500 of these queries to port..

When was this standard phased out? Any info is appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

╭ゆ眷念 2024-09-01 03:49:31

(+) 是 Oracle 特定于 ANSI-92 之前的 OUTER JOIN 语法，因为 ANSI-89 语法不提供 OUTER JOIN 支持的语法。

是RIGHT还是LEFT由哪个表&决定。附有符号的列引用。如果它在与 FROM 子句中的第一个表关联的列旁边指定 - 它是 RIGHT 连接。否则，它是LEFT 连接。对于任何需要了解的人来说这是一个很好的参考JOIN 之间的区别。

使用 ANSI-92 语法重写的第一个查询：

    SELECT e.lastname,
           d.department_name
      FROM EMPLOYEES e
RIGHT JOIN DEPARTMENTS d ON d.departmentid = e.departmentid

使用 ANSI-92 语法重写的第二个查询：

   SELECT e.lastname,
          d.department_name
     FROM EMPLOYEES e
LEFT JOIN DEPARTMENTS d ON d.departmentid = e.departmentid

The (+) is Oracle specific pre-ANSI-92 OUTER JOIN syntax, because ANSI-89 syntax doesn't provide syntax for OUTER JOIN support.

Whether it is RIGHT or LEFT is determined by which table & column reference the notation is attached to. If it is specified next to a column associated with the first table in the FROM clause - it's a RIGHT join. Otherwise, it's a LEFT join. This a good reference for anyone needing to know the difference between JOINs.

First query re-written using ANSI-92 syntax:

    SELECT e.lastname,
           d.department_name
      FROM EMPLOYEES e
RIGHT JOIN DEPARTMENTS d ON d.departmentid = e.departmentid

Second query re-written using ANSI-92 syntax:

   SELECT e.lastname,
          d.department_name
     FROM EMPLOYEES e
LEFT JOIN DEPARTMENTS d ON d.departmentid = e.departmentid

回复收藏 0 原文

夜清冷一曲。 2024-09-01 03:49:31

谷歌“Oracle 连接语法”。 (+) 用于不同风格的外连接。我认为您展示的第一个是左外连接，第二个是右外连接。我已经有一段时间没有看到这个符号了，所以我可能有点偏离，但希望这能给你足够的信息来谷歌并得到正确的答案。

更新：

那么您想要一个工具来为您完成这项工作吗？我听说 SwisSQL 可以做这样的事情，但是如果大多数查询足够简单，您可能可以编写一个小脚本来为您完成它。 OMG Ponies 的答案很好地展示了从旧语法转换为新语法的模式。

回复收藏 0 原文

幸福不弃 2024-09-01 03:49:31

这可能会变得非常复杂，因为即使在简单的情况下，WHERE 子句

WHERE e.id = d.manager_id(+) OR e.id = d.contact_id(+)

也会转换为 UNION 或子选择查询。

如果你喜欢 python，你可以看看 sqlparse，它看起来很有前途，你可能会让它执行您需要的操作，并重新格式化 SQL 代码。它可以轻松地直接在源上工作。您必须告诉它要做什么，但它确实可以让您免于编写无聊的解析部分。

This can get quite complicated as the WHERE clause in even simple situations such as

WHERE e.id = d.manager_id(+) OR e.id = d.contact_id(+)

will translate to UNION or subselect query.

If you like python you might take a look at sqlparse, it looks promising and you might get it to do what you need plus some reformatting of the SQL code. It would easily work directly on the source. You'll have to tell it what to do but it does relieve you of writing the boring parsing part.

回复收藏 0 原文