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這個(gè)數(shù)據(jù)庫會使用事務(wù)，那么PostgreSQL也需要激活事務(wù)。多個(gè)DML語句組成一個(gè)代碼片段，而這些語句不會立即提交，那么就需要使用BEGIN語句開啟一個(gè)事務(wù)，然后將這些語句包含在BEGIN這個(gè)塊中。Oracle和PG中ROLLBACK和COMMIT、SAVEPOINT的語義相同。Oracle的隔離級別，PostgreSQL中也有。大多數(shù)情況下PG的隔離級別（讀已提交）就已滿足需求。
3、語法差異
PG中有少數(shù)語法不同但功能相同SQL。ACS/pg會自動(dòng)進(jìn)行轉(zhuǎn)換，只有大部分函數(shù)不同，需要手工進(jìn)行轉(zhuǎn)換。這個(gè)工作由db_sql_prep來完成。
函數(shù)
Oracle有超過250個(gè)內(nèi)置單行函數(shù)和不止50個(gè)聚合函數(shù)，詳情查看：https://wiki.postgresql.org/wiki/Oracle_Functions。
Sysdate
Oracle使用sysdate函數(shù)獲取當(dāng)前日期和時(shí)間（以服務(wù)器的時(shí)區(qū)為準(zhǔn)）。Postgres使用’now’::timestamp作為當(dāng)前事務(wù)啟動(dòng)的日期和時(shí)間。ACS/pg將這個(gè)包裝成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中實(shí)際不需要表名的地方可以使用表DUAL，因?yàn)镺racle中的FROM子句是必須的。Postgsql中可以將FROM子句丟棄?？梢栽趐ostgres中創(chuàng)建一個(gè)視圖作為這個(gè)表從而消除上述問題。這樣就可以在不干擾Postgres的解析器情況下兼容Oracle的SQL。遷移過程中，盡可能去掉“FROM DUAL”子句。因?yàn)楹蚸ual進(jìn)行join比較奇怪。
ROWNUM和ROWID
Oracle的虛擬列ROWNUM：在執(zhí)行ORDER BY前讀取數(shù)據(jù)時(shí)分配一個(gè)數(shù)值。很多場景下可以使用ROW_NUMBER() OVER(ORDER BY...)替代。但是使用序列進(jìn)行模擬時(shí)可能會使性能慢些。
Oracle的虛擬列ROWID：表行的物理地址，以base64編碼。應(yīng)用中可以使用該列臨時(shí)緩存行地址，使第二次訪問時(shí)更加便捷。Postgres的ctid起同樣的作用。
序列
Oracle的序列語法是sequence_name.nextval。
Postgres的序列語法是nextval('sequence_name')。
Tcl中，獲取寫一個(gè)序列值可以抽象為調(diào)用[db_sequence_nextval $db sequence_name]。如果需要在一個(gè)復(fù)雜的SQL語句中使用序列值，可以使用 [db_sequence_nextval_sql sequence_name]。
解碼
Oracle的解碼函數(shù)使用方法：decode(expr, search, result [, search, result...] [, default])
為了評估這個(gè)表達(dá)式，Oracle一個(gè)一個(gè)地比較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，返回第一個(gè)為真的謂詞對應(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返回第一個(gè)參數(shù)，否則返回第二個(gè)參數(shù)：start_date := NVL(hire_date, SYSDATE);。如果hire_date為NULL，則前面的語句會返回SYSDATE。Postgres和Oracle有一個(gè)函數(shù)以更普遍的方式執(zhí)行同樣的行為： coalesce(expr1, expr2, expr3,....)，返回第一個(gè)非NULL表達(dá)式。
FROM中子查詢
Postgresql中子查詢需要使用括號包含，并提供一個(gè)別名。Oracle中不需要?jiǎng)e名：
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ù)下去，會作為一個(gè)空行進(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表中提取時(shí)，也可能不使用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í)可以定義函數(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。有時(shí)還可以將CONNECT BY當(dāng)做一個(gè)簡單的iterator：
SELECT ... FROM DUAL CONNECT BY rownum <=10
等價(jià)于：
SELECT ... FROM generate_series(...)
NO_DATA_FOUND and TOO_MANY_ROWS
默認(rèn)情況下PL/pgsql禁止使用此異常。當(dāng)需要在存儲的PLpgSQL代碼中進(jìn)行單行檢查時(shí)，需要在所有SELECT中的任何關(guān)鍵字INTO之后添加關(guān)鍵字STRICT。
5、數(shù)據(jù)類型
Postgres嚴(yán)格尊周SQL表中，而Oracle由于歷史原因，會有自己特有的方式，尤其是數(shù)據(jù)類型方面。
空字符串與NULL
Oracle中，strings()空和NULL在字符串內(nèi)容中相同。可以將NULL和和一個(gè)字符串連接起來作為結(jié)果。但是在postgres中，這種情況得到的結(jié)果是NULL。Oracle中需要使用IS NULL操作符來檢測字符串是否為空。Postgres中，對于空字符串得到的結(jié)果是FALSE，而NULL得到的是TRUE。當(dāng)從Oracle向postgres轉(zhuǎn)換時(shí)，需要分析字符代碼，分離出NULL和空字符串。
Numeric類型
Oracle中經(jīng)常使用NUMBER數(shù)據(jù)類型，PG中對應(yīng)的數(shù)據(jù)類型時(shí)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只包含秒、分、小時(shí)、天、月和年，所以一些情況下不是精確的結(jié)果。沒有幾分鐘、沒有夏令時(shí)、沒有時(shí)區(qū)。Oracle的TIMESTAMP和PG類似。
Oracle只有INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND，因此PG可以直接使用。
CLOBs
PG以TEXT的形式對CLOB有不錯(cuò)的支持。
BLOBs
PG對二進(jìn)制大對象支持非常差。因?yàn)椴荒苁褂胮g_dump進(jìn)行dump所以不適合在24/7環(huán)境中使用。利用大對象的數(shù)據(jù)庫進(jìn)行備份時(shí)，需要將數(shù)據(jù)庫關(guān)閉，然后直接備份數(shù)據(jù)目錄。
Don Baccus修改了SOLserver的PG驅(qū)動(dòng)，通過編碼/解碼二進(jìn)制文件，從而支持二進(jìn)制大對象。數(shù)據(jù)庫在運(yùn)行時(shí)進(jìn)行dump，這些結(jié)果對象可以用來保證一致性，從而在備份時(shí)不需要中斷服務(wù)。
為了繞過PG對元組大小對于一個(gè)塊的限制，驅(qū)動(dòng)程序?qū)⒕幋a的數(shù)據(jù)分成8K大小的塊。PG將在2000年夏天對大對象進(jìn)行大修。因此，只實(shí)現(xiàn)了ACS使用的BLOB功能。
為了使用BLOB驅(qū)動(dòng)擴(kuò)展，首先需要?jiǎng)?chuàng)建一個(gè)表，其lob列定義為interger類型，再創(chuàng)建一個(gè)觸發(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)建一個(gè)觸發(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)用時(shí)需將其包裝在一個(gè)事務(wù)中，即使此時(shí)沒有進(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：自動(dòng)遷移整個(gè)數(shù)據(jù)庫schema并將Oracle數(shù)據(jù)轉(zhuǎn)換成PG的數(shù)據(jù)的工具集。
Full Convert：將Oracle轉(zhuǎn)換成PG，每秒100K個(gè)記錄。
Oracle to Postgres data migration and sync：每4-5分鐘轉(zhuǎn)換1M個(gè)記錄。基于觸發(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