人大金仓数据库适配问题收集
一、数据库迁移工具:二、人大金仓与MySql不同的地方:三、人大金仓的SQL:四、MySql函数在人大金仓中的替换:五、人大金仓中最坑的时间函数:六、人大金仓写函数时遇到的坑总体来说,数据库适配过程中,需要对代码改动的,主要是不同的数据库对函数的支持不同,同一个函数或同一个功能,MySQL和Oracle的都可能不一样,此前代码对这两大主流数据库做了适配。此次金仓适配,比较耗时间的代码修改工作,也主要来自这个。
一、数据库迁移工具:
迁移工具迁移时可以对数据库表的字段、索引、约束、主键进行迁移,但迁移过程中要注意:
1、迁移索引、约束、主键名不能有重复的,在MySql中设计时如果名字重复不会报错,导入时如果A表导入成功,B表导入时有重名则不会设置,会被跳过。
2、对现有数据库转库时,转库程序没有转换视图,视图需要手动添加。
3、对于函数的迁移,只会机械的把内容复制过来,并不会完全自适应,比果MySql自带函数但人大金仓没有的,不会自动转换,以及各种机制不同造成的BUG,比果人大金仓在返回字附串的空“” 时实现返回的是 null, 也都要手工再次调试。
4、如果查询是报字段不存在,但是确认该字段是存在的,可能是导入时大小写的问题,先将该字段改名保存,后再把字段名改回来后即可恢复正常
二、人大金仓与MySql不同的地方:
1、金仓SQL对于GROUP BY比MySQL要求更严格,字段必须出现在 GROUP BY 子句中或者在聚合函数中使用,才可以在结果里展示。
2、字符串判空问题,我们使用的版本金仓直接把空字符串当成null,这样在sql里使用 a = ''判断空就会有问题。这个可以自己修改配置,来确定字符串的空值类型。需要改一下数据库data目录下的kingbase.conf文件;添加 参数ora_input_emptystr_isnull=off; 重启数据库 生效
3、判断字符串长度的坑,在判断字符串的长度时,人大金仓如果长度为零不返回 0 而是返回 null,人大金仓判断字符串的长度有两个 length 和 char_length,效果是一样
下面代码,代码的条件不符合要求
下面展示一些内联代码片
。
tempStr = '';if(tempStr is null | length(tempStr) <=0){// 代码略:在字符串长度为 0 时进不到这里}
需要修改成如下:
tempStr = '';if(tempStr is null | length(tempStr) is null){// 代码略:}
三、人大金仓的SQL:
1、对字段或是查询内容进行 命名列名或者表名操作时必须加 AS , 而 MySQL 是可以不写的
select * from (select *,(select * from X where x.punid = t.punid) AS XUNIDfrom T) AS TT
2、updata 数据库时不可以对同一字段SET 两次,即使SET的值是一样的也不行,OA代码生成器生成的UNID就会被 set 两次,如:
错误:对同一列"UNID"进行了多次分配
UPDATE sys_XXXX set UNID = '', UNID= '' where UNID = ''
3、MySql 中有时会用 ` 符号包住字段,在人大金仓中是不充许的,没有这个语法,如:
SELECT `NAME` FROM sys_operator
四、MySql函数在人大金仓中的替换:
1、MySql 中的 LOCATE 函数判断 字符串在另一个字符串中出现的位置, 用 strpos 函数替换
2、MySql 中的Group_concat 函数将结果用 , 号进行分隔聚合查询 使用 string_agg 代替,具体语法
string_agg (字段名, ‘,’)
string_agg (字段名, ‘,’ ORDER BY 字段)
3、MySql 中的 FIND_IN_SET 函数判断字符串是否包含,使用 position 代替, position (‘ab’ in ‘ab,bc,de’);自写 FIND_IN_SET函数
4、MySql 中的 CONVERT(‘123’, SIGNED) 是将 字符串转为数字,人大金仓中 CONVERT 是用来转换字符集,如UTF-8 转 GBK
使用 to_number 函数代替
5、MySql 中的 CURRENT_TIMESTAMP() 函数,在人大金仓中存在但是,人大金仓中应该不是以函数的形式存在,人大金仓中不需要括号:
CURRENT_TIMESTAMP 即可
五、人大金仓中最坑的时间函数:
date_format无法识别,但是to_date、to_char支持,需要使用oracle风格的写法
trunc处理日期函数,在金仓里不可以,需要使用date_trunc
SELECT date_trunc( 'day', TIMESTAMP '-7-13 11:30:35' ) - date_trunc( 'day', TIMESTAMP '-7-03 11:30:35' );
trunc处理日期相减的值,不支持,可以使用 to_number
SELECT to_number(date_trunc( 'day', TIMESTAMP '-7-13 11:30:35' ) - date_trunc( 'day', TIMESTAMP '-7-03 11:30:35' ));
datediff函数不支持,可以使用to_number和date_trunc结合使用
select to_number(date_trunc('day', ISNULL(b.reply_date,now())) - date_trunc('day', a.ADD_DATE))
于是在人大金仓上调试结果,结果都是正常的,
-- 服务器当前时间select now();-- 保留两个小数点select round(100*(1.0/7.0),2);-- 关于数字转时间,以及精度的控制-- 精度有以下几个选项-- millennium century decade-- year quarter month week day hour-- minute second millieconds microsecondsselect now(); -- -08-16 15:16:00SELECT date_trunc( 'second', TIMESTAMP '-08-16 15:16:00' ); -- -7-01 15:16:00SELECT date_trunc( 'day', TIMESTAMP '-08-16 15:16:00' ); -- -7-01 00:00:00SELECT date_trunc( 'day', TIMESTAMP '-7-13 11:30:35' ); -- -7-13 00:00:00SELECT date_trunc( 'second', TIMESTAMP '-08-16 15:16:00' ); -- -7-01 15:16:00SELECT date_trunc( 'hour', TIMESTAMP '-08-16 15:16:00' ); -- -7-01 15:00:00-- 两个时间相减,计算相差天数SELECT date_trunc( 'day', TIMESTAMP '-7-13 11:30:35' ) - date_trunc( 'day', TIMESTAMP '-7-03 11:30:35' );-- 10 daysSELECT date_trunc( 'day', TIMESTAMP '-7-01 11:30:35' ) - date_trunc( 'day', TIMESTAMP '-7-03 11:30:35' );-- -2 daysSELECT to_number(date_trunc( 'day', TIMESTAMP '-7-01 11:30:35' ) - date_trunc( 'day', TIMESTAMP '-7-03 11:30:35' ));-- -2-- 两个时间相减,计算相差秒数, 第一个时间为当前时间SELECT to_number(now() - date_trunc( 'second', TIMESTAMP '-08-16 15:29:50' )); -- -2.212-- 两个时间相减,计算相差秒数, 第一个时间为当前时间, 不要小数点SELECT round(to_number(now() - date_trunc( 'second', TIMESTAMP '-08-16 15:29:50' )),0); -- -2-- 两个时间相减,计算相差秒数, 第一个时间为当前时间, 保留两位小数点SELECT round(to_number(now() - date_trunc( 'second', TIMESTAMP '-08-16 15:29:50' )),2); -- -2
但是,理想很丰满,现实很骨感,上面存在非常大的BUG
第一个BUG,在写SQL时 TIMESTAMP 后面不支持字段
– 报错 错误提示 语法错误 在 “t” 的附近的 position:11
select t.* from xxxx twhere 1 = 1and to_number(now() - date_trunc( 'second', TIMESTAMP t.xxxx )) >= 0
第二个BUG,在函数中不能用变量
– 保存时报错,错误在 time1附近
-- time1 为入参数,类型为字附串return to_number(now() - date_trunc( 'second', TIMESTAMP time1)) >= 0
所以我就想问我要这个有什么用,有什么用,有什么用!浪费我一天时间。
最后用了下面的方法:
select to_date('-04-02 11:30:10','yyyy-MM-dd hh24:mi:ss') -to_date('-04-03 11:30:11','yyyy-MM-dd hh24:mi:ss');select to_number(now() -to_date('-08-16 17:30:11','yyyy-MM-dd hh24:mi:ss'));
select to_number(date_trunc('hour',to_date('-04-02 11:30:10','yyyy-MM-dd hh24:mi:ss')) -date_trunc('hour',to_date('-04-02 11:30:10','yyyy-MM-dd hh24:mi:ss'))); -- 7 days 01:03:00.000000-- -7 days -01:03:00.000000
select to_number(date_trunc('second',to_date('-04-02 11:30:10','yyyy-MM-dd hh24:mi:ss')) -date_trunc('second',to_date('-04-02 11:30:10','yyyy-MM-dd hh24:mi:ss'))); -- 整数,如果关一天返回 1.0000000-- 如差1小时返回 10000.0000000
六、人大金仓写函数时遇到的坑
函数查询结果的坑
函数中如下写法
sTempChd = ‘32位UNID’tempStr = '';select perent_unid into tempStr form sys_organization WHERE uind = sTempChd;
上面代码在数据库中查找不到该记录,也就是说数据库不存在该记录的情况下:
MySql会很智能的返回 null, 最终 tempStr值为null;
人大金仓就不友好了,直接弹窗提示,查询的结果不存在,报错!
需要优化成先判断是否存在
num = 0;sTempChd = ‘32位UNID’tempStr = '';select count into num form sys_organization WHERE uind = sTempChd;if(num > 0) thenselect perent_unid into tempStr form sys_organization WHERE uind = sTempChd;elsesTempChd = null;endif