Oracle轉(zhuǎn)換Postgres

1臂容、前提

首先需要對Oracle和PostgreSQL的SQL都比較熟悉稠肘。對其理解的越詳細(xì)就越具有優(yōu)勢沉眶，本文幫助讀者迅速理解這兩類SQL的區(qū)別是什么甩卓。

如果因ACS/pg而需要將Oracle移植到PG释簿，那么就需要熟悉AOLserver Tcl，尤其是SOLserver的API洒试。本文倍奢，主要討論：

Oracle 10g到11g（大多數(shù)可以適用到8i）

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

PostgreSQL 8.4垒棋，甚至適用更早版本卒煞。

2、事務(wù)

Oracle這個數(shù)據(jù)庫會使用事務(wù)叼架，那么PostgreSQL也需要激活事務(wù)畔裕。多個DML語句組成一個代碼片段，而這些語句不會立即提交乖订，那么就需要使用BEGIN語句開啟一個事務(wù)扮饶，然后將這些語句包含在BEGIN這個塊中。Oracle和PG中ROLLBACK和COMMIT乍构、SAVEPOINT的語義相同甜无。Oracle的隔離級別，PostgreSQL中也有。大多數(shù)情況下PG的隔離級別（讀已提交）就已滿足需求毫蚓。

3占键、語法差異

PG中有少數(shù)語法不同但功能相同SQL。ACS/pg會自動進(jìn)行轉(zhuǎn)換元潘，只有大部分函數(shù)不同畔乙，需要手工進(jìn)行轉(zhuǎn)換。這個工作由db_sql_prep來完成翩概。

函數(shù)

Oracle有超過250個內(nèi)置單行函數(shù)和不止50個聚合函數(shù)牲距，詳情查看：https://wiki.postgresql.org/wiki/Oracle_Functions。

Sysdate

Oracle使用sysdate函數(shù)獲取當(dāng)前日期和時間（以服務(wù)器的時區(qū)為準(zhǔn)）钥庇。Postgres使用’now’::timestamp作為當(dāng)前事務(wù)啟動的日期和時間牍鞠。ACS/pg將這個包裝成sysdate()函數(shù)。

ACS/pg還包括Tcl過程评姨，即db_sysdate难述。因此：

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

應(yīng)該變成：

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

Dual表

Oracle的SELECT中實際不需要表名的地方可以使用表DUAL，因為Oracle中的FROM子句是必須的吐句。Postgsql中可以將FROM子句丟棄胁后。可以在postgres中創(chuàng)建一個視圖作為這個表從而消除上述問題嗦枢。這樣就可以在不干擾Postgres的解析器情況下兼容Oracle的SQL攀芯。遷移過程中，盡可能去掉“FROM DUAL”子句文虏。因為和jual進(jìn)行join比較奇怪侣诺。

ROWNUM和ROWID

Oracle的虛擬列ROWNUM：在執(zhí)行ORDER BY前讀取數(shù)據(jù)時分配一個數(shù)值。很多場景下可以使用ROW_NUMBER() OVER(ORDER BY...)替代氧秘。但是使用序列進(jìn)行模擬時可能會使性能慢些年鸳。

Oracle的虛擬列ROWID：表行的物理地址，以base64編碼敏储。應(yīng)用中可以使用該列臨時緩存行地址阻星，使第二次訪問時更加便捷。Postgres的ctid起同樣的作用已添。

序列

Oracle的序列語法是sequence_name.nextval。

Postgres的序列語法是nextval('sequence_name')滥酥。

Tcl中更舞，獲取寫一個序列值可以抽象為調(diào)用[db_sequence_nextval $db sequence_name]。如果需要在一個復(fù)雜的SQL語句中使用序列值坎吻，可以使用 [db_sequence_nextval_sql sequence_name]缆蝉。

解碼

Oracle的解碼函數(shù)使用方法：decode(expr, search, result [, search, result...] [, default])

為了評估這個表達(dá)式，Oracle一個一個地比較expr和search值。如果expr等于search刊头，Oracle返回對應(yīng)的result黍瞧。如果沒有找到匹配值，返回default或者null原杂。

Postgres沒有這樣的結(jié)構(gòu)印颤，但是可以使用下面格式替代：

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

例如：CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END，返回第一個為真的謂詞對應(yīng)的表達(dá)式穿肄。

DECODE和CASE的模擬方式有一點(diǎn)不同：DECODE (x,NULL,'null','else')年局，如果x為NULL則返回NULL；而CASE x WHEN NULL THEN 'null' ELSE 'else' END咸产，則返回’else’的result矢否。Oracle同樣。

NVL

Oracle還有其他便捷函數(shù)：NVL脑溢。如果不為NULL僵朗，NVL返回第一個參數(shù)，否則返回第二個參數(shù)：start_date := NVL(hire_date, SYSDATE);屑彻。如果hire_date為NULL衣迷，則前面的語句會返回SYSDATE。Postgres和Oracle有一個函數(shù)以更普遍的方式執(zhí)行同樣的行為： coalesce(expr1, expr2, expr3,....)酱酬，返回第一個非NULL表達(dá)式壶谒。

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具備大部分功能陨界，但是一些特性還需要等待其新版本發(fā)布。

Outer joins

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

SELECT a.field1, b.field2

FROM a, b

WHERE a.item_id = b.item_id(+)

(+)表示菌瘪，如果表b中沒有匹配的item_id值，匹配會繼續(xù)下去阱当，會作為一個空行進(jìn)行匹配俏扩。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录淡，此時可以定義函數(shù)：

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語句轉(zhuǎn)換成WITH RECURSIVE。有時還可以將CONNECT BY當(dāng)做一個簡單的iterator：

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

