赞
踩
迁移gaussdb使用的是华为官方提供的华为UGO+DRS工具实现,还是比较简单方便的 迁移过程中 需要注意所有表需要有主键,而所用视图、函数、触发器、存储过程必须是有效状态,才能迁移成功。人工干预的比较少,相对稳定 效果还是不错的。
1.会存在dns解析问题 导致Java应用启动慢反应慢。
2.会存在字体库问题 导致Java 操作excel pdf出现乱码 抛异常等 例如:Aspose.cell Excel转Pdf抛出CellsException
3.中间件采用的 tongweb,tongweb需要有授权文件license.bat 才能启动运行
虚拟表dual
Oracle获取一个常量需要通过一个dual,guassdb/Opengauss不需
虚拟列rownum
对于查询返回的每行数据,rownum虚拟列会返回一个数字,第一行的ROWNUM为1,第二行为2,以此类推。
rownum在select列表中时重写为row_number() over ()
rownum在where子句中时重写为limit… offset…
虚拟列rowid
Oracle中的rowid虚拟列返回特定行的具体地址,在gauss中重写为tableoid || ‘#’ || ctid
字符串函数
nvl(col, value)
Oracle中的nvl(col, value)用来设置默认值,col为空就设置为value;
在gauss中重写为coalesce
Oracle中的decode(arg1, arg2, arg3, arg4)函数, 表示当 arg1 等于 arg2 时,取 arg3,否则取 arg4。
postgre中没有类似的函数,可以重写为case… when…
但是guassdb 已经兼容decode 函数了
Oracle中的add_months 函数主要是对日期函数进行操作,对日期按月增加。在Opengauss没有对应的函数,需将其转化为基于日期和interval的运算。或者自己写一个add_months 函数代替,
guassdb数据库中 string_agg()函数 可以实现列转行,将某个字段值连接成一个字符串,并用逗号和空格(, )作为分隔符。
string_agg(column_name, separator)
Oracle | gaussdb/openguass |
---|---|
select 2 from dual | select 2 |
select rownum from customer; | select row_number() over () as rownum from customer |
select tableoid from customer where rownum < 10 and rownum >= 2; | select tableoid from customer limit 9 OFFSET 2 |
select rowid, c.* from customer c; | select tableoid || ‘#’ || ctid, c.* from customer as c |
select nvl(c_phone, 1) from customer; | select coalesce(customer.c_phone, ‘1’) from customer |
select nvl2(c_phone, 1, 2) from customer; | select case when c_phone is null then 1 else 2 end from customer |
select decode(c_phone,‘110’, 1 , 2) from customer; | select case when c_phone = ‘110’ then 1 else 2 end from customer |
select substr(c_phone, 1 , -2 ) from customer; | select substr(c_phone, 1, length(c_phone) - 2) from customer |
select instr(‘123’, ‘23’) | select strpos(‘123’, ‘23’) |
select replace(‘123’,‘1’); | select replace(‘123’,‘1’,‘’); |
select listagg(c_name,‘,’) as name from customer group by c_phone | select string_agg(c_name,‘,’) as name from customer group by c_phone |
select listagg(c_name,‘,’) within group(order by c_name) over (partition by c_phone) as name from customer; | sselect string_agg(customer.c_name, ‘,’) over (partition by customer.c_phone order by c_custkey) as name from customer |
select listagg(c_name,‘,’) within group(order by c_name) as name from customer group by c_phone; | select max(paw_dt.name) as name from (select string_agg(customer.c_name, ‘,’) over (partition by customer.c_phone order by c_name) as name, customer.c_phone from customer) as paw_dt group by c_phone |
select listagg(c_name,‘,’) within group(order by c_name) as name from customer group by c_phone | select string_agg(c_name,‘,’) as name from customer group by c_phone |
select sysdate | select sysdate/select current_timestamp |
select sysdate() | select sysdate/select now() |
select systimestamp | select current_timestamp |
select trunc( 111.23,2) | select trunc( 111.23,2) |
select trunc(sysdate,‘year’) | select date_trunc(‘year’, current_timestamp) |
select trunc(sysdate) | select date_trunc(‘dd’, current_timestamp) |
select add_months(sysdate, 2) | select current_timestamp + 2 * interval ‘1 month’ |
select add_months(sysdate, 2) | select cast(date_trunc(‘MONTH’, current_timestamp) + interval ‘1 MONTH - 1 DAY’ as date) |
select c_name from customer having count(*) > 2 group by c_name | select c_name from customer group by c_name having count(*) > 2 |
select unique c_phone from customer | select distinct customer.c_phone from customer |
select c_custkey from customer minus select o_custkey from orders | select c_custkey from customer except select o_custkey from orders |
delete customer where 1=0; | delete from customer where 1 = 0; |
insert into customer nologging select * from customer_bk; | insert into customer select * from customer_bk; |
insert into t as select c1 from t1 | insert into t select c1 from t1 |
select * from (select * from CUSTOMER) | select * from (select * from CUSTOMER) as foo |
update customer c set c.c_name = ‘xxx’ where c_custkey = 1; | update customer set c_name = ‘xxx’ where c_custkey = 1 |
select substr(1234.1, 0, 4) | select substr(‘1234.1’, 1, 4+1) |
select substr(‘1234.1’, 0, ‘2’) | select substr(‘1234.1’, 0, 2) |
select sum(‘2’) | select sum(2) |
select round(‘2’) | select round(2) |
select to_number(c_phone) from customer; | select cast(c_phone as numeric) from customer |
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。