Oracle向PG轉移建議以及注意點

? 一、語法差異與遷移建議

1. 包結構（Package）

• Oracle 支持 PACKAGE 和 PACKAGE BODY 分離定義。
• PostgreSQL 不支持包結構，需將每個函數/過程單獨定義。

遷移建議：

• 將 PACKAGE 包中的每個函數和存儲過程拆分為獨立的 FUNCTION 或 PROCEDURE。
• 使用模式（Schema）來組織這些對象，模擬 Oracle 的包結構邏輯。

---

2. 變量聲明和賦值

• Oracle 中變量在 DECLARE 部分聲明。
• PostgreSQL 使用 %ROWTYPE 類似，但變量聲明需放在 DECLARE 塊中。

注意點：

• SELECT INTO 在 PostgreSQL 中用于賦值。
• 確保使用 PERFORM 替代無返回值的查詢（如日志插入）。

-- Oraclea A%Rowtype;-- PostgreSQL
DECLAREa A%ROWTYPE;

---

3. 異常處理

• Oracle 使用 WHEN OTHERS THEN ...
• PostgreSQL 使用 EXCEPTION WHEN OTHERS THEN ...

遷移建議：

• 替換 RAISE_APPLICATION_ERROR(xxx, '錯誤信息') 為 RAISE EXCEPTION '錯誤信息'

-- Oracle
Raise_Application_Error(xxx, 'DataNotFound' || Geterrmsg);
-- PostgreSQL
RAISE EXCEPTION 'DataNotFound: %', SQLERRM;

---

4. 表名大小寫敏感

• Oracle 默認大寫表名。
• PostgreSQL 默認小寫，引用原大小寫需加雙引號。

建議：

• 表名統一使用小寫，避免問題。
• 如有保留原名需求，用雙引號包裹。

---

5. 序列和 GUID

• Oracle 使用 SYS_GUID()
• PostgreSQL 可使用 uuid_generate_v4()（需安裝 uuid-ossp 擴展）

---

6. 日期計算

• Oracle 中 Sysdate 獲取當前時間。
• PostgreSQL 使用 NOW() 或 CURRENT_TIMESTAMP

-- Oracle
createdate := Sysdate;
-- PostgreSQL
createdate := NOW();

---

7. 時間差計算

• Oracle 時間差 (Sysdate - Starttime) * 24 * 60 * 60 * 1000
• PostgreSQL 使用 EXTRACT(EPOCH FROM ...) 轉換為毫秒

-- Oracle
Durtime = (Sysdate - Starttime) * 24 * 60 * 60 * 1000
-- PostgreSQL
durtime := EXTRACT(EPOCH FROM (NOW() - starttime)) * 1000;

---

8. 數據庫鏈接（如 @Mysqlct）

• Oracle 支持通過數據庫鏈接訪問遠程表。
• PostgreSQL 可以使用 dblink 或 postgres_fdw 擴展實現。

-- 示例：使用 dblink 查詢遠程用戶
SELECT * FROM dblink('host=... dbname=... user=...', 'SELECT * FROM sys_user') AS t(user_id TEXT, user_name TEXT);

---

? 二、遷移工具建議

工具	功能
ora2pg	自動化轉換 Oracle 到 PostgreSQL（這種方式可以遷移表結構和數據，存儲過程和函數可能還是需要手工遷移）
AWS Schema Conversion Tool	提供圖形界面輔助遷移
dataX	提供表數據遷移(使用DataX遷移后需要注意數字類型精度！！)
手動調整	對于復雜邏輯更可靠，尤其是異常處理、事務控制等

---

? 三、測試建議

1. 單元測試

   • 每個函數/過程單獨驗證輸入輸出。
   • 使用 pgTAP 或 PL/pgSQL 測試框架。

2. 性能測試

   • 觀察執行計劃是否合理。
   • 檢查索引是否缺失或冗余。

3. 日志記錄

   • 遷移過程中建議啟用 [LOG] 輸出調試信息。

---

? 四、存儲過程聲明結構對比

Oracle 函數

