Microsoft T-SQL 到 Oracle SQL 的翻译

Microsoft T-SQL to Oracle SQL translation

提问by Michael Prewecki

I've worked with T-SQL for years but I've just moved to an organisation that is going to require writing some Oracle stuff, probably just simple CRUD operations at least until I find my feet. I'm not going to be migrating databases from one to the other simply interacting with existing Oracle databases from an Application Development perspective. Is there are tool or utility available to easily translate T-SQL into Oracle SQL, a keyword mapper is the sort of thing I'm looking for.

我已经使用 T-SQL 工作多年，但我刚刚搬到一个需要编写一些 Oracle 内容的组织，可能只是简单的 CRUD 操作，至少在我站稳脚跟之前。我不会从应用程序开发的角度简单地将数据库从一个迁移到另一个，而只是与现有的 Oracle 数据库进行交互。是否有工具或实用程序可以轻松地将 T-SQL 转换为 Oracle SQL，关键字映射器是我正在寻找的那种东西。

P.S. I'm too lazy to RTFM, besides it's not going to be a big part of my role so I just want something to get me up to speed a little faster.

PS 我懒得使用 RTFM，此外它不会成为我角色的重要组成部分，所以我只想让我加快速度。

回答by

The language difference listed so far are trivialcompared to the logical differences. Anyone can lookup NVL. What's hard to lookup is

与逻辑差异相比，到目前为止列出的语言差异微不足道。任何人都可以查找 NVL。难查的是

DDL

数据线

In SQL server you manipulate your schema, anywhere, anytime, with little or no fuss.

在 SQL Server 中，您可以随时随地操作您的架构，几乎没有大惊小怪。

In Oracle, we don't like DDL in stored procedures so you have jump through hoops. You need to use EXECUTE IMMEDIATE to perform a DDL function.

在 Oracle 中，我们不喜欢存储过程中的 DDL，因此您必须跳过障碍。您需要使用 EXECUTE IMMEDIATE 来执行 DDL 功能。

Temp Tables

临时表

IN SQL Server when the logic becomes a bit tough, the common thing is to shortcut the sql and have it resolved to a temp table and then the next step is done using that temp table. MSSS makes it very easy to do this.

在 SQL Server 中，当逻辑变得有点困难时，常见的做法是将 sql 快捷化并将其解析为临时表，然后使用该临时表完成下一步。MSSS 可以很容易地做到这一点。

In Oracle we don't like that. By forcing an intermediate result you completely prevent the Optimizer from finding a shortcut for you. BUT If you must stop halfway and persist the intermediate results Oracle wants you to make the temp table in advance, not on the fly.

在 Oracle 中，我们不喜欢那样。通过强制中间结果，您可以完全阻止优化器为您找到快捷方式。但是，如果您必须中途停止并保留中间结果，Oracle 希望您提前制作临时表，而不是即时制作。

Locks

锁

In MSSS you worry about locking, you have nolock hints to apply to DML, you have lock escalation to reduce the count of locks.

在 MSSS 中，您担心锁定，您可以将 nolock 提示应用于 DML，您可以通过锁升级来减少锁的数量。

In Oracle we don't worry about these in that way.

在 Oracle 中，我们不会以这种方式担心这些。

Read Commited

读已提交

Until recently MSSS didn't fully handle Read Committed isolation so you worried about dirty reads.

直到最近，MSSS 还没有完全处理已提交读隔离，因此您担心脏读。

Oracle has been that way for decades.

几十年来，Oracle 一直如此。

etc

等等

MSSS has no concept of Bitmap indexes, IOT, Table Clusters, Single Table hash clusters, non unique indexes enforcing unique constraints....

MSSS 没有位图索引、IOT、表簇、单表哈希簇、强制唯一约束的非唯一索引的概念......

回答by Peter

I get the impression most answers focus on migrating an entire database or just point to some differences between T-SQL and PL/SQL. I recently had the same problem. The Oracle database exists, but I need to convert a whole load of T-SQL scripts to PL/SQL.

我的印象是大多数答案都集中在迁移整个数据库上，或者只是指出 T-SQL 和 PL/SQL 之间的一些差异。我最近遇到了同样的问题。Oracle 数据库存在，但我需要将整个负载的 T-SQL 脚本转换为 PL/SQL。

I installed Oracle SQL Developerand ran the Translation Scratch Editor(Tools > Migration > Translation Scratch Editor).

我安装了Oracle SQL Developer并运行了Translation Scratch Editor（工具 > 迁移 > Translation Scratch Editor）。

Then, just enter your T-SQL, choose the correct translation in the dropdownlist (it should default to 'T-SQL to PL/SQL'), and convert it.

然后，只需输入您的 T-SQL，在下拉列表中选择正确的翻译（默认为“T-SQL 到 PL/SQL”），然后进行转换。

回答by Mark Breen

I have to things to mention.

我有话要说。

1) When I worked on Oracle 8, you could not do "Select @Result", you had to instead use the dummy table as follows "Select @Result from dual". Not sure if that ridiculousness still exists.

1）当我在 Oracle 8 上工作时，您无法执行“Select @Result”，您必须改为使用虚拟表，如下所示“Select @Result from dual”。不知道这种荒谬是否还存在。

2) In the Oracle world they seem to love cursors and you better read up on them, they use them all the time AFAICS.

2) 在 Oracle 世界中，他们似乎喜欢游标，您最好仔细阅读它们，他们一直在使用 AFAICS。

