记录一下信创改造遇到的问题

Oracle迁移gaussdb

迁移gaussdb使用的是华为官方提供的华为UGO+DRS工具实现，还是比较简单方便的 迁移过程中 需要注意所有表需要有主键，而所用视图、函数、触发器、存储过程必须是有效状态，才能迁移成功。人工干预的比较少，相对稳定 效果还是不错的。

信创服务器 银河麒麟+arm架构

1.会存在dns解析问题 导致Java应用启动慢反应慢。
2.会存在字体库问题 导致Java 操作excel pdf出现乱码 抛异常等 例如：Aspose.cell Excel转Pdf抛出CellsException
3.中间件采用的 tongweb，tongweb需要有授权文件license.bat 才能启动运行

Java程序sql的适配问题

虚拟表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)  

日期操作函数

select sysdate; --Timestamp
select now(); --Timestampz
/**
YYYY 表示4位年份
MM 表示2位月份
DD 表示2位天数
HH24 表示24小时制的小时数
MI 表示分钟数
SS 表示秒数
*/
-- 时间戳转字符串
SELECT to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date;
-- 字符串转日期
SELECT to_date('2025-01-01', 'YYYY-MM-DD');
SELECT CAST('2025-01-01' AS DATE);
-- 日期截取
/**'microseconds'（微秒）
'milliseconds'（毫秒）
'second' 或 'seconds'（秒）
'minute' 或 'minutes'（分钟）
'hour' 或 'hours'（小时）
'day' 或 'days'（天）
'week' 或 'weeks'（周）
'month' 或 'months'（月）
'quarter' 或 'quarters'（季度）
'year' 或 'years'（年）‌*/
SELECT date_trunc('day', current_timestamp); --2025-01-20 00:00:00.000 +0800
SELECT date_trunc('hour', current_timestamp);--2025-01-20 17:00:00.000 +0800

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
select to_char(12345) as string_value from dual;
select to_char(123.45, ‘fm99999d99’) as formatted_string from dual;	select your_numeric_column::varchar from your_table;