oracle数据库merge语句的用法小结

在oracle数据库中通过merge可以在一个SQL语句中对一个表同时执行insert和update操作,当然是insert还是update是根据你的指定的条件判断的,merge into可以实现用B表来更新A表数据,如果A表中没有数据,则把B表的数据插入A表。有关merge into的基本语法就不列举了,直接看下面的例子:

MERGE INTO products p
USING newproducts np
ON (p.product_id = np.product_id)
WHEN MATCHED THEN
  UPDATE SET p.product_name = np.product_name
WHEN NOT MATCHED THEN
  INSERT VALUES (np.product_id, np.product_name, np.category);

在这个例子里,前面的merge into products using newproducts表示的是用newproducts表来merge到products表,merge的匹配关系就是on后面的条件子句的内容,这里根据两个表的product_id来进行匹配,那么匹配上了我们的操作就是when matched then的子句的操作,这里的操作是update set p.product_name = np.product_name,很显然就是把newproducts里的内容更新到products的product_name里去;如果没有匹配上则insert插入一条语句。批量更新数据还可以参考下博客中《oracle数据库用代码实现批量更新(update)、批量插入(insert)、批量删除(delete)》这篇文章。

下面是oracle retail系统中一段标准的代码,其merge用的比较漂亮,收藏:

merge into svc_item_master sim
        using (select LP_process_id as process_id,
                      LP_chunk_id as chunk_id,
                      NVL(max(rownum+1), 1) as rowseq,
                      I_action as action,
                      LP_process_status as process_status,
                      I_item_rec.item as item,
                      I_item_rec.item_number_type as item_number_type,
                      I_item_rec.format_id as format_id,
                      I_item_rec.prefix as prefix,
                      I_item_rec.item_parent as item_parent,
                      I_item_rec.item_grandparent as item_grandparent,
                      I_item_rec.pack_ind as pack_ind,
                      I_item_rec.item_level as item_level,
                      I_item_rec.tran_level as tran_level,
                      I_item_rec.item_aggregate_ind as item_aggregate_ind,
                      I_item_rec.diff_1 as diff_1,
                      I_item_rec.diff_1_aggregate_ind as diff_1_aggregate_ind,
                      I_item_rec.diff_2 as diff_2,
                      I_item_rec.diff_2_aggregate_ind as diff_2_aggregate_ind,
                      I_item_rec.diff_3 as diff_3,
                      I_item_rec.diff_3_aggregate_ind as diff_3_aggregate_ind,
                      I_item_rec.diff_4 as diff_4,
                      I_item_rec.diff_4_aggregate_ind as diff_4_aggregate_ind,
                      I_item_rec.dept as dept,
                      I_item_rec.CLASS as CLASS,
                      I_item_rec.subclass as subclass,
                      NVL(I_item_rec.status,'W') as status,
                      I_item_rec.item_desc as item_desc,
                      I_item_rec.item_desc_secondary as item_desc_secondary,
                      I_item_rec.short_desc as short_desc,
                      I_item_rec.desc_up as desc_up,
                      I_item_rec.primary_ref_item_ind as primary_ref_item_ind,
                      I_item_rec.cost_zone_group_id as cost_zone_group_id,
                      I_item_rec.standard_uom as standard_uom,
                      I_item_rec.uom_conv_factor as uom_conv_factor,
                      I_item_rec.package_size as package_size,
                      I_item_rec.package_uom as package_uom,
                      I_item_rec.merchandise_ind as merchandise_ind,
                      I_item_rec.store_ord_mult as store_ord_mult,
                      I_item_rec.forecast_ind as forecast_ind,
                      I_item_rec.original_retail as original_retail,
                      I_item_rec.mfg_rec_retail as mfg_rec_retail,
                      I_item_rec.retail_label_type as retail_label_type,
                      I_item_rec.retail_label_value as retail_label_value,
                      I_item_rec.handling_temp as handling_temp,
                      I_item_rec.handling_sensitivity as handling_sensitivity,
                      I_item_rec.catch_weight_ind as catch_weight_ind,
                      I_item_rec.waste_type as waste_type,
                      I_item_rec.waste_pct as waste_pct,
                      I_item_rec.default_waste_pct as default_waste_pct,
                      I_item_rec.const_dimen_ind as const_dimen_ind,
                      I_item_rec.simple_pack_ind as simple_pack_ind,
                      I_item_rec.contains_inner_ind as contains_inner_ind,
                      I_item_rec.sellable_ind as sellable_ind,
                      I_item_rec.orderable_ind as orderable_ind,
                      I_item_rec.pack_type as pack_type,
                      I_item_rec.order_as_type as order_as_type,
                      I_item_rec.comments as comments,
                      I_item_rec.item_service_level as item_service_level,
                      I_item_rec.gift_wrap_ind as gift_wrap_ind,
                      I_item_rec.ship_alone_ind as ship_alone_ind,
                      I_item_rec.check_uda_ind as check_uda_ind,
                      I_item_rec.item_xform_ind as item_xform_ind,
                      I_item_rec.inventory_ind as inventory_ind,
                      I_item_rec.order_type as order_type,
                      I_item_rec.sale_type as sale_type,
                      I_item_rec.deposit_item_type as deposit_item_type,
                      I_item_rec.container_item as container_item,
                      I_item_rec.deposit_in_price_per_uom as deposit_in_price_per_uom,
                      I_item_rec.aip_case_type as aip_case_type,
                      I_item_rec.catch_weight_type as catch_weight_type,
                      I_item_rec.perishable_ind as perishable_ind,
                      NVL(I_item_rec.notional_pack_ind, 'N') as notional_pack_ind,
                      NVL(I_item_rec.soh_inquiry_at_pack_ind, 'N') as soh_inquiry_at_pack_ind,
                      I_item_rec.catch_weight_uom as catch_weight_uom,
                      I_item_rec.product_classification as product_classification,
                      I_item_rec.brand_name as brand_name,
                      LP_user as create_id,
                      I_item_rec.create_datetime as create_datetime,
                      I_item_rec.last_update_id as last_update_id,
                      NULL as next_upd_id,
                      SYSDATE as last_upd_datetime,
                      NULL as orig_ref_no
                 from svc_item_master
                where process_id = LP_process_id
                  and chunk_id = LP_chunk_id) temp
             on (sim.item = temp.item and temp.action = CORESVC_ITEM.ACTION_MOD)
           when matched then
         update
            set process_id = temp.process_id,
                chunk_id = temp.chunk_id,
                row_seq = temp.rowseq,
                action = temp.action,
                process$status = temp.process_status,
                item_number_type = temp.item_number_type,
                format_id = temp.format_id,
                prefix = temp.prefix,
                item_parent = temp.item_parent,
                item_grandparent = temp.item_grandparent,
                pack_ind = temp.pack_ind,
                item_level = temp.item_level,
                tran_level = temp.tran_level,
                item_aggregate_ind = temp.item_aggregate_ind,
                diff_1 = temp.diff_1,
                diff_1_aggregate_ind = temp.diff_1_aggregate_ind,
                diff_2 = temp.diff_2,
                diff_2_aggregate_ind = temp.diff_2_aggregate_ind,
                diff_3 = temp.diff_3,
                diff_3_aggregate_ind = temp.diff_3_aggregate_ind,
                diff_4 = temp.diff_4,
                diff_4_aggregate_ind = temp.diff_4_aggregate_ind,
                dept = temp.dept,
                class = temp.class,
                subclass = temp.subclass,
                status = temp.status,
                item_desc = temp.item_desc,
                item_desc_secondary = temp.item_desc_secondary,
                short_desc = temp.short_desc,
                desc_up = temp.desc_up,
                primary_ref_item_ind = temp.primary_ref_item_ind,
                cost_zone_group_id = temp.cost_zone_group_id,
                standard_uom = temp.standard_uom,
                uom_conv_factor = temp.uom_conv_factor,
                package_size = temp.package_size,
                package_uom = temp.package_uom,
                merchandise_ind = temp.merchandise_ind,
                store_ord_mult = temp.store_ord_mult,
                forecast_ind = temp.forecast_ind,
                original_retail = temp.original_retail,
                mfg_rec_retail = temp.mfg_rec_retail,
                retail_label_type = temp.retail_label_type,
                retail_label_value = temp.retail_label_value,
                handling_temp = temp.handling_temp,
                handling_sensitivity = temp.handling_sensitivity,
                catch_weight_ind = temp.catch_weight_ind,
                waste_type = temp.waste_type,
                waste_pct = temp.waste_pct,
                default_waste_pct = temp.default_waste_pct,
                const_dimen_ind = temp.const_dimen_ind,
                simple_pack_ind = temp.simple_pack_ind,
                contains_inner_ind = temp.contains_inner_ind,
                sellable_ind = temp.sellable_ind,
                orderable_ind = temp.orderable_ind,
                pack_type = temp.pack_type,
                order_as_type = temp.order_as_type,
                comments = temp.comments,
                item_service_level = temp.item_service_level,
                gift_wrap_ind = temp.gift_wrap_ind,
                ship_alone_ind = temp.ship_alone_ind,
                check_uda_ind = temp.check_uda_ind,
                item_xform_ind = temp.item_xform_ind,
                inventory_ind = temp.inventory_ind,
                order_type = temp.order_type,
                sale_type = temp.sale_type,
                deposit_item_type = temp.deposit_item_type,
                container_item = temp.container_item,
                deposit_in_price_per_uom = temp.deposit_in_price_per_uom,
                aip_case_type = temp.aip_case_type,
                catch_weight_type = temp.catch_weight_type,
                perishable_ind = temp.perishable_ind,
                notional_pack_ind = temp.notional_pack_ind,
                soh_inquiry_at_pack_ind = temp.soh_inquiry_at_pack_ind,
                catch_weight_uom = temp.catch_weight_uom,
                product_classification = temp.product_classification,
                brand_name = temp.brand_name,
                last_upd_id = temp.last_update_id,
                next_upd_id = temp.next_upd_id,
                last_upd_datetime = temp.last_upd_datetime,
                orig_ref_no = orig_ref_no
           when not matched then
         insert (process_id,
                 chunk_id,
                 row_seq,
                 action,
                 process$status,
                 item,
                 item_number_type,
                 format_id,
                 prefix,
                 item_parent,
                 item_grandparent,
                 pack_ind,
                 item_level,
                 tran_level,
                 item_aggregate_ind,
                 diff_1,
                 diff_1_aggregate_ind,
                 diff_2,
                 diff_2_aggregate_ind,
                 diff_3,
                 diff_3_aggregate_ind,
                 diff_4,
                 diff_4_aggregate_ind,
                 dept,
                 class,
                 subclass,
                 status,
                 item_desc,
                 item_desc_secondary,
                 short_desc,
                 desc_up,
                 primary_ref_item_ind,
                 cost_zone_group_id,
                 standard_uom,
                 uom_conv_factor,
                 package_size,
                 package_uom,
                 merchandise_ind,
                 store_ord_mult,
                 forecast_ind,
                 original_retail,
                 mfg_rec_retail,
                 retail_label_type,
                 retail_label_value,
                 handling_temp,
                 handling_sensitivity,
                 catch_weight_ind,
                 waste_type,
                 waste_pct,
                 default_waste_pct,
                 const_dimen_ind,
                 simple_pack_ind,
                 contains_inner_ind,
                 sellable_ind,
                 orderable_ind,
                 pack_type,
                 order_as_type,
                 comments,
                 item_service_level,
                 gift_wrap_ind,
                 ship_alone_ind,
                 check_uda_ind,
                 item_xform_ind,
                 inventory_ind,
                 order_type,
                 sale_type,
                 deposit_item_type,
                 container_item,
                 deposit_in_price_per_uom,
                 aip_case_type,
                 catch_weight_type,
                 perishable_ind,
                 notional_pack_ind,
                 soh_inquiry_at_pack_ind,
                 catch_weight_uom,
                 product_classification,
                 brand_name,
                 create_id,
                 create_datetime,
                 last_upd_id,
                 next_upd_id,
                 last_upd_datetime,
                 orig_ref_no)
          values(temp.process_id,
                 temp.chunk_id,
                 temp.rowseq,
                 temp.action,
                 temp.process_status,
                 temp.item,
                 temp.item_number_type,
                 temp.format_id,
                 temp.prefix,
                 temp.item_parent,
                 temp.item_grandparent,
                 temp.pack_ind,
                 temp.item_level,
                 temp.tran_level,
                 temp.item_aggregate_ind,
                 temp.diff_1,
                 temp.diff_1_aggregate_ind,
                 temp.diff_2,
                 temp.diff_2_aggregate_ind,
                 temp.diff_3,
                 temp.diff_3_aggregate_ind,
                 temp.diff_4,
                 temp.diff_4_aggregate_ind,
                 temp.dept,
                 temp.CLASS,
                 temp.subclass,
                 NVL(temp.status,'W'),
                 temp.item_desc,
                 temp.item_desc_secondary,
                 temp.short_desc,
                 temp.desc_up,
                 temp.primary_ref_item_ind,
                 temp.cost_zone_group_id,
                 temp.standard_uom,
                 temp.uom_conv_factor,
                 temp.package_size,
                 temp.package_uom,
                 temp.merchandise_ind,
                 temp.store_ord_mult,
                 temp.forecast_ind,
                 temp.original_retail,
                 temp.mfg_rec_retail,
                 temp.retail_label_type,
                 temp.retail_label_value,
                 temp.handling_temp,
                 temp.handling_sensitivity,
                 temp.catch_weight_ind,
                 temp.waste_type,
                 temp.waste_pct,
                 temp.default_waste_pct,
                 temp.const_dimen_ind,
                 temp.simple_pack_ind,
                 temp.contains_inner_ind,
                 temp.sellable_ind,
                 temp.orderable_ind,
                 temp.pack_type,
                 temp.order_as_type,
                 temp.comments,
                 temp.item_service_level,
                 temp.gift_wrap_ind,
                 temp.ship_alone_ind,
                 temp.check_uda_ind,
                 temp.item_xform_ind,
                 temp.inventory_ind,
                 temp.order_type,
                 temp.sale_type,
                 temp.deposit_item_type,
                 temp.container_item,
                 temp.deposit_in_price_per_uom,
                 temp.aip_case_type,
                 temp.catch_weight_type,
                 temp.perishable_ind,
                 temp.notional_pack_ind,
                 temp.soh_inquiry_at_pack_ind,
                 temp.catch_weight_uom,
                 temp.product_classification,
                 temp.brand_name,
                 temp.create_id,
                 temp.create_datetime,
                 temp.last_update_id,
                 temp.next_upd_id,
                 temp.last_upd_datetime,
                 temp.orig_ref_no);

本文标题:oracle数据库merge语句的用法小结

本文链接:http://yedward.net/?id=126

本文版权归作者所有,欢迎转载,转载请以文字链接的形式注明文章出处。

相关文章