Function funcName(v_Text Varchar2) Return returnType%Rowtype AsfieldName fieldType%Rowtype;
Begin//具體邏輯/////Return fieldName;
Exception//異常處理When No_Data_Found ThenRaise_Application_Error(xxxx, 'Data Not Found' || Geterrmsg);When Too_Many_Rows ThenRaise_Application_Error(xxxx, 'Too Many Rows' || Geterrmsg);When Others ThenRaise_Application_Error(xxxx, 'Unknow Err' || Geterrmsg);
End;

PostgreSQL 函數

CREATE OR REPLACE FUNCTION funcName(v_text VARCHAR)
RETURNS returnType
LANGUAGE plpgsql
AS $$
DECLAREfiledName fieldType%ROWTYPE;
BEGIN//具體邏輯///////異常處理IF NOT FOUND THENRAISE EXCEPTION 'Data Not Found: %', SQLERRM;END IF;RETURN funcName;
EXCEPTIONWHEN TOO_MANY_ROWS THENRAISE EXCEPTION 'Too Many Rows: %', SQLERRM;WHEN OTHERS THENRAISE EXCEPTION 'Unknow Err: %', SQLERRM;
END;
$$;

---

? 五、總結

遷移要點	Oracle	PostgreSQL
包結構	支持	不支持
異常處理	WHEN OTHERS THEN	EXCEPTION WHEN OTHERS THEN
GUID	SYS_GUID()	uuid_generate_v4()
時間函數	SYSDATE	NOW()
表名大小寫	默認大寫	默認小寫
序列生成	SEQUENCE.NEXTVAL	nextval('seq')
數據庫鏈接	支持	使用 dblink / fdw
函數定義	FUNCTION	CREATE OR REPLACE FUNCTION
存儲過程	PROCEDURE	CREATE OR REPLACE FUNCTION

注：

1. 使用Ora2pg遷移時，對于復雜的存儲過程和函數無法正確轉換，需要手工遷移，若有更好的解決方案，可以評論或私聊，我們一起研究一下。
2. 對于某些特殊的數據類型如bool，使用ora2pg遷移后，可能會被轉成數字類型，需要后置手動修改。
3. psql schema postgres -c "SET session_replication_role = replica;" -f data.sql 使用 SET session_replication_role = replica可以忽略約束進行數據導入。

---

坑點記錄

一、語法相關

1. 異常對應關系

PostgreSQL Error Codes(PGSQL 異常Code文檔)

2. 觸發器不支持針對某個字段監控

Oracle遷移PGSQL_觸發器
...BEFORE UPDATE OF xxxxx ON xxxx...
需要結合功能，判斷修改方案。

3. PGSQL沒有Package概念，需要把OraclePackage包中的存儲過程和函數等，單獨拆出來，并按PGSQL方言修改。

二、ORM相關

1. Mybatis PGSQL CallableStatement 不支持命名參數綁定

解決方式：不使用命名參數綁定的方式，直接拼接

• 轉義全包

<![CDATA[ CALL XXXXXXXX('${p1}','${p2}')
]]>

• 使用{},這個方法暫時還沒有驗證

2. Mybatis 日志開放

<setting name="logImpl" value="STDOUT_LOGGING" />

3. @Select注解結尾不要分號 ;

相關SQL記錄

ORACLE查詢某個模式下所有number類型的字段信息

SELECT a.table_name,a.column_name,a.data_type,a.data_precision,a.data_scale
FROM all_tab_columns a
WHERE a.owner = 'SD'AND a.TABLE_NAME LIKE 'SD%'AND a.data_type = 'NUMBER'
ORDER BY TABLE_NAME ASC,COLUMN_NAME asc;

PostgreSQL查詢number類型字段信息

SELECT table_schema AS schema_name,table_name,column_name,data_type,numeric_precision AS precision,numeric_scale AS scale
FROM information_schema.columns
WHERE table_schema = 'public'AND data_type IN ('smallint', 'integer', 'bigint','decimal', 'numeric', 'real', 'double precision');