Oracle和Postgresql数据库SQL语法对比适配实战总结(基本涵盖90%开发需求)

最近应业务需求，公司需要把数据库从oracle数据迁移到postgresql库，以下简称pg库。代码逻辑是适用的，但是因为oracle语法比较随意，而pg库语法比较严格，这就涉及到整理哪些是oracle语法，以及替代函数等，现总结分享如下：

1.虚拟表dual 存在虚拟表dual查询
例：select 2 num from dual 直接去掉dual表(oracle示例)
例：select 2 num（pg库示例）

2.使用rownum 分页查询
例：

(select * from ( 
select *，rownum rownum from (A) row_ where rownum <= endIndex) where rownum_ > startIndex

直接用limit 1 offset 0替换

例：

select * from (A) limit （endIndex-startIndex offset startIndex

3.(+)连接符 连接符（+）跟在哪个条件后面就是哪张表被左连
例：

A左连B:
Select A.* from A,B where A.ID=B.ID（+）
1.B左连A：
Select A.* from A,B where A.ID（+）=B.ID	改成标准sql

例1：

Select A.* from A left join B on  A.ID=B.ID
例2:
Select A.* from A right join B where A.ID=B.ID

4.nvl空值判断 Nvl判断
例：

select nvl(t2.con, 0) con from A

使用coalesce(field, default)替代

例：select nvl(t2.con, 0) con from A

5.sysdate（） 返回系统当前时间
例：

Select sysdate  from A

使用now（）替代

例：

Select now() from A

6.instr()
例：

instr('be,ce,af,de,bf',duty.dutytype)

使用同名函数代替

例：

DECLARE  
    pos integer;  
BEGIN  
    pos:= instr($1, $2, 1);  
    RETURN pos;  
END;  

7.to_char

to_char里面为数字类型时会出错

例：

To_char(INTEGER)
	直接去掉该函数，拼接空字符串

例：
INTEGER||’’

7.sum oracle支持直接字符串类型的数字计算
例：

Sum（‘2’）	需要先用cast进行格式转换

例：

Sum(cast(‘2’ as INTEGER))

8.delete delete后面没有加from
例：

Delete a 表A	严格sql语法，需要加from；

例：

Delete a from 表A

9. Start with connect by prior
查找树形结构数据，根据prior关键字决定在节点位置是向上查找还是向下查找
例：

Select org.short_name orgName from common_maillist_rel_org org where org.nodestatus='0' "
      + " start with org.org_code ='"+orgCode+"' "    
      + " CONNECT BY PRIOR org.parent_code =  org.org_code
	使用RECURSIV代替

例：

WITH RECURSIVE t as ( SELECT x.parent_code ,x.org_code,x.short_name ,1 lv FROM common_maillist_rel_org x WHERE x.org_code= 'orgCode' AND x.nodestatus = '0' UNION ALL SELECT y.parent_code ,y.org_code ,y.short_name ,t.lv+1 lv FROM common_maillist_rel_org y JOIN t ON t.parent_code=y.org_code )SELECT * FROM t

10.to_date 日期转换函数
例：

Select t.TXSJ<to_date( endTime ,'yyyy-mm-dd hh24:mi:ss') 
	使用to_timestamp替代

例：

Select t.TXSJ<to_timestamp( endTime ,'yyyy-mm-dd hh24:mi:ss') 

11.nvl2 nvl2(列名,‘1’,‘0’)，与nvl相比多参数
例：

select  nvl2(c.readtime,'1','0') as rtime  from C c  
	多参数需要使用case when替代

例：

select (case when readtime is null then '0' else '1' end) as rtime from C c

12.字符’'和数值类型字段 Oracle宽sq语法，字符串和数值类型可以自动转换
例：

select org_code from common_orgdata_view where delete_flag <> 1

Pg库严sq语法，必须对应相应字段类型

例：

select org_code from common_orgdata_view where delete_flag <> ‘1’

13.查询语句from 问题 Oracle语法可以省略不写
例：

select * from (select * from a)
	Pg库子查询必须要有别名

例：

select * from (select * from a)as t

14.update语句as 问题 oracle语法同上
例：

insert into A select * from A

Pg库必须加as别名

例：

insert into A as select * from A

15.as 修饰别名关键字问题 Oracle中有些别名不是敏感字，但是pg库是特殊含义
例：

SELECT A.org_name name from A

Pg库中name是敏感字符，需要加as

例：

SELECT A.org_name as name from A

16.别名大小写问题 Oracle中别名大写可以随意
例：

SELECT parentcode PARENTCODE    from A
	PG库给别名必须加双引号""，不然始终是小写状态
例：SELECT parentcode 'PARENTCODE'  from A

17.定时任务start字段默认为’u0000’问题 定时器类方法，start字段默认为’u0000’，PG库并不支持’\u0000’，调用插入方法报错 定时器插入时是默认开启的，可以直接先给start赋值，setStar(true)

18.update别名点字段问题 Oracle支持随意别名各种点
例：

UPDATE common_maillist_org c set c.org_name='xxx'

Pg库不支持更新的时候给别名点字段

例：

UPDATE common_maillist_org c set org_name='xxx'

19.sys_date-1问题 Oracle中当前时间减去一年
例：

Sys_date-1	Pg库使用extract，减一年用year，小时用hour等

例：

Extract(year from sysdate)-1

20.decode问题 decode在oracle中含义：
Decode（条件,值1,返回值1,值2,返回值2…）
例：
Decode（a.text,null,0,1） Pg库中要么给所有的返回值改成字符串类型，要么使用case when代替
例：

Case a.text when a.text is null then a.text=0 else a.text=1 end

21.to_number问题 oracle中to_number里面可以转化字符串，但是pg不行
例：

To_number(‘1’)	pg库直接使用cast代替即可

例：

Cast(‘1’ as integer)

22.数据库uuid问题 Oracle的sql中支持uuid
Pg库需要在数据库创建uuid的同名函数

23.Boolean类型实体类 Oracle中char等类型可以对应实体类boolean类型，但是pg不行 Pg库对应的实体类需要将boolean字段改成对应数据库的char或者string类型

Pg库： 实体类：
Char char
Varchar string

24.round等统计函数 Oracle中像round，sum等统计函数均支持字符串类型计算 Pg库中对于round等函数必须先cast 字段as integer转成num类型

25.replace用法问题 把目标b替换为c：

Replace（‘ab’,’c’）
	Replace（‘ab’,’b’,’c’）

注：数据库有表，视图，函数等区分存储，直接在函数里面建立同名函数，里面内容为替代内容即可。