Oracle转换Postgres

1、前提

首先需要对Oracle和PostgreSQL的SQL都比较熟悉。对其理解的越详细就越具有优势，本文帮助读者迅速理解这两类SQL的区别是什么。

如果因ACS/pg而需要将Oracle移植到PG，那么就需要熟悉AOLserver Tcl，尤其是SOLserver的API。本文，主要讨论：

Oracle 10g到11g（大多数可以适用到8i）

Oracle 12c某些方面会有不同，但是迁移更加便捷

PostgreSQL 8.4，甚至适用更早版本。

2、事务

Oracle这个数据库会使用事务，那么PostgreSQL也需要激活事务。多个DML语句组成一个代码片段，而这些语句不会立即提交，那么就需要使用BEGIN语句开启一个事务，然后将这些语句包含在BEGIN这个块中。Oracle和PG中ROLLBACK和COMMIT、SAVEPOINT的语义相同。Oracle的隔离级别，PostgreSQL中也有。大多数情况下PG的隔离级别（读已提交）就已满足需求。

3、语法差异

PG中有少数语法不同但功能相同SQL。ACS/pg会自动进行转换，只有大部分函数不同，需要手工进行转换。这个工作由db_sql_prep来完成。

函数

Oracle有超过250个内置单行函数和不止50个聚合函数，详情查看：https://wiki.postgresql.org/wiki/Oracle_Functions。

Sysdate

Oracle使用sysdate函数获取当前日期和时间（以服务器的时区为准）。Postgres使用’now’::timestamp作为当前事务启动的日期和时间。ACS/pg将这个包装成sysdate()函数。

ACS/pg还包括Tcl过程，即db_sysdate。因此：

set now [database_to_tcl_string $db "select sysdate from dual"]

应该变成：

set now [database_to_tcl_string $db "select [db_sysdate] from dual"]

Dual表

Oracle的SELECT中实际不需要表名的地方可以使用表DUAL，因为Oracle中的FROM子句是必须的。Postgsql中可以将FROM子句丢弃。可以在postgres中创建一个视图作为这个表从而消除上述问题。这样就可以在不干扰Postgres的解析器情况下兼容Oracle的SQL。迁移过程中，尽可能去掉“FROM DUAL”子句。因为和jual进行join比较奇怪。

ROWNUM和ROWID

Oracle的虚拟列ROWNUM：在执行ORDER BY前读取数据时分配一个数值。很多场景下可以使用ROW_NUMBER() OVER(ORDER BY...)替代。但是使用序列进行模拟时可能会使性能慢些。

Oracle的虚拟列ROWID：表行的物理地址，以base64编码。应用中可以使用该列临时缓存行地址，使第二次访问时更加便捷。Postgres的ctid起同样的作用。

序列

Oracle的序列语法是sequence_name.nextval。

Postgres的序列语法是nextval('sequence_name')。

Tcl中，获取写一个序列值可以抽象为调用[db_sequence_nextval $db sequence_name]。如果需要在一个复杂的SQL语句中使用序列值，可以使用 [db_sequence_nextval_sql sequence_name]。

解码

Oracle的解码函数使用方法：decode(expr, search, result [, search, result...] [, default])

为了评估这个表达式，Oracle一个一个地比较expr和search值。如果expr等于search，Oracle返回对应的result。如果没有找到匹配值，返回default或者null。

Postgres没有这样的结构，但是可以使用下面格式替代：

CASE WHEN expr THEN expr [...] ELSE expr END

例如：CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END，返回第一个为真的谓词对应的表达式。

DECODE和CASE的模拟方式有一点不同：DECODE (x,NULL,'null','else')，如果x为NULL则返回NULL；而CASE x WHEN NULL THEN 'null' ELSE 'else' END，则返回’else’的result。Oracle同样。

NVL

Oracle还有其他便捷函数：NVL。如果不为NULL，NVL返回第一个参数，否则返回第二个参数：start_date := NVL(hire_date, SYSDATE);。如果hire_date为NULL，则前面的语句会返回SYSDATE。Postgres和Oracle有一个函数以更普遍的方式执行同样的行为： coalesce(expr1, expr2, expr3,....)，返回第一个非NULL表达式。

FROM中子查询

Postgresql中子查询需要使用括号包含，并提供一个别名。Oracle中不需要别名：

Oracle： SELECT * FROM (SELECT * FROM table_a)

Postgresql： SELECT * FROM (SELECT * FROM table_a) AS foo

4、功能差异

Postgresql并不具备Oracle所有功能。ACS/pg通过指定的方案解决这些限制。虽然postgres具备大部分功能，但是一些特性还需要等待其新版本发布。

Outer joins

Oracle老版本9i之前，outer join：

SELECT a.field1, b.field2

FROM a, b

WHERE a.item_id = b.item_id(+)

(+)表示，如果表b中没有匹配的item_id值，匹配会继续下去，会作为一个空行进行匹配。Postgresql和Oracle 9i及之前版本：

SELECT a.field1, b.field2

FROM a

LEFT OUTER JOIN b

ON a.item_id = b.item_id;

