Oracle向PG转移建议以及注意点

Oracle向PG转移建议以及注意点

✅ 一、语法差异与迁移建议

1. 包结构（Package）

• Oracle 支持 PACKAGE 和 PACKAGE BODY 分离定义。
• PostgreSQL 不支持包结构，需将每个函数/过程单独定义。

迁移建议：

• 将 PACKAGE 包中的每个函数和存储过程拆分为独立的 FUNCTION 或 PROCEDURE。
• 使用模式（Schema）来组织这些对象，模拟 Oracle 的包结构逻辑。

---

2. 变量声明和赋值

• Oracle 中变量在 DECLARE 部分声明。
• PostgreSQL 使用 %ROWTYPE 类似，但变量声明需放在 DECLARE 块中。

注意点：

• SELECT INTO 在 PostgreSQL 中用于赋值。
• 确保使用 PERFORM 替代无返回值的查询（如日志插入）。

-- Oracle
	a A%Rowtype;

-- PostgreSQL
DECLARE
    a 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);

---

9. 空值判断

• Oracle使用 nvl(fieldName, ifNullVal)
• PostgreSQL使用 COALESCE(fieldName, ifNullVal)

---

10. ROWNUM

Oracle中使用ROWNUM伪列来获取记录行号，在PostgreSQL中也有相关用法，需要改写成 ROW_NUMBER() OVER()

---

✅ 二、迁移工具建议

工具	功能
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 As
    fieldName fieldType%Rowtype;
Begin
    ////////////
    //具体逻辑//
    ///////////
      
    Return fieldName;
Exception
	//异常处理
    When No_Data_Found Then
      Raise_Application_Error(xxxx, 'Data Not Found' || Geterrmsg);
    When Too_Many_Rows Then
      Raise_Application_Error(xxxx, 'Too Many Rows' || Geterrmsg);
    When Others Then
      Raise_Application_Error(xxxx, 'Unknow Err' || Geterrmsg);
End;

PostgreSQL 函数

CREATE OR REPLACE FUNCTION funcName(v_text VARCHAR)
RETURNS returnType
LANGUAGE plpgsql
AS $$
DECLARE
    filedName fieldType%ROWTYPE;
BEGIN
    ////////////
    //具体逻辑//
    ///////////

	//异常处理
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Data Not Found: %', SQLERRM;
    END IF;

    RETURN funcName;
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        RAISE EXCEPTION 'Too Many Rows: %', SQLERRM;
    WHEN OTHERS THEN
        RAISE 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'
    );