Oracle merge合并更新函数实例详解

MERGE INTO table_name alias1 
USING (table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
 UPDATE table_name 
 SET col1 = col_val1, 
   col2 = col_val2 
WHEN NOT MATCHED THEN 
 INSERT (column_list) VALUES (column_values); 

MERGE INTO t_config_related A1 USING(select '97547758-6f85-419e-85f9-b8f711ca2660' seq,
   '97547758-6f85-419e-85f9-b8f711ca2658' tipsSeq,
   to_number('1') appLevel,
   '24e1ccc38d9542189d1cc6c23f814a18' relaSeq from dual) A2
ON(A1.seq = A2.seq)
WHEN MATCHED THEN
UPDATE SET
  A1.tips_seq = A2.tipsSeq,   
  A1.app_level = A2.appLevel,
  A1.rela_seq = A2.relaSeq
WHEN NOT MATCHED THEN
INSERT(seq , tips_seq , app_level , rela_seq)
VALUES(A2.seq , A2.tipsSeq , A2.appLevel , A2.relaSeq);

/**
  * 批量更新保存地区关联信息
  * @date 2019年2月21日上午11:17:10
  * @return
  */
 
 @RequestMapping("/batchSaveTipsAreaConfig")
 @ResponseBody
 public ResultModel batchSaveTipsConfig(ApprTipsRelatedModel relatedModel) {
 
  List<ApprTipsRelatedModel> areaTipsList = new ArrayList<ApprTipsRelatedModel>();
 
  //封装批量更新的地区列表
 
  String[] areaSeqs = StringUtils.split(relatedModel.getAreaSeq(),",");
 
  for (String areaSeq : areaSeqs) {
 
   ApprTipsRelatedModel areaRelatedModelNew = new ApprTipsRelatedModel();
 
   areaRelatedModelNew.setSeq(commonService.generateUUID());
 
   areaRelatedModelNew.setTipsSeq(relatedModel.getTipsSeq());
 
   areaRelatedModelNew.setAppLevel(NumConstant.COMMON_NUM_TREE);
 
   areaRelatedModelNew.setRelaSeq(areaSeq);
 
   areaTipsList.add(areaRelatedModelNew);
 
 }
 
  //批量更新新增数据
 
  try {
 
   this.tipsConfigService.batchSaveTipsRelatedConfig(areaTipsList);
 
   return ResultModel.success("保存成功");
 
  } catch(Exception e) {
 
   logger.error("批量绑定地区异常:{}"+e);
 
   throw new SuperControllerException();
 
  }
 
 }

Preparing: MERGE INTO appr_tips_config_related A1 "htmlcode">

"color: #ff0000">总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。