Shulou(Shulou.com)06/01 Report--

Preface

The MERGE statement is a new syntax for Oracle9i to merge UPDATE and INSERT statements. Through the MERGE statement, query the other table according to the join conditions of one table or the joint query of multiple tables, UPDATE on the matching of join conditions, and execute INSERT that cannot be matched. This syntax takes only one full table scan to do all the work, and is more efficient than INSERT+UPDATE. With this MERGE you can perform both INSERT and UPDATE operations on a table in a SQL statement.

This blog introduces the Oracle merge merge function. Business scenarios: when adding new data, you should first query whether the database has changed the data, update the data if there is data, and add data only if there is no data. This is a very common business scenario. If you use Oracle database, it is actually faster to use merge function directly, and the performance of merge function is relatively better.

The syntax of the merge function:

MERGE INTO table_name alias1 USING (table | view | sub_query) alias2ON (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)

For example:

SQL implementation, which means that if there is data, it will be updated, and if there is no data, it will be added.

MERGE INTO t_config_related A1 USING (select '97547758-6f85-419eMurb8f711ca2660' seq,' 97547758-6f85-419eMurb8f711ca2660' tipsSeq, to_number ('1') appLevel, '24e1ccc38d9542189d1cc6c23f814a18' relaSeq from dual) A2ON (A1.seq = A2.seq) WHEN MATCHED THENUPDATE SET A1.tips_seq = A2.tipsSeq, A1.app_level = A2.appLevel, A1.rela_seq = A2.relaSeqWHEN NOT MATCHED THENINSERT (seq, tips_seq, app_level, rela_seq) VALUES (A2.seq A2.tipsSeq, A2.appLevel, A2.relaSeq) / * * bulk updates save locale related information * @ date 11:17:10 on February 21, 2019 * @ return * / @ RequestMapping ("/ batchSaveTipsAreaConfig") @ ResponseBody public ResultModel batchSaveTipsConfig (ApprTipsRelatedModel relatedModel) {List areaTipsList = new ArrayList (); / / encapsulates the list of regions [] 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);} / / batch update new data try {this.tipsConfigService.batchSaveTipsRelatedConfig (areaTipsList); return ResultModel.success ("saved successfully") } catch (Exception e) {logger.error ("batch bind region exception: {}" + e); throw new SuperControllerException ();}}

Key points to be noted:

I encountered an exception during development. The SQL printed by mybatis looks like this:

Preparing: MERGE INTO appr_tips_config_related A1? USING (? Select? Seq,? TipsSeq,? AppLevel,? RelaSeq from dual?) A2? ON (? A1.seq = A2.seq?) WHEN MATCHED THEN?? UPDATE SET A1.seq = A2.seq, A1.tips_seq = A2.tipsSeq, A1.app_level = A2.appLevel, A1.rela_seq = A2.relaSeq? WHEN NOT MATCHED THEN? INSERT (seq,tip_seq,app_level,rela_seq)? VALUES (A2.seq, A2.tipsSeq, A2.appLevel, A2.relaSeq)

What can you see? There are a lot of question marks in the printed SQL, which is caused by spaces in SQL, so you don't need to add spaces casually when using the merge function, or you will report an error.

? USING (? Select? Seq,? TipsSeq,? AppLevel,? RelaSeq from dual?) A2? ON (? A1.seq = A2.seq?)

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.