Good luck and enjoy,

祝你好运和享受，

it is not that different to MS SQL. Thankfully, I do not have to work with it anymore and I am back in the warm comfort of MS tools.

它与 MS SQL 没有什么不同。谢天谢地，我不必再使用它了，我又回到了 MS 工具的温暖舒适中。

回答by ninesided

If you replace your ISNULL and NVL nonsense with COALESCE, it'll work in T-SQL and PL/SQL!

如果您用 COALESCE 替换 ISNULL 和 NVL 废话，它将在 T-SQL 和 PL/SQL 中工作！

回答by Tom Clark AaiCanHelp.com

I have done a few SQL server to oracle migrations. There is no way to migrate without rewriting the backend code. Too many differences between the 2 databases and more importantly differences between the 2 mind sets of the programmers. Many managers think that the 2 are interchangeable, I have had managers ask me to copy the stored procedures from SQL server and compile them in oracle, not a clue! Toad is by far the best tool on the market for supporting an oracle application. SQL developer is ok but was disappointing compared to toad. I hope that oracle will catch their product up to toad one day but it is not there yet. Have a good day :) chances are if you are migrating to oracle it is for a reason and in order to meet that requirement you will need to rewrite the back end code or you will have many issues.

我已经完成了一些 SQL 服务器到 oracle 的迁移。不重写后端代码就没有办法迁移。两个数据库之间的差异太多，更重要的是程序员的两种思维方式之间的差异。很多经理认为这两个是可以互换的，我有经理让我从SQL Server复制存储过程并在oracle中编译它们，没有任何线索！Toad 是迄今为止市场上支持 Oracle 应用程序的最佳工具。SQL 开发人员还可以，但与 toad 相比令人失望。我希望 oracle 有一天能赶上他们的产品，但现在还没有。祝您有美好的一天 :) 如果您迁移到 oracle 是有原因的，为了满足该要求，您将需要重写后端代码，否则您会遇到很多问题。

回答by jodonnell

It's not trivial to map them back and forth, so I doubt there's a tool that does it automatically. But this link might help you out: http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm

来回映射它们并非易事，所以我怀疑是否有一种工具可以自动完成。但是这个链接可能会帮助你：http: //vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm

回答by vzczc

The most important differences for plain T-SQL are:

纯 T-SQL 最重要的区别是：

• NVL replaces ISNULL
• SYSDATE replaces GETDATE()
• CONVERT is not supported
• Identity columns must be replaced with sequences <-- not technically T- or PL/ but just SQL

• NVL 替换 ISNULL
• SYSDATE 替换 GETDATE()
• 不支持转换
• 标识列必须替换为序列 <-- 技术上不是 T- 或 PL/，而只是 SQL

Note. I assume you do not use the deprecated SQL Server *= syntax for joins

笔记。我假设您不使用已弃用的 SQL Server *= 语法进行连接

@jodonell: The table you link to is a bit outdated, oracle has become somewhat more standards compliant after 9i supporting things like CASE and ANSI outer joins

@jodonell：您链接到的表有点过时，在 9i 支持 CASE 和 ANSI 外连接等内容之后，oracle 变得更加符合标准

回答by tlhong

In Oracle SQL Developer, there is a tool called Translation Scratch Editor. You can find it from Tools > Migration.

在 Oracle SQL Developer 中，有一个名为 Translation Scratch Editor 的工具。您可以从工具 > 迁移中找到它。

The Oracle SQL Developer is a free download from Oracle and it is an easy install.

Oracle SQL Developer 可从 Oracle 免费下载，并且易于安装。

回答by Lukas Eder

jOOQ has a publicly available, free translator, which can be accessed from the website here: https://www.jooq.org/translate

jOOQ 有一个公开可用的免费翻译器，可以从这里的网站访问：https://www.jooq.org/translate

It supports DML, DDL, and a few procedural syntax elements. If you want to run the translation locally via command line, a license can be purchased and the command line works as follows:

它支持 DML、DDL 和一些程序语法元素。如果您想通过命令行在本地运行翻译，可以购买许可证，命令行的工作方式如下：

$ java -cp jooq-3.11.9.jar org.jooq.ParserCLI -t ORACLE -s "SELECT substring('abcde', 2, 3)"
select substr('abcde', 2, 3) from dual;

See: https://www.jooq.org/doc/latest/manual/sql-building/sql-parser/sql-parser-cli

请参阅：https: //www.jooq.org/doc/latest/manual/sql-building/sql-parser/sql-parser-cli

(Disclaimer, I work for the vendor)

（免责声明，我为供应商工作）

回答by Thomas Jones-Low

If you're doing a one-off conversion, rather than trying to support two versions, you must look at Oracle Migration Workbench. This tool works with Oracle's SQLDeveloper (which you really should have if you are working with Oracle). This does a conversion of the schema, data, and some of the T-SQL to PL/SQL. Knowing both well, I found it did about an 80% job. Good enough to make it worth while to convert the bulk of procedures, and hand convert the remainder "tougher" unknown parts.

如果您要进行一次性转换，而不是尝试支持两个版本，则必须查看Oracle Migration Workbench。该工具可与 Oracle 的 SQLDeveloper 一起使用（如果您正在使用 Oracle，您确实应该拥有它）。这会将模式、数据和一些 T-SQL 转换为 PL/SQL。对两者都了如指掌，我发现它完成了大约 80% 的工作。足够好，值得花时间转换大部分程序，并手动转换剩余的“更难”的未知部分。