只有汇聚值从outer joined表中提取时，也可能不使用join。如果原始查询：

SELECT a.field1, sum (b.field2)

FROM a, b

WHERE a.item_id = b.item_id (+)

GROUP BY a.field1

Postgres的查询：SELECT a.field1, b_sum_field2_by_item_id (a.item_id) FROM a，此时可以定义函数：

CREATE FUNCTION b_sum_field2_by_item_id (integer)

RETURNS integer

AS '

DECLARE

v_item_id alias for $1;

BEGIN

RETURN sum(field2) FROM b WHERE item_id = v_item_id;

END;

' language 'plpgsql';

Oracle 9i开始将支持SQL 99的 outer join语法。但是一些程序员仍然使用旧语法，所以这篇文章显得有意义。

CONNECT BY

Postgres不支持connect by语句。可以使用WITH RECURSIVE替代。由于WITH RECURSIVE是图灵完毕的，因此很容易将CONNECT BY语句转换成WITH RECURSIVE。有时还可以将CONNECT BY当做一个简单的iterator：

SELECT ... FROM DUAL CONNECT BY rownum <=10

等价于：

SELECT ... FROM generate_series(...)

NO_DATA_FOUND and TOO_MANY_ROWS

默认情况下PL/pgsql禁止使用此异常。当需要在存储的PLpgSQL代码中进行单行检查时，需要在所有SELECT中的任何关键字INTO之后添加关键字STRICT。

5、数据类型

Postgres严格尊周SQL表中，而Oracle由于历史原因，会有自己特有的方式，尤其是数据类型方面。

空字符串与NULL

Oracle中，strings()空和NULL在字符串内容中相同。可以将NULL和和一个字符串连接起来作为结果。但是在postgres中，这种情况得到的结果是NULL。Oracle中需要使用IS NULL操作符来检测字符串是否为空。Postgres中，对于空字符串得到的结果是FALSE，而NULL得到的是TRUE。当从Oracle向postgres转换时，需要分析字符代码，分离出NULL和空字符串。

Numeric类型

Oracle中经常使用NUMBER数据类型，PG中对应的数据类型时DECIMAL或者NUMERIC。PG中的numbers限制（小数点前到131072位，小数点后16383位）比Oracle高，内部存储方式相同。Oracle的FLOAT在PG中是REAL，DOUBLE是DOUBLE PRECISION。

Date and Time

Oracle中的DATE包含data和time。很多中情况下，使用PG中的TIMESTAMP就足够了。由于date只包含秒、分、小时、天、月和年，所以一些情况下不是精确的结果。没有几分钟、没有夏令时、没有时区。Oracle的TIMESTAMP和PG类似。

Oracle只有INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND，因此PG可以直接使用。

CLOBs

PG以TEXT的形式对CLOB有不错的支持。

BLOBs

PG对二进制大对象支持非常差。因为不能使用pg_dump进行dump所以不适合在24/7环境中使用。利用大对象的数据库进行备份时，需要将数据库关闭，然后直接备份数据目录。

Don Baccus修改了SOLserver的PG驱动，通过编码/解码二进制文件，从而支持二进制大对象。数据库在运行时进行dump，这些结果对象可以用来保证一致性，从而在备份时不需要中断服务。

为了绕过PG对元组大小对于一个块的限制，驱动程序将编码的数据分成8K大小的块。PG将在2000年夏天对大对象进行大修。因此，只实现了ACS使用的BLOB功能。

为了使用BLOB驱动扩展，首先需要创建一个表，其lob列定义为interger类型，再创建一个触发器on_lob_ref。例如：

create table my_table (

my_key integer primary key,

lob integer references lobs,

my_other_data some_type -- etc

);

创建一个触发器my_table_lob_trig，在insert或delete或update前触发：

set lob [database_to_tcl_string $db "select empty_lob()"]

ns_db dml $db "begin"

ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"

ns_pg blob_dml_file $db $lob $tmp_filename

ns_db dml $db "end"

主要，调用时需将其包装在一个事务中，即使此时没有进行update。：

set lob [database_to_tcl_string $db "select lob from my_table

where my_key = $my_key"]

ns_pg blob_write $db $lob

6、其他工具

Ispirer MnMTK：自动迁移整个数据库schema并将Oracle数据转换成PG的数据的工具集。

Full Convert：将Oracle转换成PG，每秒100K个记录。

Oracle to Postgres data migration and sync：每4-5分钟转换1M个记录。基于触发器的数据库同步方法和并行双向同步方式可帮助轻松地管理数据。

ESF Database Migration Toolkit：直连Oracle和PG，迁移表结构、数据、索引、主键、外键、内容等。

Orafce：兼容Oracle的函数。比如date函数（next_day,last_day,trunc,round等）、字符串函数、一些包DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE等。

Ora2pg：Perl脚本，兼容schema。连接Oracle，提取结构，产生SQL语句然后加载到PG。

Oracle to postgres：不使用ODBC和其他中间件。转换表结构、数据、索引、主键和外键。

ora_migrator：PL/pgSQL扩展，充分利用Oracle的Foreign Data Wrapper。

7、原文

https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion