merge into 語(yǔ)句是insert 與update語(yǔ)句的結(jié)合需频，可以同時(shí)實(shí)現(xiàn)update和insert的功能漓帅。
一眷茁、merge into語(yǔ)句的語(yǔ)法炕泳。
? ?? 1）、 MERGE INTO schema. table alias
????????????????USING { schema. table | views | query} alias ? ON {(condition) } ? ?
????????????????WHEN MATCHED THEN ? UPDATE SET {clause}?
????????????????WHEN NOT MATCHED THEN ? ? INSERT VALUES {clause};
? ? 2）上祈、--解析
????????????????INTO 子句------>用于指定你所update或者Insert目的表培遵。
?????????????? USING 子句------->用于指定你要update或者Insert的記錄的來(lái)源浙芙，它可能是一個(gè)表，視圖籽腕，子查詢嗡呼。
????????????? ON? ----->用于目的表和源表（視圖，子查詢）的關(guān)聯(lián)皇耗，如果匹配（或存在）南窗，則更新，否則插入郎楼。
?????????????? update? set? ------>用于寫update語(yǔ)句
?????????????? insert? ----->用于插入目標(biāo)表沒有的數(shù)據(jù)
二万伤、merge 語(yǔ)句的各種用法練習(xí)
? ? ? ?? 1）、創(chuàng)建表aa
????????????????create table t_B_info_aa
????????????????????????????????????????( id varchar2(20),
??????????????????????????????????????????name varchar2(50),
????????????????????????????????????????? type varchar2(30),
????????????????????????????????????????? price number
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? );
? ? ?? 2）箭启、向a表插入記錄
????????????insert into t_B_info_aa values('01','冰箱','家電',2000);
????????????insert into t_B_info_aa values('02','洗衣機(jī)','家電',1500);
????????????insert into t_B_info_aa values('03','熱水器','家電',1000);
????????????insert into t_B_info_aa values('04','凈水機(jī)','家電',1450);
????????????insert into t_B_info_aa values('05','燃?xì)庠?,'家電',800);
????????????insert into t_B_info_aa values('06','太陽(yáng)能','家電',1200);
????????????insert into t_B_info_aa values('07','西紅柿','食品',1.5);
????????????insert into t_B_info_aa values('08','黃瓜','食品',3);
????????????insert into t_B_info_aa values('09','菠菜','食品',4);
? ? ? ? ? ? insert into t_B_info_aa values('10','香菇','食品',9);
????????????insert into t_B_info_aa values('11','韭菜','食品',2);
????????????insert into t_B_info_aa values('12','白菜','食品',1.2);
????????????insert into t_B_info_aa values('13','芹菜','食品',2.1);
????3）壕翩、創(chuàng)建表b
????????????create table t_B_info_bb
????????????????????????????????????????????????( id varchar2(20),
????????????????????????????????????????????????? type varchar2(50),
????????????????????????????????????????????????? price number
????4）、 向bb插入數(shù)據(jù) ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? );
????????????insert into t_B_info_bb values('01','家電',2000);
????????????insert into t_B_info_bb values('02','家電',1000);
?? 5) 傅寡、update和insert同時(shí)使用
????????????merge into t_B_info_bb b
????????????using t_B_info_aa a --如果是子查詢要用括號(hào)括起來(lái)
????????????on (a.id = b.id and a.type = b.type) --關(guān)聯(lián)條件要用括號(hào)括起來(lái)
????????????when matched then
? ? ? ? ?? update? set b.price = a.price --update 后面直接跟set語(yǔ)句
? ? ? ? ?? when not matched then
? ? ? ? ? insert (id, type, price) values (a.id, a.type, a.price) --insert 后面不加into
---這條語(yǔ)句根據(jù)t_B_info_aa 更新了t_B_info_bb中的一條記錄放妈，插入了11條記錄
?? 6)、只插入不更新
????????--處理表中數(shù)據(jù)荐操，使表中的一條數(shù)據(jù)發(fā)生變化芜抒，刪掉一部分?jǐn)?shù)據(jù)。用來(lái)驗(yàn)證只插入不更新的功能
????????update t_B_info_bb b set b.price=1000 where b.id='02';
? ? ? ? delete from t_B_info_bb b where b.type='食品';
????????--只是去掉了when matched then update語(yǔ)句
????????merge into t_B_info_bb b
????????using t_B_info_aa a
????????on (a.id = b.id and a.type = b.type)
????????when not matched then
????????? insert (id, type, price) values (a.id, a.type, a.price)
? 7)托启、只更新不插入
????????--處理表中數(shù)據(jù)宅倒，刪掉一部分?jǐn)?shù)據(jù)，用來(lái)驗(yàn)證只更新不插入
????????delete from t_B_info_bb b where b.type='食品';
????????--只更新不插入屯耸，只是去掉了when not matched then insert語(yǔ)句
????????merge into t_B_info_bb b
????????using t_B_info_aa a
????????on (a.id = b.id and a.type = b.type)
????????when matched then
?????????? update set b.price = a.price
三拐迁、加入限制條件的操作
?????????? update t_B_info_bb b
???????????set b.price = 1000
???????????where b.id='02'
?????????? delete from t_b_info_bb b where b.id not in ('01','02') and b.type='家電'
?????????? update t_B_info_bb b
???????????set b.price = 8
?????????? where b.id='10'
???????????delete from t_b_info_bb b where b.id? in ('11','12','13') and b.type='食品'
表中數(shù)據(jù)
執(zhí)行merge語(yǔ)句：腳本一和腳本二執(zhí)行結(jié)果相同
腳本一： merge into t_B_info_bb b
???????? using t_B_info_aa a
???????? on (a.id = b.id and a.type = b.type)
???????? when matched then
? ? ? update set b.price = a.price where a.type = '家電'
???????? when not matched then
?????????? insert
? ? ? ? (id, type, price)
? ? ? values
? ? ? ? (a.id, a.type, a.price) where a.type = '家電';
? 腳本二：? merge into t_B_info_bb b
? ? using (select a.id, a.type, a.price
? ? ? ? ? ? ? from t_B_info_aa a
? ? ? ? ? ?? where a.type = '家電') a
???????????? on (a.id = b.id and a.type = b.type)
???????????? when matched then
???????????update set b.price = a.price
? ? ? ? ?? when not matched then
?????????? insert (id, type, price) values (a.id, a.type, a.price);
結(jié)果：
上面兩個(gè)語(yǔ)句是只對(duì)類型是家電的語(yǔ)句進(jìn)行插入和更新
而腳本三是只對(duì)家電進(jìn)行更新，其余的全部進(jìn)行插入
? ? merge into t_B_info_bb b
? ? using t_B_info_aa a
? ? on (a.id = b.id and a.type = b.type)
? ? when matched then
? ? ? update set b.price = a.price
? ? ? ? where a.type = '家電'
? ? when not matched then
? ? ? insert
? ? ? ? (id, type, price)
? ? ? values
? ? ? ? (a.id, a.type, a.price);
四疗绣、加刪除操作
update子句后面可以跟delete子句來(lái)去掉一些不需要的行
delete只能和update配合线召，從而達(dá)到刪除滿足where條件的子句的記錄
例句：
? ? merge into t_B_info_bb b
? ? using t_B_info_aa a
? ? on (a.id=b.id and a.type = b.type)
? ? when matched then
?? update set b.price=a.price
?? delete where (a.type='食品')
? ? when not matched then
?? insert values (a.id, a.type, a.price) where a.id = '10'
