Oracle轉(zhuǎn)換Postgres

1、前提
首先需要對(duì)Oracle和PostgreSQL的SQL都比較熟悉。對(duì)其理解的越詳細(xì)就越具有優(yōu)勢(shì)，本文幫助讀者迅速理解這兩類SQL的區(qū)別是什么。
如果因ACS/pg而需要將Oracle移植到PG，那么就需要熟悉AOLserver Tcl，尤其是SOLserver的API。本文，主要討論：
Oracle 10g到11g（大多數(shù)可以適用到8i）
Oracle 12c某些方面會(huì)有不同，但是遷移更加便捷
PostgreSQL 8.4，甚至適用更早版本。
2、事務(wù)
Oracle這個(gè)數(shù)據(jù)庫(kù)會(huì)使用事務(wù)，那么PostgreSQL也需要激活事務(wù)。多個(gè)DML語(yǔ)句組成一個(gè)代碼片段，而這些語(yǔ)句不會(huì)立即提交，那么就需要使用BEGIN語(yǔ)句開(kāi)啟一個(gè)事務(wù)，然后將這些語(yǔ)句包含在BEGIN這個(gè)塊中。Oracle和PG中ROLLBACK和COMMIT、SAVEPOINT的語(yǔ)義相同。Oracle的隔離級(jí)別，PostgreSQL中也有。大多數(shù)情況下PG的隔離級(jí)別（讀已提交）就已滿足需求。
3、語(yǔ)法差異
PG中有少數(shù)語(yǔ)法不同但功能相同SQL。ACS/pg會(huì)自動(dòng)進(jìn)行轉(zhuǎn)換，只有大部分函數(shù)不同，需要手工進(jìn)行轉(zhuǎn)換。這個(gè)工作由db_sql_prep來(lái)完成。
函數(shù)
Oracle有超過(guò)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過(guò)程，即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è)表從而消除上述問(wèn)題。這樣就可以在不干擾Postgres的解析器情況下兼容Oracle的SQL。遷移過(guò)程中，盡可能去掉“FROM DUAL”子句。因?yàn)楹蚸ual進(jìn)行join比較奇怪。
ROWNUM和ROWID
Oracle的虛擬列ROWNUM：在執(zhí)行ORDER BY前讀取數(shù)據(jù)時(shí)分配一個(gè)數(shù)值。很多場(chǎng)景下可以使用ROW_NUMBER() OVER(ORDER BY...)替代。但是使用序列進(jìn)行模擬時(shí)可能會(huì)使性能慢些。
Oracle的虛擬列ROWID：表行的物理地址，以base64編碼。應(yīng)用中可以使用該列臨時(shí)緩存行地址，使第二次訪問(wèn)時(shí)更加便捷。Postgres的ctid起同樣的作用。
序列
Oracle的序列語(yǔ)法是sequence_name.nextval。
Postgres的序列語(yǔ)法是nextval('sequence_name')。
Tcl中，獲取寫一個(gè)序列值可以抽象為調(diào)用[db_sequence_nextval $db sequence_name]。如果需要在一個(gè)復(fù)雜的SQL語(yǔ)句中使用序列值，可以使用 [db_sequence_nextval_sql sequence_name]。
解碼
Oracle的解碼函數(shù)使用方法：decode(expr, search, result [, search, result...] [, default])
為了評(píng)估這個(gè)表達(dá)式，Oracle一個(gè)一個(gè)地比較expr和search值。如果expr等于search，Oracle返回對(duì)應(yīng)的result。如果沒(méi)有找到匹配值，返回default或者null。
Postgres沒(méi)有這樣的結(jié)構(gòu)，但是可以使用下面格式替代：
CASE WHEN expr THEN expr [...] ELSE expr END
例如：CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END，返回第一個(gè)為真的謂詞對(duì)應(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，則前面的語(yǔ)句會(huì)返回SYSDATE。Postgres和Oracle有一個(gè)函數(shù)以更普遍的方式執(zhí)行同樣的行為： coalesce(expr1, expr2, expr3,....)，返回第一個(gè)非NULL表達(dá)式。
FROM中子查詢
Postgresql中子查詢需要使用括號(hào)包含，并提供一個(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通過(guò)指定的方案解決這些限制。雖然postgres具備大部分功能，但是一些特性還需要等待其新版本發(fā)布。
Outer joins
Oracle老版本9i之前，outer join：
SELECT a.field1, b.field2
FROM a, b
WHERE a.item_id = b.item_id(+)
(+)表示，如果表b中沒(méi)有匹配的item_id值，匹配會(huì)繼續(xù)下去，會(huì)作為一個(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開(kāi)始將支持SQL 99的 outer join語(yǔ)法。但是一些程序員仍然使用舊語(yǔ)法，所以這篇文章顯得有意義。
CONNECT BY
Postgres不支持connect by語(yǔ)句?？梢允褂肳ITH RECURSIVE替代。由于WITH RECURSIVE是圖靈完畢的，因此很容易將CONNECT BY語(yǔ)句轉(zhuǎn)換成WITH RECURSIVE。有時(shí)還可以將CONNECT BY當(dāng)做一個(gè)簡(jiǎn)單的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)需要在存儲(chǔ)的PLpgSQL代碼中進(jìn)行單行檢查時(shí)，需要在所有SELECT中的任何關(guān)鍵字INTO之后添加關(guān)鍵字STRICT。
5、數(shù)據(jù)類型
Postgres嚴(yán)格尊周SQL表中，而Oracle由于歷史原因，會(huì)有自己特有的方式，尤其是數(shù)據(jù)類型方面。
空字符串與NULL
Oracle中，strings()空和NULL在字符串內(nèi)容中相同?？梢詫ULL和和一個(gè)字符串連接起來(lái)作為結(jié)果。但是在postgres中，這種情況得到的結(jié)果是NULL。Oracle中需要使用IS NULL操作符來(lái)檢測(cè)字符串是否為空。Postgres中，對(duì)于空字符串得到的結(jié)果是FALSE，而NULL得到的是TRUE。當(dāng)從Oracle向postgres轉(zhuǎn)換時(shí)，需要分析字符代碼，分離出NULL和空字符串。
Numeric類型
Oracle中經(jīng)常使用NUMBER數(shù)據(jù)類型，PG中對(duì)應(yīng)的數(shù)據(jù)類型時(shí)DECIMAL或者NUMERIC。PG中的numbers限制（小數(shù)點(diǎn)前到131072位，小數(shù)點(diǎn)后16383位）比Oracle高，內(nèi)部存儲(chǔ)方式相同。Oracle的FLOAT在PG中是REAL，DOUBLE是DOUBLE PRECISION。
Date and Time
Oracle中的DATE包含data和time。很多中情況下，使用PG中的TIMESTAMP就足夠了。由于date只包含秒、分、小時(shí)、天、月和年，所以一些情況下不是精確的結(jié)果。沒(méi)有幾分鐘、沒(méi)有夏令時(shí)、沒(méi)有時(shí)區(qū)。Oracle的TIMESTAMP和PG類似。
Oracle只有INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND，因此PG可以直接使用。
CLOBs
PG以TEXT的形式對(duì)CLOB有不錯(cuò)的支持。
BLOBs
PG對(duì)二進(jìn)制大對(duì)象支持非常差。因?yàn)椴荒苁褂胮g_dump進(jìn)行dump所以不適合在24/7環(huán)境中使用。利用大對(duì)象的數(shù)據(jù)庫(kù)進(jìn)行備份時(shí)，需要將數(shù)據(jù)庫(kù)關(guān)閉，然后直接備份數(shù)據(jù)目錄。
Don Baccus修改了SOLserver的PG驅(qū)動(dòng)，通過(guò)編碼/解碼二進(jìn)制文件，從而支持二進(jìn)制大對(duì)象。數(shù)據(jù)庫(kù)在運(yùn)行時(shí)進(jìn)行dump，這些結(jié)果對(duì)象可以用來(lái)保證一致性，從而在備份時(shí)不需要中斷服務(wù)。
為了繞過(guò)PG對(duì)元組大小對(duì)于一個(gè)塊的限制，驅(qū)動(dòng)程序?qū)⒕幋a的數(shù)據(jù)分成8K大小的塊。PG將在2000年夏天對(duì)大對(duì)象進(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()"]

成都創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都網(wǎng)站建設(shè)、網(wǎng)站設(shè)計(jì)、青海網(wǎng)絡(luò)推廣、成都微信小程序、青海網(wǎng)絡(luò)營(yíng)銷、青海企業(yè)策劃、青海品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運(yùn)營(yíng)等，從售前售中售后，我們都將竭誠(chéng)為您服務(wù)，您的肯定，是我們最大的嘉獎(jiǎng)；成都創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供青海建站搭建服務(wù)，24小時(shí)服務(wù)熱線：13518219792，官方網(wǎng)址：bm7419.com

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í)沒(méi)有進(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ù)庫(kù)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ù)庫(kù)同步方法和并行雙向同步方式可幫助輕松地管理數(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語(yǔ)句然后加載到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

本文題目：Oracle轉(zhuǎn)換Postgres
標(biāo)題路徑：http://bm7419.com/article32/pssisc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計(jì)公司、網(wǎng)站導(dǎo)航、動(dòng)態(tài)網(wǎng)站、定制網(wǎng)站、企業(yè)網(wǎng)站制作、云服務(wù)器

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)