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 個聚合函式，詳情檢視：

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 。

Oracle轉換Postgres