Oracle（旧？）加入 - 用于转换的工具/脚本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2425960/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle (Old?) Joins - A tool/script for conversion?
提问by mainstringargs
I have been porting oracle selects, and I have been running across a lot of queries like so:
我一直在移植 oracle selects，我遇到了很多这样的查询：
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..
更好.. 是否有工具/脚本可以为我进行此转换（首选免费）？某种优化器？我有大约 500 个这样的查询要移植..
When was this standard phased out? Any info is appreciated.
这个标准是什么时候淘汰的？任何信息表示赞赏。
回答by OMG Ponies
The (+)is Oracle specific pre-ANSI-92 OUTER JOIN syntax, because ANSI-89 syntax doesn't provide syntax for OUTER JOINsupport.
这(+)是 Oracle 特定的 pre-ANSI-92 OUTER JOIN 语法，因为 ANSI-89 语法不提供语法OUTER JOIN支持。
Whether it is RIGHTor LEFTis 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 FROMclause - it's a RIGHTjoin. Otherwise, it's a LEFTjoin. This a good reference for anyone needing to know the difference between JOINs.
无论是RIGHT或LEFT由哪个表＆列参考符号被附加到确定。如果在与FROM子句中第一个表相关联的列旁边指定它- 它是一个RIGHT连接。否则，它是一个LEFT连接。 对于需要了解 JOIN 之间区别的任何人来说，这是一个很好的参考。
First query re-written using ANSI-92 syntax:
使用 ANSI-92 语法重写的第一个查询：
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:
使用 ANSI-92 语法重写的第二个查询：
SELECT e.lastname,
d.department_name
FROM EMPLOYEES e
LEFT JOIN DEPARTMENTS d ON d.departmentid = e.departmentid
回答by FrustratedWithFormsDesigner
Google "Oracle join syntax". the (+)is used for different flavours of outer joins. I think the first one you showed is a Left Outer Join, and the second one is a Right Outer Join. I haven't seen this notation for quite a while, so I could be a little off, but hopefully, this gives you enough info to hit Google and get the right answer.
谷歌“Oracle 连接语法”。该(+)被用于外连接不同的口味。我认为您展示的第一个是左外连接，第二个是右外连接。我已经有一段时间没有看到这个符号了，所以我可能有点不对劲，但希望这能给你足够的信息来点击谷歌并得到正确的答案。
UPDATE:
更新：
So you wants a tool to do it for you? I have heard that SwisSQLcan do something like this, but if most of the queries are simple enough you can probably write a little script that does it for you. OMG Ponies answer nicely shows the pattern for converting from old to new syntax.
所以你想要一个工具来为你做这件事？我听说SwisSQL可以做这样的事情，但是如果大多数查询都足够简单，您可能可以编写一个小脚本来为您完成。OMG Ponies 的回答很好地展示了从旧语法转换为新语法的模式。
回答by Unreason
This can get quite complicated as the WHERE clause in even simple situations such as
即使在简单的情况下，这也会变得非常复杂，因为 WHERE 子句在诸如
WHERE e.id = d.manager_id(+) OR e.id = d.contact_id(+)
will translate to UNION or subselect query.
将转换为 UNION 或 subselect 查询。
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.
如果你喜欢 python，你可以看看sqlparse，它看起来很有希望，你可能会得到它做你需要做的事情，再加上一些 SQL 代码的重新格式化。它很容易直接在源上工作。您必须告诉它该做什么，但它确实让您无需编写枯燥的解析部分。
回答by Bill Karwin
I don't know of a tool to do the conversion automatically, but even if there were, you would want to review its changes one a case by case basis anyway. Therefore, I don't think a tool would save you much time.
我不知道有什么工具可以自动进行转换，但即使有，您仍然希望逐个查看其更改。因此，我认为工具不会为您节省很多时间。
http://www.dba-oracle.com/oracle_news/2004_2_19_rittman.htmsays:
http://www.dba-oracle.com/oracle_news/2004_2_19_rittman.htm说：
There's no performance benefit or hit by using ANSI joins rather than traditional joins, but by using ANSI joins, your queries are more portable between DBMS platforms, and they're a bit easier to read. In the end, though, it's down to personal preference and whilst there's advantages to the ANSI standard, there's no need to switch if you don't want to.
使用 ANSI 连接而不是传统连接没有性能优势或影响，但是通过使用 ANSI 连接，您的查询在 DBMS 平台之间更具可移植性，并且更易于阅读。不过，归根结底，这取决于个人喜好，虽然 ANSI 标准有优势，但如果您不想切换，则无需切换。
Also you don't have to use one style or the other everywhere. You can convert your code one query at a time, with some assurance that they will all keep working. I would leave the queries as they are, and resolve to use ANSI SQL-92 syntax in new code.
此外，您不必在任何地方都使用一种样式或另一种样式。您可以一次转换您的代码一个查询，并保证它们都将继续工作。我会保留查询原样，并决定在新代码中使用 ANSI SQL-92 语法。
回答by Shizzmo
For Oracle 10g
对于 Oracle 10g
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#i2054012
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#i2054012
You can find the other versions of oracle manuals online if your version is different, though the join syntax is probably not different.
如果您的版本不同，您可以在线找到其他版本的 oracle 手册，但连接语法可能没有不同。
回答by Jason M
I seem to remember this syntax going away in the transition from Oracle 8i to 9i -- I think we had a toad plugin that ended up converting everything for us just so we didn't have to waste time going through every query
我似乎记得这种语法在从 Oracle 8i 到 9i 的过渡中消失了——我想我们有一个蟾蜍插件，它最终为我们转换了所有东西，这样我们就不必浪费时间来处理每个查询
