最近使用 merge into 陳述句時發現，update，delete之間存在一些微妙的關系，特將結論和測驗程序列舉如下：
測驗環境：oracle11g
結論：在merge into中，當update子句中存在where clause時，只有滿足on和update中的where雙重條件時，才會執行delete 子句。
--創建測驗表，并插入資料
CREATE TABLE t1 (
"ID" NUMBER(10) NOT NULL ,
"NAME" VARCHAR2(255 BYTE) NULL ,
"AGE" NUMBER(10) NULL ,
"SE" VARCHAR2(255 BYTE) NULL
)
;
INSERT INTO t1 VALUES ('1201', 'Peter', '23', 'male');
INSERT INTO t1 VALUES ('6695', 'Lu', '12', 'female');
INSERT INTO t1 VALUES ('6720', 'Ka', '22', 'female');
CREATE TABLE t2 (
"ID" NUMBER NULL ,
"NAME" VARCHAR2(255 BYTE) NULL ,
"AGE" NUMBER NULL ,
"SE" VARCHAR2(255 BYTE) NULL
)
;
INSERT INTO t2 VALUES ('1201', 'John', '47', 'male');
INSERT INTO t2 VALUES ('1202', 'lili', '30', 'female');
--結果0
SQL> select * from t1;
ID NAME AGE SE
---------- -------------------- ---------- ---------
1201 Peter 23 male
6695 Lu 12 female
6720 Ka 22 female
SQL> select * from t2;
ID NAME AGE SE
---------- -------------------- ---------- --------------------
1201 John 47 male
1202 lili 30 female
--測驗1
MERGE INTO t1 b
USING t2 e
ON (b.ID = e.ID)
WHEN MATCHED THEN
update
set b.NAME = E.NAME,
b.AGE = E.AGE,
b.SE = E.SE
where t1.name<>'Peter'
delete where (t1.name = 'Peter')
WHEN NOT MATCHED THEN
insert (b.ID,b.NAME,b.AGE,b.SE)
values(E.ID,E.NAME,E.AGE,E.SE) ;
--結果1
--創建測驗表，并插入資料
CREATE TABLE t1 (
"ID" NUMBER(10) NOT NULL ,
"NAME" VARCHAR2(255 BYTE) NULL ,
"AGE" NUMBER(10) NULL ,
"SE" VARCHAR2(255 BYTE) NULL
)
;
INSERT INTO t1 VALUES ('1201', 'Peter', '23', 'male');
INSERT INTO t1 VALUES ('6695', 'Lu', '12', 'female');
INSERT INTO t1 VALUES ('6720', 'Ka', '22', 'female');
CREATE TABLE t2 (
"ID" NUMBER NULL ,
"NAME" VARCHAR2(255 BYTE) NULL ,
"AGE" NUMBER NULL ,
"SE" VARCHAR2(255 BYTE) NULL
)
;
INSERT INTO t2 VALUES ('1201', 'John', '47', 'male');
INSERT INTO t2 VALUES ('1202', 'lili', '30', 'female');
--結果
SQL> select * from t1;
ID NAME AGE SE
---------- -------------------- ---------- ---------
1201 Peter 23 male
6695 Lu 12 female
6720 Ka 22 female
SQL> select * from t2;
ID NAME AGE SE
---------- -------------------- ---------- --------------------
1201 John 47 male
1202 lili 30 female
--測驗1
MERGE INTO t1 b
USING t2 e
ON (b.ID = e.ID)
WHEN MATCHED THEN
update
set b.NAME = E.NAME,
b.AGE = E.AGE,
b.SE = E.SE
where t1.name<>'Peter'
delete where (t1.name = 'Peter')
WHEN NOT MATCHED THEN
insert (b.ID,b.NAME,b.AGE,b.SE)
values(E.ID,E.NAME,E.AGE,E.SE) ;
--結果1
SQL> select * from t1;
ID NAME AGE SE
---------- -------------------- ---------- --------------------
1201 Peter 23 male
6695 Lu 12 female
6720 Ka 22 female
1202 lili 30 female
可見，通過上述代碼，只是將 t2 中 id 為 1202 的記錄insert到了t1中，并沒有將t1中name為Peter的記錄洗掉，即delete陳述句沒有執行。
rollback 回退結果。
現在表t1中內容為：
SQL> select * from t1;
ID NAME AGE SE
---------- -------------------- ---------- --------------------
1201 Peter 23 male
6695 Lu 12 female
6720 Ka 22 female
--測驗2：
MERGE INTO t1 b
USING t2 e
ON (b.ID = e.ID)
WHEN MATCHED THEN
update
set b.NAME = E.NAME,
b.AGE = E.AGE,
b.SE = E.SE
where t1.name='Peter'
delete where (t1.name = 'Peter')
WHEN NOT MATCHED THEN
insert (b.ID,b.NAME,b.AGE,b.SE)
values(E.ID,E.NAME,E.AGE,E.SE) ;
--結果2：
SQL> select * from t1;
ID NAME AGE SE
---------- -------------------- ---------- --------------------
1201 John 47 male
6695 Lu 12 female
6720 Ka 22 female
1202 lili 30 female
因為先執行了update，將t1中id為1201中的name修改為了John，所以不滿足delete條件，delete不執行。從該測驗中可以知道，當滿足on條件時，先執行update后執行delete。
同樣rollback，
SQL> select * from t1;
ID NAME AGE SE
---------- -------------------- ---------- -------------------
6695 Lu 12 female
6720 Ka 22 female
1201 Peter 23 male
--測驗3：
MERGE INTO t1 b
USING t2 e
ON (b.ID = e.ID)
WHEN MATCHED THEN
update
set b.NAME = E.NAME,
b.AGE = E.AGE,
b.SE = E.SE
where b.id='1201'
delete where (b.id = '1201')
WHEN NOT MATCHED THEN
insert (b.ID,b.NAME,b.AGE,b.SE)
values(E.ID,E.NAME,E.AGE,E.SE) ;
--結果3：
SQL> select * from t1;
ID NAME AGE SE
---------- -------------------- ---------- --------------------
6695 Lu 12 female
6720 Ka 22 female
1202 lili 30 female
當既滿足on條件又滿足update中的where條件時，delete陳述句執行了。。。。
為了確保萬一，我們驗證一下不滿足update中where條件的情況：
--測驗4：
MERGE INTO t1 b
USING t2 e
ON (b.ID = e.ID)
WHEN MATCHED THEN
update
set b.NAME = E.NAME,
b.AGE = E.AGE,
b.SE = E.SE
where b.id<>'1201'
delete where (b.name = 'Peter')
WHEN NOT MATCHED THEN
insert (b.ID,b.NAME,b.AGE,b.SE)
values(E.ID,E.NAME,E.AGE,E.SE) ;
--結果4：
SQL> select * from t1;
ID NAME AGE SE
---------- -------------------- ---------- --------------------
6695 Lu 12 female
6720 Ka 22 female
1201 Peter 23 male
1202 lili 30 female
如上，對于1201記錄，雖然既滿足on條件，又滿足delete中的where條件，但是因為不滿足update的where條件，所以delete并沒有執行。。。。
綜上：在merge into中，當update子句中存在where clause時，只有滿足on和update中的where雙重條件時，才會執行delete 子句。
如有疏漏的地方，歡迎大家指正~
uj5u.com熱心網友回復：
我想也是這樣，on是匹配大條件，只有on中條件滿足了入選，在此條件下，如果有where子句，再來看是否滿足。uj5u.com熱心網友回復：
主要是用來驗證update 和 delete 之間的關系
uj5u.com熱心網友回復：
uj5u.com熱心網友回復：
樓主不厚道，散分太少
uj5u.com熱心網友回復：
還是我來加到 200 吧！
uj5u.com熱心網友回復：
還是版主大氣
uj5u.com熱心網友回復：
還可以加分呢
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/116430.html
標籤：高級技術
上一篇：Oracle安裝問題INS-20802] Oracle Net Configuration Assistant failed.
下一篇：如何在oracle中求95分位數