【Oracle】去IOE（IBM服务器,Oracle，EMC存储），Oracle迁移postgreSQL部分问题解决方案

Oracle迁移postgreSQL部分问题解决方案

    “去IOE化"理念最早是由阿里巴巴提出。具体就是要把公司里IBM服务器、Oracle数据库、EMC存储都替换掉，    
更换为开源或国产化的选项，postgreSQL 对Oracle兼容性比较好。

1.当前时间 oracle的sysdate

处理方法：未开启事务时用now()替换，一般项目也是没有事务的。在事务中用到sysdate时，建议用clock_timestamp()替换。

例子

select sysdate from dual   等价于  select now()

2.序列 oracle的SEQNAME.NEXTVAL

处理方法：pg的NEXTVAL（'SEQNAME'）等效于SEQNAME.NEXTVAL
pg还提供了其他几种处理序列的方法
    currval('SEQNAME')  输出sequence的当前值 
    setval('SEQNAME')   设置sequence将要输出的下一个值

例子

select SEQNAME.NEXTVAL from dual 等价于 NEXTVAL('SEQNAME')

3.虚表 oracle的from dual

处理方法：直接去掉

4.空值处理函数 Oracle的nvl

处理方法： NVL可以用COALESCE函数替换

例子

select nvl(null,0) from dual 等价于 select COALESCE(null,0)

5.空值处理函数 Oracle的nvl2

处理方法：select nvl2(列名,1,0) from dual 等价于 select case when 列名 is null then 0 else 1   
  ------用case when 解决

例子

select nvl2(null,1,0) from dual 等价于 select case when null is null then 0 else 1 end

6.自动类型转换

pg不支持强自动类型转换 解决方法 使用 
方法1：cast（列名 as 类型）或者 
方法2：列名::类型    来转换
类型问题多集中在字符和数字之间 
pg支持弱自动转换 比如  numeric和integer，smallint，bigint之间是可以自动转换的。
					text和character之间的转换

例子 字符和数字之间的转换解决方案

select * from  tb_hello where '1'=1  
等价于 
select * from  tb_hello where cast('1' as integer)=1
select * from  tb_hello where '1' :: integer=1

7.INSTR函数 字符查找函数

Oracle的instr()有两种表达格式
格式一：instr( string1, string2 )    // instr(源字符串, 目标字符串)

格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] )   // instr(源字符串, 目标字符串, 起始位置, 匹配序号)

对于格式1 
处理方法  直接用strpos替换

对于格式2
处理方法  重写一个函数instr来处理（百度搜一下）

例子

select instr('hello','l') from dual 等价于
select strpos('hello','l')

8.substr() 截取字符函数

oracle的substr()有两种格式
格式1： substr(string string, int a, int b);
			##从字符串的第a位开始截取b个字符形成新字符串
格式2：substr(string string, int a) ;

对于格式1：
如果a>0 那么可以直接替换为substring()
如果a=0 那么替换为1
如果小于0  处理就比较麻烦 下面会给出具体处理办法。

对于格式2：
直接替换为substring()

例子

格式1（a>0）和a=0的情况   --格式2情况就不讲解了
select substr('hello',0,2) from dual   ##截取字符第一位开始2位 返回截取的字符
select substr('hello',1,2) from dual   ##截取字符第一位开始2位 返回截取的字符  两者结果一致
等价于
select substring('hello',1,2)
select substr('hello',1,2)

对于a<0的情况  
select substr('hello',-a,2) from dual ##oracle从字符串倒数定位字符，所以意思就是从str倒数第一位开始截取2位组成字符串
解决方法：
select substring('hello',CHAR_LENGTH('hello')-a+2,2) ##具体意思自己理解

9.连接

oracle的连接可以简写为（+）
解决办法：替换为join   口诀：那个表的字段后（+） 那么就left join 那个表。

例子

select a.id,b.name
	from tb_hello a,tb_hi b
	where a.id=b.id(+)
等价于
select a.id,b.name
	from tb_hello a
	left join tb_hi b on a.id=b.id

10,层次查询（递归）

oracle的start with connect by 函数实现层次查询  
	对于下表sr_menu给出递归查询在oracle和pg的实现
id | parent |  title  | recursion_level
----+--------+---------+-----------------
  1 |        | level 0 |               1
  2 |      1 | level 1 |               2
  3 |      1 | level 1 |               2
  4 |      1 | level 1 |               2
  5 |      3 | level 2 |               3
  
解决：pg中使用WITH RECURSIVE语句实现递归

例子（对于上面的sr_menu表给的例子）

oracle 写法：
	select * from sr_menu          ##需要查询的列。
		start with id = 1 			##查询初始条件
	connect by prior id = parent;  	##递归条件
	
pgsql 写法：
	WITH RECURSIVE a AS (
		SELECT id, parent, title
  		FROM sr_menu
  		WHERE id = 1               ##查询初始条件
	UNION ALL
  		SELECT d.id, d.parent, d.title
  		FROM sr_menu d
  		JOIN a ON a.id = d.parent )   ##递归条件
	SELECT * FROM a; 				  ##需要查询的列。

