去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 函数实现层次查询
oracle 时间转换对于下表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
<_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')
<_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
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论