ORALCE整合兼容PGSQL

ORACLE整合兼容PGSQL

最近经理让我做oracle整合兼容PG，说白了就是把现在项目里的oracle语句改成既能兼容oracle又能兼容pg的语句，下面是自己的一些记录，供自己学习积累。

1. Merge into
   这个merge into在我整改的这个项目里，用的最多的地方就是导入功能，但是在pg里面是不支持merge into所以只能换种写法，所以这边我换成了比较简单的，update+insert的写法，这边先进行插入再进行更新操作，条件就是原先merge into里面的条件就可以了。

2. ‘‘和null
   这边在项目中会有一些sql脚本，其中insert语句会在数据库表中进行一些初始值的插入，有一些字段会给个空值，就是’‘或者null，这个在oracle 中赋值查值没啥太大区别，但是在pg中是有区别的，在pg中’‘并不代表null，而是代表空字符串,’‘和null是两个概念。所以，这边为了兼容pg，把insert 脚本里面的’‘替换成null，这里为什么不直接都用’’，有两个原因，因为有的字段为时间字段，插不进去，还有一个原因是因为方便使用 is null 或 is not null查询数据。

3. update form
   在pg中是支持update from写法的，例如：

   update ta a set name = b.name,
   tel = b.tel
   from tb b  
   where a.id = b.id 
   and b.type is NULL ...;
   

   但是，在oracle 中是不支持的，这里可以用其他写法代替，例如update select

   update ta a
   set (name,tel) = 
   (select name,tel 
    from tb where a.id = b.id) 
   where exists (
   	select 1 
   	from tb b
   	where a.id = b.id
   	and b.type is null...
    )
   

4. 两个数比较
   在oracle 中可以’1’>0这么写，当然严格点需要将数字字符串转成数字类型，但是，PG是不支持这么写的，必须严格的写法to_number(‘1’)>0

5. 存储过程
   在pg里面是不支持存储过程的，我这边的做法都是建成相同名字的方法function，当然这边需要判断当前所连接的数据库是oracle 还是pg。

6. 视图
   这边如果前一个脚本已经创建了视图，在后面新添加的脚本里面想要修改这个视图，虽然已经写了carete or replace 但是在跑脚本的时候还是提示视图已经创建了，解决办法就是简单粗暴的先删除，再重新创建，这边最好也要判断一下当前数据库。

7. update
   在oralce中

   update ta set ta.name = '1' where ta.type = '0';
   

   是可以的，但是在pg中就报“relation does not exist”什么关系不存在，这时候改成

   update ta set name = '1' where type = '0';
   

   就可以了。

8. 保留小数问题
   pg里面做除法‘/’是不会自动保留小数的，这也与oralce不同，例如：

   select 7/2 from dual;
   

   pg结果为3，oracle为3.5，这里如果想要在pg里面也要保留小数的话，可以使用round()函数改造一下。

   select round(round(7,1)/2,1);
   

   具体保留几位，自己设置就行了。

9. decode()
   在oralce中decode(type,null,1,2)表达的意思，如果type为null那么赋值为1，否则默认赋值为2，这在我们写select语句经常会用到的函数，但是在pg里面这么写可能会出现问题，如果type的值为null，那么在select decode(type,null,1,2)的时候就会是null而不是1，显然是有问题的，所以解决这个问题，可以在decode函数里面在嵌套一个nvl函数decode(nvl(type,‘’),‘’,1,2),这边nvl默认值写’‘的原因为如果type是’‘那么nvl返回的值为’'（这边如果是在oracle环境基础上兼容pg的话，而且涉及的sql比较多的话，那么就建议修改decode方法了）

10. 递归查询
    oracle中可以通过start with … connect by 来操作，但是在pg中是没有这个语法的，但是pg自带了with recursive
    例如：
    现有t_cs_test表，其中cid为本身节点（子节点），pid为隶属节点（父节点）
    

有如下查询：

1.查询结果自己所有的后代节点（包括自己）
oracle：
sql select cid,pid from t_cs_test start with cid='3' connect by prior cid = pid

pg：

with recursive cte as(
    select x.* from t_cs_test x where x.cid= '3'
    union all
    select y.* from t_cs_test y join cte on y.pid = cte.cid
)select cid,pid from cte order by cid

2.查询结果自己所有的前代节点（包括自己）
oracle:

select cid,pid from t_cs_test start with cid='3' connect by cid = prior pid

pg：

with recursive cte as(
    select x.* from t_cs_test x where x.cid= '3'
    union all
    select y.* from t_cs_test y join cte on y.cid = cte.pid
)select cid,pid from cte order by cid

常用的就这两个场景，剩下的还有oracle下：

查询结果自己所有的后代节点（不包括自己）
select * from t_cs_test start with pid='3' connect by prior cid = pid

查询结果自己的第一代后节点和所有的前代节点（包括自己）
select * from t_cs_test start with pid='3' connect by cid = prior pid

上面这两个可以自行在pg里面替换一下，和前两个场景差不多，在此不再赘述

11.delete from rownum
oracle中可以使用delete from rownum，而在pg中是不行的，首先pg中没有rownum，但是使用delete from limit 也不可以，但可以使用with as语法，例如：

with a as
(select id from t_test where xn like '%2020%' limit 10)
delete from t_test where exists (
	select 1 from a
	where t_test.id = a.id
)

12.rownum
oralce中有rownum行号可供条件查询，pg中没有，但是可以用row_number()函数来代替，例如：

oralce：

select * from a where rownum <=10

pg:

select * from (select row_number() over(order by wid) as rownum,* from a) where rownum <=10

13.列转行
我们都知道oracle 里面实现列转行有wm_concat函数，在pg里面是没有这个函数的，但是pg也自带了列转行函数，如下：

第一种，string_agg(expression, delimiter)直接把一个表达式变成字符串

第二种，array_agg(expression)把表达式变成一个数组 ，一般配合 array_to_string() 函数使用

14.求交、并、差集的方法
oracle中可以使用关键字minus来求两个并集和交集，pg中不支持这个关键字，但是pg里面也提供了类似的关键字供我们使用，如下：

UNION 关键字求两个结果集的并集

INTERSECT 求两个结果集的交集

EXCEPT 求两个结果集的差集

14.删除指定分区数据

在oracle中可以使用 ALTER TABLE tablenameTRUNCATE PARTITION partionName;来清空指定分区的数据，而在pg中则使用TRUNCATE partionName;来执行相同的操作，因为在pg数据库中每个分区就相当于一张表，所以直接清空这个表里的数据就可以了。

未完待续。。。

最后，附上pg的一个中文手册，可以进一步的学习pg。

PostgreSQL 9.5.3 中文手册