11.数据库对象大小写

oracel  不区分大小写
pgsql   大小写区分 创建数据库对象时要小写，这样才不区分SQL的大小写

12.ROWNUM虚列

oracle会为每个表默认生成一个虚列ROWNUM，pg不会。
在我们使用ROWNUM一般分2种情况
1.限制结果集数量，用于翻页等    处理方法为 limit关键字
2.生成行号                   处理方法为 用ROW_NUMBER() OVER()来生成行号

例子

对于第一种情况

select *  from  tb_hello  where rownum <=10  
替换为
select *  from  tb_hello  where 1=1 limit 10    ##注意limit只能用在where条件结束之后


第二种情况
select *,rownum from tb_hello 
替换为
select *,ROW_NUMBER() OVER() as rownum from tb_hello

13.DECODE等判断函数

语法：decode(条件，值1，返回值1，值2，返回值2，...值n,返回值n，缺省值) ##当条件的值为值1时，返回返回值1，为值2时 返回返回值2......
oracle的decode类似java的case语句   
所以在pg中也是用case when语句来实现替换

例子

select decode(X，A，B，C，D，E）from dual
等价于
select case X when A then B when C then D else E end 

14.to_char()

to_char()在oracle中几乎可以满足任何入参转换
在pg中却不同，举例说明

例子

select to_char('hello') oracle可行，pg报错  ##pg不能转换字符类型
解决： 1,重写to_char()函数 支持入参为字符串   2.使用cast()替换to_char()

select to_char(1111)   oracle可行 ，pg报错  ##pg转换数值型时必须接上精度
解决 
select to_char(1111,'fm99999')

15.to_date()

to_date()函数
oracle：
	to_date('','YYYYMMDD')只能匹配'20200102'     
'yyyy/MM/dd'和'yyyy-MM-dd'匹配所有三种时间格式字符串（'20200304','2020-03-04',2020/03/04'）
pg：
	pgsql的to_date()函数格式要求严格一些，‘yyyyMMdd’匹配‘2020-01-03’和‘20200103’ 。 ‘yyyy/MM/dd’匹配‘2020-01-03’和 ‘2020/01/03’  。 ‘yyyy-MM-dd' 匹配  ‘2020-01-03’和 ‘2020/01/03’  。   

16.NULL和''

ORACLE认为''等同于NULL
pg认为NULL和''不同
关于null和''解决可以用case when或者COALESCE来处理

oracle的 LENGTH('')为NULL
pg的LENGTH('')为0

oracle的TO_DATE('','YYYYMMDD')为空
pg的TO_DATE('','YYYYMMDD')为0001-01-01 BC

oracle的TO_NUMBER('',1)为NULL
pg的TO_NUMBER('',1),报错

18.ADD_MONTHS(DATE,INT)

oracle的ADD_MONTHS(DATE,INT) 指在给定的时间戳上增加或减少固定个月份
解决方法 1.创建add_month(date,int)函数  2.用- interval关键字解决

例子

select add_months(sysdate,1) from dual
等价于
select now() -interval '1 month'

19.子查询别名

oracle 子查询可以没有别名
select * from (
select * from tb_b_dim_latn_all);
或者
select * from (
select * from tb_b_dim_latn_all);
pg 必须写别名
select * from (
select * from tb_b_dim_latn_all) t;

20.查询当前登陆用户

SELECT USER FROM DUAL  ##oracle
select current_user    ##pg

21.MINUS 取差集

##orace
MINUS语句：返回两个结果集的差（即从左查询中返回右查询没有找到的所有非重复值）。

##pg
EXCEPT 返回两个结果集的差（即从左查询中返回右查询没有找到的所有非重复值）

例子

select 1,2,3 from dual
MINUS
select 1,2,3 from dual
等价于
select 1,2,3 
EXCEPT
select 1,2,3 

扩展

pg中
	INTERSECT 返回 两个结果集的交集（即两个查询都返回的所有非重复值）。
	union返回两个结果集的并集。

22.update语句

uodate 语句 在pg中尽量不要别名，oracle可以使用别名。在pg使用别名会报错

例子

update tb_hello t 
set t.hello_id=1  			##在oracle可以，在pg会报错。

23.分组连接函数和group by连用（列转行）

oracle中
	wmsys.wm_concat(列)默认拼接逗号
	LISTAGG()和wmsys.wm_concat(列)类似
解决办法
pg中提供三个函数解决
	string_agg(列，‘，’  order by 列)来替换wmsys.wm_concat()函数
	array_agg()来替换LISTAGG()函数。

同时pg还提供了xml_agg()函数。

例子

select id,wmsys.wm_concat(name)
	from tb_hello 
	group by id
	
等价于
select id,string_agg(name,',' order by name)
	from tb_hello
	group by id