等價于：

SELECT ... FROM generate_series(...)

NO_DATA_FOUND and TOO_MANY_ROWS

默認(rèn)情況下PL/pgsql禁止使用此異常窍帝。當(dāng)需要在存儲的PLpgSQL代碼中進(jìn)行單行檢查時努潘，需要在所有SELECT中的任何關(guān)鍵字INTO之后添加關(guān)鍵字STRICT。

5盯桦、數(shù)據(jù)類型

Postgres嚴(yán)格尊周SQL表中慈俯，而Oracle由于歷史原因，會有自己特有的方式拥峦，尤其是數(shù)據(jù)類型方面贴膘。

空字符串與NULL

Oracle中，strings()空和NULL在字符串內(nèi)容中相同略号⌒滔浚可以將NULL和和一個字符串連接起來作為結(jié)果。但是在postgres中玄柠，這種情況得到的結(jié)果是NULL突梦。Oracle中需要使用IS NULL操作符來檢測字符串是否為空。Postgres中羽利，對于空字符串得到的結(jié)果是FALSE宫患，而NULL得到的是TRUE。當(dāng)從Oracle向postgres轉(zhuǎn)換時这弧，需要分析字符代碼娃闲，分離出NULL和空字符串。

Numeric類型

Oracle中經(jīng)常使用NUMBER數(shù)據(jù)類型匾浪，PG中對應(yīng)的數(shù)據(jù)類型時DECIMAL或者NUMERIC皇帮。PG中的numbers限制（小數(shù)點(diǎn)前到131072位，小數(shù)點(diǎn)后16383位）比Oracle高蛋辈，內(nèi)部存儲方式相同属拾。Oracle的FLOAT在PG中是REAL，DOUBLE是DOUBLE PRECISION冷溶。

Date and Time

Oracle中的DATE包含data和time渐白。很多中情況下珍昨，使用PG中的TIMESTAMP就足夠了离例。由于date只包含秒、分鸳玩、小時虏劲、天托酸、月和年，所以一些情況下不是精確的結(jié)果柒巫。沒有幾分鐘励堡、沒有夏令時、沒有時區(qū)堡掏。Oracle的TIMESTAMP和PG類似应结。

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

CLOBs

PG以TEXT的形式對CLOB有不錯的支持鹅龄。

BLOBs

PG對二進(jìn)制大對象支持非常差。因為不能使用pg_dump進(jìn)行dump所以不適合在24/7環(huán)境中使用亭畜。利用大對象的數(shù)據(jù)庫進(jìn)行備份時扮休，需要將數(shù)據(jù)庫關(guān)閉，然后直接備份數(shù)據(jù)目錄拴鸵。

Don Baccus修改了SOLserver的PG驅(qū)動玷坠，通過編碼/解碼二進(jìn)制文件，從而支持二進(jìn)制大對象劲藐。數(shù)據(jù)庫在運(yùn)行時進(jìn)行dump八堡，這些結(jié)果對象可以用來保證一致性，從而在備份時不需要中斷服務(wù)聘芜。

為了繞過PG對元組大小對于一個塊的限制兄渺，驅(qū)動程序?qū)⒕幋a的數(shù)據(jù)分成8K大小的塊。PG將在2000年夏天對大對象進(jìn)行大修汰现。因此挂谍，只實現(xiàn)了ACS使用的BLOB功能。

為了使用BLOB驅(qū)動擴(kuò)展服鹅，首先需要創(chuàng)建一個表凳兵，其lob列定義為interger類型，再創(chuàng)建一個觸發(fā)器on_lob_ref企软。例如：

create table my_table (

????my_key integer primary key,

????lob integer references lobs,

????my_other_data some_type -- etc

);

創(chuàng)建一個觸發(fā)器my_table_lob_trig庐扫，在insert或delete或update前觸發(fā)：

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"

主要，調(diào)用時需將其包裝在一個事務(wù)中仗哨，即使此時沒有進(jìn)行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：自動遷移整個數(shù)據(jù)庫schema并將Oracle數(shù)據(jù)轉(zhuǎn)換成PG的數(shù)據(jù)的工具集厌漂。

Full Convert：將Oracle轉(zhuǎn)換成PG萨醒，每秒100K個記錄。

Oracle to Postgres data migration and sync：每4-5分鐘轉(zhuǎn)換1M個記錄苇倡「恢剑基于觸發(fā)器的數(shù)據(jù)庫同步方法和并行雙向同步方式可幫助輕松地管理數(shù)據(jù)囤踩。

ESF Database Migration Toolkit：直連Oracle和PG，遷移表結(jié)構(gòu)晓褪、數(shù)據(jù)堵漱、索引、主鍵涣仿、外鍵勤庐、內(nèi)容等。

Orafce：兼容Oracle的函數(shù)好港。比如date函數(shù)（next_day,last_day,trunc,round等）愉镰、字符串函數(shù)、一些包DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE等钧汹。

Ora2pg：Perl腳本丈探，兼容schema。連接Oracle崭孤，提取結(jié)構(gòu)类嗤，產(chǎn)生SQL語句然后加載到PG。

Oracle to postgres：不使用ODBC和其他中間件辨宠。轉(zhuǎn)換表結(jié)構(gòu)遗锣、數(shù)據(jù)、索引嗤形、主鍵和外鍵精偿。

ora_migrator：PL/pgSQL擴(kuò)展，充分利用Oracle的Foreign Data Wrapper赋兵。

7笔咽、原文

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