oracle merge into 的用法詳解+執行個體

轉自：http://www.cnblogs.com/xy6521/articles/1310177.html

oracle merge into 的用法詳解+執行個體

作用：merge into 解決用B表跟新A表資料，如果A表中沒有，則把B表的資料插入A表； 文法： MERGE INTO [your table-name] [rename your table here] USING ( [write your query here] )[rename your query-sql and using just like a table] ON ([conditional expression here] AND [...]...) WHEN MATHED THEN [here you can execute some update sql or something else ] WHEN NOT MATHED THEN [execute something else here ! ] -------------------------------------執行個體----------------------------------------------------------------- merge into tfa_alarm_act_nms a using (select FP0,FP1,FP2,FP3,REDEFINE_SEVERITY from tfa_alarm_status) b on (a.fp0=b.fp0 and a.fp1=b.fp1 and a.fp2=b.fp2 and a.fp3=b.fp3) when matched then update set a.redefine_severity=b.redefine_severity when not matched then insert (a.fp0,a.fp1,a.fp2,a.fp3,a.org_severity,a.redefine_severity,a.event_time ,a.int_id) values (b.fp0,b.fp1,b.fp2,b.fp3,b.REDEFINE_SEVERITY,b.redefine_severity,sysdate,7777778); 作用：利用表 tfa_alarm_status跟新表tfa_alarm_act_nms 的b.redefine_severity，條件是a.fp0=b.fp0 and a.fp1=b.fp1 and a.fp2=b.fp2 and a.fp3=b.fp3，如果tfa_alarm_act_nms表中沒有該條件的資料就插入。 如果你的資料量很大，此sql效率非常高。