DB2迁移Oracle SQL语句改动点_db2 with ur 在oracle有吗

1. 去掉数据库访问中的“with ur”和 “for read only” 关键字，Oracle不支持

--DB2
select * from userInfo with ur

--Oracle
select * from userInfo
1
2
3
4
5

2. current date改为current_date，current timestamp改为current_timestamp

--DB2	
insert into 
coreframe.userInfo (name, createorid, lastmodifyid,createTime,lastModifyTime)
values(#{name}, #{createorid}, #{lastmodifyid},current timestamp,current timestamp)

--Oracle	
insert into coreframe.userInfo (name, createorid, lastmodifyid,createTime,lastModifyTime)
values(#{name}, #{createorid}, #{lastmodifyid},current_timestamp,current_timestamp)
1
2
3
4
5
6
7
8

3. merge into 临时表写法不同
• values要改为select xx from dual，参数为list时通过union连接不同参数
• 需要用括号将ON后边的条件框住
• 表别名不能用AS修饰，否则会报错

--DB2	
Merge into coreframe.FTU_ORG_MAP AS O
using (
values(cast(#{ftuBrn} as varchar(6)),cast(#{brnNam} as varchar(60)),cast(#{orgId} as int))
)
AS T(ftu_Brn,brn_Nam,orgId) 
on O.ftu_Brn = T.ftu_Brn 
when matched then 
update set 
O.brn_Nam= T.brn_Nam,
O.orgId= T.orgId,
O.lastModifyId=#{lastModifyId},
O.lastModifyTime=current timestamp 
when not matched then 
insert (O.orgId, O.ftu_Brn, O.brn_Nam, O.createorId, O.createTime, O.lastModifyId, O.lastModifyTime) 
values (T.orgid, T.ftu_brn, T.brn_nam, #{creatorId}, current timestamp, #{lastModifyId}, current timestamp)

--Oracle	
Merge into coreframe.FTU_ORG_MAP O
using (
SELECT cast(#{ftu_Brn} as varchar(6)) ftu_Brn, cast(#{brnNam} as varchar(60)) brn_Nam, cast(#{orgId} as int) orgId FROM DUAL
) T
on 
(O.ftu_Brn = T.ftu_Brn )
when matched then 
update set 
O.brn_Nam= T.brn_Nam,
O.orgId= T.orgId,
O.lastModifyId=#{lastModifyId},
O.lastModifyTime=current_timestamp
when not matched then 
insert (O.orgId, O.ftu_Brn, O.brn_Nam, O.createorId, O.createTime, O.lastModifyId, O.lastModifyTime) 
values (T.orgid, T.ftu_brn, T.brn_nam, #{creatorId}, current_timestamp, #{lastModifyId}, current_timestamp)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

4. Rownumber() over() 改为 Row_number() over(order by xxx asc)
排序条件必须放在over()函数中

--DB2	
select * from(
SELECT ROWNUMBER() OVER() AS RN,t.* FROM(
SELECT ORGID, GROUP_ID, PATHNAME, LEAF, UNIT_TYPE, EMAIL_ID, HIERARCHY_FLAG FROM COREFRAME.ORG_GROUP_INFO ORDER BY SORT_ID,GROUP_ID
)t )

--Oracle	
select * from(
SELECT ROW_NUMBER() OVER(ORDER BY SORT_ID,GROUP_ID ) AS RN,t.* FROM(
SELECT ORGID, SORT_ID, GROUP_ID, PATHNAME, LEAF, UNIT_TYPE, HIERARCHY_FLAG FROM COREFRAME.ORG_GROUP_INFO 
)t )
1
2
3
4
5
6
7
8
9
10
11

5. 给表取别名，需要去掉as

--DB2	
select u.id, u.name from userInfo as u

--Oracle	
select u.id, u.name from userInfo u
1
2
3
4
5

6. 插入时，自增长ID需要手动赋值
oracle不支持自增长ID，需要自己创建序列，然后在插入的时候，手动给serialId赋值。

--DB2	
insert into coreframe.userLog(app, logtype) values(#{app}, #{logType})

--Oracle	
insert into coreframe.userLog(serialId, app, logtype) values(COREFRAME.S_userLog.NEXTVAL, #{app}, #{logType})
1
2
3
4
5

7. 使用mybatis时，char类型字段作为查询条件时一直都查不出数据，其他类型的则可以
由于在oracle中，char类型字段，如果内容长度不够，会自动以空格方式补足长度。如字段 name char(5)，若值为“myq”，那么oracle会自动用空格补足长度，最终值为”myq ”。
解决方法：
方法1：先用trim()函数把值去掉两边空格再作为条件查询
方法2：将字段类型char()改为varchar2()类型。一般情况下，只有所有值长度都一样时才用char()类型，比如性别字段，用0表示男和1表示女时，就可以用char(1)，如果值的长度不固定，有长有短，最好别用char()类型。
另外如有存量数据，记得在搬迁到Oracle时，将数据做trim操作。
在使用厂商工具做数据迁移时，会将char字段的空格一同写入变更后的varchar2字段，造成同样的问题，此时需要对相关表字段做统一trim()处理

8. 批量插入时，语法不同

--DB2	
<insert id="batchInsert" parameterType="java.util.List">
insert into 
coreframe.userroleorg(urid, orgid, expand_flag, orgscope_flag, hmac, createtime, createorid, lastmodifytime, lastmodifyid) 
values
<foreach collection="list" item="item" index="index" separator="," >
(#{item.urId},#{item.orgId,jdbcType=INTEGER},#{item.expand_Flag},#{item.orgScope_Flag},'',
current_timestamp, #{item.creatorId}, current_timestamp, #{item.creatorId})
</foreach>
</insert>

--Oracle	
<insert id="batchInsert" parameterType="java.util.List">
insert ALL
<foreach collection="list" item="item" index="index" separator=" ">
into coreframe.userroleorg(urid, orgid, expand_flag, orgscope_flag, hmac, createtime, createorid,
lastmodifytime, lastmodifyid)
values
(#{item.urId},#{item.orgId,jdbcType=INTEGER},#{item.expand_Flag},#{item.orgScope_Flag},'',
current_timestamp, #{item.creatorId}, current_timestamp, #{item.creatorId})
</foreach>
SELECT 1 FROM DUAL
</insert>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

9. 使用mybatis时，当插入为null的字段时，报无效列类型：1111错误
解决方法：
方法1：给每个字段加上jdbcType
方法2：统一配置，加上以下配置，因为oracle数据库，当数据为null时，默认当做JdbcType.OTHER来处理，而DB2则默认当做JdbcType.NULL来处理。

mybatis:
  configuration:
    jdbc-type-for-null: 'null'
1
2
3