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替代无返回值的查询（如日志插入）。
-- 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迁移后需要注意数字类型精度！！) |
| 手动调整 | 对于复杂逻辑更可靠，尤其是异常处理、事务控制等 |
✅ 三、测试建议
-
单元测试
- 每个函数/过程单独验证输入输出。
- 使用
pgTAP或PL/pgSQL测试框架。
-
性能测试
- 观察执行计划是否合理。
- 检查索引是否缺失或冗余。
-
日志记录
- 迁移过程中建议启用 [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 |
注：
- 使用Ora2pg迁移时，对于复杂的存储过程和函数无法正确转换，需要手工迁移，若有更好的解决方案，可以评论或私聊，我们一起研究一下。
- 对于某些特殊的数据类型如bool，使用ora2pg迁移后，可能会被转成数字类型，需要后置手动修改。
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');