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