将DB2查询转换为oracle查询 [英] converting a DB2 query into oracle query

问题描述

以前我们使用DB2作为数据库，但现在我们正在迁移到Oracle。现在，在我们的项目中，我们广泛使用了Db2特定的sql。

有没有办法将这些DB2特定的查询转换为支持oracle的查询。

谢谢

解决方案

你有很多工作要前进！

在DB2和Oracle之间，一些重要的区别是（只是我可以想到的任意枚举）：

数据类型< h2>

• 数字数据类型：DB2有更多的标准类型，例如 SMALLINT ， INTEGER ， DOUBLE 等。在Oracle SQL中不存在（尽管有些存在于PL / SQL中）。这对于DDL和转换以及其他一些使用情况非常重要，例如谓词的正确性。


• 日期数据类型：Oracle在 DATE 和 TIMESTAMP 是事实， TIMESTAMP 有微秒。但是， DATE 也可能包含时间信息。在DB2中， DATE 没有时间信息，我想。


• 字符数据类型：阅读有关和 VARCHAR2


• NULL 。在Oracle中， NULL 比DB2中更为通用。在DB2 v9.7之前，您必须将 NULL 转换为任何显式类型，例如 cast（null as integer）。在Oracle中没有必要。

系统对象

• SYSIBM.DUAL 简单地变成 DUAL


• 功能：全都有点不同你必须逐案检查。例如， LOCATE 成为 INSTR

语法

• TRUNCATE IMMEDIATE become TRUNCATE


• EXCEPT 成为 MINUS li>
  
• DB2的 FETCH FIRST n ROWS ONLY ：Oracle中没有这样的子句。您必须使用 ROWNUM 或 ROW_NUMBER（）OVER（）过滤（请参阅此示例）


• DB2的 MERGE 语句比Oracle的功能更强大，以防您使用此功能。


• DB2支持 INSERT INTO ..（..）VALUES ..），（..），（..）。使用Oracle，您必须编写 INSERT INTO .. SELECT .. UNION ALL SELECT .. UNION ALL SELECT ..

高级

• 如果使用存储过程，它们的工作方式有所不同，特别是高级涉及的数据类型，但是这里超出了范围。

您最有效的拍摄可能是使用某种类型的SQL抽象。如果您使用Java，我建议您使用 jOOQ （我是开发人员）来包装您的SQL语句。 。 jOOQ为上述所有事实提供API级抽象。在DB2和Oracle上可以执行大量的SQL，而无需调整。

在更高级别的抽象中， Hibernate （或其他JPA实现）可以为您做同样的操作

Previously we used DB2 as database, but now we are migrating to Oracle. Now, in our project we have extensively used sql's that were Db2 specific.

Is there any way to convert those DB2 specific queries to oracle supported queries.

Thanks

解决方案

You have a lot of work ahead!

Between DB2 and Oracle, some important differences are (just an arbitrary enumeration of what I can think of):

Data types

• Number data types: DB2 has many more standard types, such as SMALLINT, INTEGER, DOUBLE, etc. Those don't exist in Oracle SQL (although some exist in PL/SQL). This is important for DDL and for casting and some other use cases, such as the correctness of predicates
• Date data types: Oracle's only difference between DATE and TIMESTAMP is the fact that TIMESTAMP has microseconds. But DATE may also contain time information. In DB2, DATE has no time information, I think.
• Character data types: Read about the difference between VARCHAR and VARCHAR2 in Oracle
• NULL. In Oracle, NULL is much more general than in DB2. Before DB2 v9.7, you had to cast NULL to any explicit type, e.g. cast(null as integer). That's not necessary in Oracle.

System objects

• SYSIBM.DUAL simply becomes DUAL
• Functions: They're all a bit different. You'll have to check case by case. For example, LOCATE becomes INSTR

Syntax

• TRUNCATE IMMEDIATE becomes TRUNCATE
• EXCEPT becomes MINUS
• DB2's FETCH FIRST n ROWS ONLY: There is no such clause in Oracle. You'll have to use ROWNUM or ROW_NUMBER() OVER() filtering (see this example)
• DB2's MERGE statement is more powerful than that of Oracle, in case you use this.
• DB2 supports INSERT INTO .. (..) VALUES (..), (..), (..). With Oracle, you'd have to write INSERT INTO .. SELECT .. UNION ALL SELECT .. UNION ALL SELECT ..

Advanced

• If you use stored procedures, they work a bit differently, especially with advanced data types involved, but that's out of scope here.

Your most efficient shot at this might be to use SQL abstraction of some sort. If you're using Java, I would recommend you wrap your SQL statements with jOOQ (of which I am the developer). jOOQ provides API-level abstraction for all of the above facts. A great deal of SQL can be executed both on DB2 and Oracle, without adaptation.

On a higher level of abstraction, Hibernate (or other JPA implementations) can do the same for you

这篇关于将DB2查询转换为oracle查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！