Oracle merge into插入和修改数据
目录
在使用oracle数据库,需要操作数据,增加和删除。使用merge into 的语法可以一个语句搞定。
语法:
MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
WHEN MATCHED THEN
[UPDATE sql]
WHEN NOT MATCHED THEN
[INSERT sql]
虚拟例子
MERGE INTO tableX x
USING (SELECT 1222 AS id, 'yan' AS name FROM dual) y
ON ( x.id=y.id)
WHEN MATCHED THEN
UPDATE SET x.name = y.name
WHEN NOT MATCHED THEN
INSERT (id,name) VALUES(y.id,y.id);
实际例子:
merge INTO report_show_cfg a
using (select 5 as cfgId,
1 as classId,
'0BF' as isPage,
10 as pageSize
from dual) b
on (a.CFG_ID = b.cfgId and a.CLASS_ID = b.classId)
when matched then
update
set a.IS_PAGE = b.isPage,
a.PAGE_SIZE = b.pageSize
-- 这边就不用写判断条件了,上面on 里面有
-- where a.CFG_ID = b.cfgId and a.CLASS_ID = b.classId
when not matched then
insert
(a.CFG_ID,
a.CLASS_ID,
a.IS_PAGE,
a.PAGE_SIZET)
values
(REPORT_SHOW_CFG_SEQ.nextval,
b.classId,
b.isPage,
b.pageSize)
了解了语法,操作起来比较简单,那对应mybatis里面的xml怎么写呢?
Mybatis:
单个插入:
Mapper:
void mergeReportShowCfg(@Param("param") ReportShowCfg reportShowCfg);
显象声明参数,养成好习惯
Xml:
<update id="mergeReportShowCfg" parameterType=" report.entity.ReportShowCfg" databaseId="oracle">
merge into sys_report_show_cfg a using (
SELECT #{param.cfgId,jdbcType=BIGINT} as cfgId, #{param.classId,jdbcType=BIGINT} as classId,
#{param.isPage,jdbcType=VARCHAR} as isPage, #{param.pageSize,jdbcType=INTEGER} as pageSize FROM dual
) b on (
a.CFG_ID= b.cfgId
AND a.CLASS_ID= b.classId
)
when matched then
UPDATE SET
a.IS_PAGE = b.isPage,
a.PAGE_SIZE = b.pageSize
# 这边就不用写判断条件了,上面on 里面有
# where a.CFG_ID= b.cfgId AND a.CLASS_ID= b.classId
when not matched then
INSERT (a.CFG_ID, a.CLASS_ID, a.IS_PAGE, a.PAGE_SIZE)
values (
SYS_REPORT_SHOW_CFG_SEQ.nextval,b.classId,b.isPage,b.pageSize )
</update>
#{param.cfgId,jdbcType=BIGINT} 声明参数的数据类型
databaseId="oracle" 指定使用什么数据库。 在多数据库中会用到,比如可能用mysql数据,pg数据库等
多个插入:
Mapper:
void mergeReportParamBach(@Param("reportParamList") List<ReportParam> reportParamList);
Xml:
<update id="mergeSysReportParamBach" databaseId="oracle">
merge into report_param a using (
<foreach collection="reportParamList" index="index" item="item" open=""
close="" separator="union all">
SELECT #{item.paramId,jdbcType=BIGINT} as paramId, #{item.paramName,jdbcType=VARCHAR} as paramName,
#{item.sortId,jdbcType=INTEGER} as sortId, #{item.classId,jdbcType=BIGINT} as classId FROM dual
</foreach>
) b on (
a.PARAM_ID= b.paramId
AND a.CLASS_ID= b.classId
)
when matched then
UPDATE SET
a.PARAM_NAME = b.paramName,
a.SORT_ID = b.sortId
# 这边就不用写判断条件了,上面on 里面有
# where a.PARAM_ID= b.paramId AND .CLASS_ID= b.classId
when not matched then
INSERT (a.PARAM_ID, a.PARAM_NAME,a.SORT_ID, a.CLASS_ID)
values (
SYS_REPORT_PARAM_SEQ.nextval,b.paramName,b.sortId,b.classId
)
</update>
总结:
使用oracle数据库,merge into 可以一起处理增加修改的操作。
在mybatis注意参数的显象声明和声明数据类型。
Xml里面中指定databaseId 可以根据实际情况,动态读取数据库。 具体情况参考 数据操作,使用不同数据库处理方式