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

问题描述

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

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.

有什么方法可以将这些 DB2 特定查询转换为 oracle 支持的查询.

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

谢谢

推荐答案

你还有很多工作要做！

在 DB2 和 Oracle 之间，一些重要的区别是(只是我能想到的任意列举):

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

• 数字数据类型:DB2还有很多标准类型，如SMALLINT、INTEGER、DOUBLE等，这些都不存在在 Oracle SQL 中(虽然有些存在于 PL/SQL 中).这对于 DDL 和强制转换以及其他一些用例(例如谓词的正确性)很重要
• 日期数据类型:Oracle DATE 和 TIMESTAMP 之间的唯一区别是 TIMESTAMP 有微秒.但DATE 也可能包含时间信息.在 DB2 中，我认为 DATE 没有时间信息.
• 字符数据类型:了解Oracle中VARCHAR和VARCHAR2的区别
• NULL.在 Oracle 中，NULL 比在 DB2 中更通用.在 DB2 v9.7 之前，您必须将 NULL 强制转换为任何显式类型，例如cast(null 作为整数).这在 Oracle 中不是必需的.

• 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.

• SYSIBM.DUAL 简单地变成 DUAL
• 功能:它们都有点不同.您必须逐案检查.例如，LOCATE 变为 INSTR

• 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

• TRUNCATE IMMEDIATE 变为 TRUNCATE
• EXCEPT 变成 MINUS
• 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 ..

• 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 ..

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

您最有效的方法可能是使用某种 SQL 抽象.如果您使用 Java，我建议您使用 jOOQ 包装您的 SQL 语句(免责声明:我为jOOQ 背后的公司).jOOQ 为上述所有事实提供了 API 级别的抽象.大量的 SQL 可以在 DB2 和 Oracle 上执行，无需适应.我们还在开发更独立的翻译产品:https://www.jooq.org/translate

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 (Disclaimer: I work for the company behind jOOQ). 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. We're also working on a more independent translator product: https://www.jooq.org/translate

在更高的抽象层次上，Hibernate(或其他 JPA 实现)可以为您做同样的事情

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

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