oracle转postgreSQL修改点

从2019年开始，就有一个很火热的话题：“去O化”。O就是oracle，也就是将oracle替换成别的数据库。为什么要去O？大致有以下原因：

• oracle是收费的，为了进一步降低成本；
• 以美国为首的西方国家对华科技种种遏制行为，最近越闹越厉害，最近docker的付费服务就禁止中国企业使用；
• 甲骨文公司中国区大幅度裁员，或将放弃中国市场也说不定；
• 2020年12月31起，甲骨文公司将不再对oracle11.2版本提供技术支持，即出现bug也不会维护了。

鉴于以上种种原因，很多企业都在更换数据库，但是如果新的数据库语法和oracle差别很大，那工作量会特别大，权衡之下，postgreSQL是个不错的选择。

本人前两周就在做这个“去O化”，将遇到的改造点记录下来，供大家参考。

1、jar包/maven依赖的更换：

驱动包要换成postgresql-xxx.jrex.jar，x表示版本。如果是maven项目，则添加如下依赖：

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>版本</version>
</dependency>

2、driverClassName等信息的更换：

datasource.driverClassName=org.postgresql.Driver
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

3、字段类型问题：

• 主外键字段类型问题：A表的主键在B表做外键时，这个字段在两张表的类型一定要一致，否则连接查询会报错。
• 实体类与数据表字段类型问题：实体类字段类型一定要与数据表字段类型对应，否则映射的时候就报错了。
• 查询条件的字段类型问题：假如数据表中id字段类型是int4，查询条件传String类型的1，是会报错的，要用Integer类型，而oracle不会报错，会自动进行类型转换。

4、postgre与java字段类型对照表：

以下是常用字段类型对照：

5、序列问题：

• oracle获取序列：my_sequece.nextval
• postgre获取序列：nextval('my_sequece')

6、sysdate替换方案：

7、NVL函数替换方案：

oracle中的NVL(arg1, arg2)用来设置默认值，arg1为空就设置为arg2。postgre中可以用coalesce(arg1, arg2)实现相同效果。

8、分页问题：

比如要查询user表第一页，每页显示10条数据：

• oracle中用rownum进行分页：

select * from (select aa.*, rownum rn from (select * from user) aa where rownum <= 11 ) where rn > 1;

• postgre中用limit进行分页：

select * from user limit 10 offset 1

查询user表5条数据：

• oracle写法：

select * from user where rownum <= 5

• postgre写法：

select * from user limit 5

9、不等于的问题：

postgre中column !=-?会报错，负号和数字应用括号括起来，或者!=统一改成<>。

10、数字类型字段模糊查询问题：

非String类型的字段要进行模糊查询，需要先将数据库字段类型转成varchar，如下：

and cast(user_phone as varchar) LIKE ?

11、update语句相关问题：

postgre中，update语句不能设置表别名，否则会报错。

12、count和order by的问题：

select count(*)的语句不能加order by，否则会报错。

13、order by的问题：

group by的字段一定要在select中查出来，并且如果group by的字段有使用函数，select的时候也要使用相同的函数，例如：

select upper(user_id) from user order by upper(user_id)

14、blob类型的问题：

oracle中用blob类型可以存储文件，在java中也用blob类型对应。postgre与之对应的是bytea，在java中用byte[]数组对应就可以了。

15、clob类型的问题：

oracle中用clob存储大文本，在java中也用clob类型对应。postgre与之对应的是text，在java中直接用String对应就可以了。

16、decode函数问题：

oracle中的decode(arg1, arg2, arg3, arg4)函数，表示：当 arg1 等于 arg2 时，取 arg3 ，否则取 arg4。postgre中没有类似的函数，可以用如下方式实现：

case when arg1 = arg2 then arg3 else arg4 end

17、to_date函数的问题：

特别注意，postgre中to_date函数转出来的是不带时分秒的时间，如果想要带时分秒的，需要用to_timestamp。

18、substr函数的问题：

substr(arg, begin, num)函数，表示对arg进行截取，从第begin位开始，截取num个。oracle中，arg可以是字符串也可以数字类型，但是postgre中只支持对字符串的截取，如果要对数字进行截取，得写成：SUBSTR(1.23 :: TEXT,1,3)。还有一点，oracle中substr(1.23, -2)表示截取最后两位，结果就是23，postgre中不支持这种用法，要实现相同功能，可以用right函数：right(1.23::text, 2)。

19、 listagg(column1, ',') within group(column2)函数的问题：

这个函数的意思是将column2相同的多行记录的column1的值合并成一行，例如：

SELECT tr.tr_gw_no,
        listagg(tr.tr_status, ',') WITHIN GROUP(ORDER BY tr.tr_status) status
FROM trade tr
where tr_gw_no = 12198006
     or tr_gw_no = 12167001
GROUP BY tr.tr_gw_no;

查询结果就是：

查询结果

postgre中可以用string_agg函数实现相同的功能，具体用法如下：

SELECT
    tr_gw_no,
    string_agg ( tr_status :: TEXT, ',' ) status
FROM
    trade 
WHERE
    tr_gw_no = 12198006 
    OR tr_gw_no = 12167001 
GROUP BY
    tr_gw_no;

20、start with connect by函数的问题：

oracle的这个函数是用来查树形结构的，即同一张的表的记录有父子级关系的那种。oracle中用法如下：

select module_id
    from sys_modules
start with module_id = ?
connect by prior module_id = module_fid;

这就表示查询module_id为?的所有子module，即父模块idmodule_fid为?的所有的记录。postgre可以用WITH RECURSIVE实现相同的效果，如下：

WITH RECURSIVE subtabela AS (
    SELECT
        module_id 
    FROM
        sys_modules 
    WHERE
        module_id = ? UNION ALL
    SELECT
        tt.module_id 
    FROM
        sys_modules tt
        INNER JOIN subtabela st ON tt.module_fid = st.module_id 
    ) SELECT
    * 
FROM
    subtabela;

21、存储过程调用的问题：

postgre11开始，支持存储过程procedure，之前的版本只支持function。通过代码去调用存储过程时，要注意以下三点：

• 调用存储过程的sql语句不需要加大括号，加了大括号的调用的是function而不是procedure；
• 如果存储过程中用了事务，那个在代码中调用时就不要使用spring的事务了，否则会冲突；
• 如果存储过程有inout参数，表示这个即是输入又是输出参数，都要进行设置，oracle可以只当作输入或者输出来用。

比如现在有一个名为test_procedure(IN "id" int4, INOUT "result" int4)的存储过程，java中调用方式如下：

Session session = sessionFactory.getCurrentSession();
Connection conn = session.connection();
CallableStatement cs = null;
Integer returnInfo = -1;
cs = conn.prepareCall("call p_paynum_processing_create(?,?)");
// oracle中调用要加大括号
// cs = conn.prepareCall("{call p_paynum_processing_create(?,?)}");
// 输入的参数
cs.setInt(1, id.intValue());
// 输出的参数
cs.setInt(2, -1); // oracle中这一行可以不用
cs.registerOutParameter(2, Types.INTEGER);
// 执行存储过程
cs.execute();
// 获取返回值，-1为操作异常
returnInfo = cs.getInt(2);

22、连接查询的问题：

oracle中外连接可以这样写：

select * from tableA a, tableB b  where a.id = b.id(+);

有(+)的一方是副表，另一方是主表，即上面那种写法表示左外连接。postgre不支持这种写法，可以用left join代替。

23、instr函数的问题：

instr函数表示包含，postgre中可以用strpos函数替代。