/*Merge into 详细介绍
MERGE语句用来合并UPDATE和INSERT语句。
通过MERGE语句,根据一张表或子查询的连接条件对另外一张表进行查询,
连接条件匹配上的进行UPDATE,无法匹配的执行INSERT。
这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERT+UPDATE。
*/
/*語法:
MERGE [INTO [schema .] table [t_alias]
USING [schema .] { table | view | subquery } [t_alias]
ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;*/
具体例子如下:
create or replace procedure merge_pos_sale_details is
begin
merge into pos_sale_details P
using (select *
from pos_sale_details_temp
where nvl(SAD_LCHG_DATE, SAD_RCRE_DATE) between sysdate - 2 and
sysdate
and SAD_MEGRE_FLG = 'N'
order by SAD_TIMESTAMP) T
on (P.SAD_SALEFORM_ID = T.SAD_SALEFORM_ID and P.SAD_GOOD_SEQ = T.SAD_GOOD_SEQ and P.SAD_LOC_CD = T.SAD_LOC_CD)
when matched then
update
set P.SAD_GOOD_ID = T.SAD_GOOD_ID,
P.SAD_COUNT = T.SAD_COUNT,
P.SAD_PRICE = T.SAD_PRICE,
P.SAD_TOTAL_AMT = T.SAD_TOTAL_AMT,
P.SAD_RCRE_USER_ID = T.SAD_RCRE_USER_ID,
P.SAD_RCRE_DATE = T.SAD_RCRE_DATE,
P.SAD_LCHG_USER_ID = T.SAD_LCHG_USER_ID,
P.SAD_LCHG_DATE = T.SAD_LCHG_DATE,
P.SAD_GOOD_COST = T.SAD_GOOD_COST,
P.SAD_TAX = T.SAD_TAX,
P.SAD_DISCOUNT = T.SAD_DISCOUNT,
P.SAD_INTEGRALYN = T.SAD_INTEGRALYN,
P.SAD_GC_ID = T.SAD_GC_ID,
P.SAD_INTEGRAL = T.SAD_INTEGRAL,
P.SAD_G_DISCOUNT = T.SAD_G_DISCOUNT,
P.SAD_G_DEDUCTION = T.SAD_G_DEDUCTION,
P.DISCOUNT_LIST = T.DISCOUNT_LIST,
P.SAD_TYPE_ID = T.SAD_TYPE_ID,
P.SAD_FLEXIBLE_PAIR = T.SAD_FLEXIBLE_PAIR
when not matched then
insert
(P.SAD_SALEFORM_ID,
P.SAD_GOOD_ID,
P.SAD_COUNT,
P.SAD_PRICE,
P.SAD_TOTAL_AMT,
P.SAD_RCRE_USER_ID,
P.SAD_RCRE_DATE,
P.SAD_LCHG_USER_ID,
P.SAD_LCHG_DATE,
P.SAD_GOOD_COST,
P.SAD_TAX,
P.SAD_DISCOUNT,
P.SAD_LOC_CD,
P.SAD_GOOD_SEQ,
P.SAD_INTEGRALYN,
P.SAD_GC_ID,
P.SAD_INTEGRAL,
P.SAD_G_DISCOUNT,
P.SAD_G_DEDUCTION,
P.DISCOUNT_LIST,
P.SAD_TYPE_ID,
P.SAD_FLEXIBLE_PAIR)
values
(T.SAD_SALEFORM_ID,
T.SAD_GOOD_ID,
T.SAD_COUNT,
T.SAD_PRICE,
T.SAD_TOTAL_AMT,
T.SAD_RCRE_USER_ID,
T.SAD_RCRE_DATE,
T.SAD_LCHG_USER_ID,
T.SAD_LCHG_DATE,
T.SAD_GOOD_COST,
T.SAD_TAX,
T.SAD_DISCOUNT,
T.SAD_LOC_CD,
T.SAD_GOOD_SEQ,
T.SAD_INTEGRALYN,
T.SAD_GC_ID,
T.SAD_INTEGRAL,
T.SAD_G_DISCOUNT,
T.SAD_G_DEDUCTION,
T.DISCOUNT_LIST,
T.SAD_TYPE_ID,
T.SAD_FLEXIBLE_PAIR);
update pos_sale_details_temp
set SAD_MEGRE_FLG = 'Y', sad_megre_time=sysdate
where nvl(SAD_LCHG_DATE, SAD_RCRE_DATE) between sysdate - 2 and sysdate;
end merge_pos_sale_details;