Oraclei引入了MERGE命令你能夠在一個SQL語句中對一個表同時執行inserts和updates操作 MERGE命令從一個或多個數據源中選擇行來updating或inserting到一個或多個表在Oracle g中MERGE有如下一些改進
UPDATE或INSERT子句是可選的
UPDATE和INSERT子句可以加WHERE子句
在ON條件中使用常量過濾謂詞來insert所有的行到目標表中不需要連接源表和目標表
UPDATE子句後面可以跟DELETE子句來去除一些不需要的行
首先創建示例表
create table PRODUCTS
(
PRODUCT_ID INTEGER
PRODUCT_NAME VARCHAR()
CATEGORY VARCHAR()
);
insert into PRODUCTS values ( VIVITAR MM ELECTRNCS);
insert into PRODUCTS values ( OLYMPUS IS ELECTRNCS);
insert into PRODUCTS values ( PLAY GYM TOYS);
insert into PRODUCTS values ( LAMAZE TOYS);
insert into PRODUCTS values ( HARRY POTTER DVD);
commit;
create table NEWPRODUCTS
(
PRODUCT_ID INTEGER
PRODUCT_NAME VARCHAR()
CATEGORY VARCHAR()
);
insert into NEWPRODUCTS values ( OLYMPUS CAMERA ELECTRNCS);
insert into NEWPRODUCTS values ( LAMAZE TOYS);
insert into NEWPRODUCTS values ( HARRY POTTER TOYS);
insert into NEWPRODUCTS values ( WAIT INTERFACE BOOKS);
commit;
可省略的UPDATE或INSERT子句
在Oracle i MERGE語句要求你必須同時指定INSERT和UPDATE子句而在Oracle g 你可以省略UPDATE或INSERT子句中的一個 下面的例子根據表NEWPRODUCTS的PRODUCT_ID字段是否匹配來updates表PRODUCTS的信息:
SQL> MERGE INTO products p
USING newproducts np
ON (pproduct_id = npproduct_id)
WHEN MATCHED THEN
UPDATE
SET pproduct_name = npproduct_name
pcategory = npcategory;
rows merged
SQL> SELECT * FROM products;
PRODUCT_ID PRODUCT_NAME CATEGORY
VIVITAR MM ELECTRNCS
OLYMPUS CAMERA ELECTRNCS
PLAY GYM TOYS
LAMAZE TOYS
HARRY POTTER TOYS
SQL>
SQL> ROLLBACK;
Rollback complete
SQL>
在上面例子中 MERGE語句影響到是產品id為 和的行 它們的產品名字和種 類被更新為表newproducts中的值 下面例子省略UPDATE子句 把表NEWPRODUCTS中新的PRODUCT_ID插入到表PRODUCTS中 對於在兩個表中能夠匹配上PRODUCT_ID的數據不作任何處理 從這個例子你能看到PRODUCT_ID=的行被插入到表PRODUCTS中
SQL> MERGE INTO products p
USING newproducts np
ON (pproduct_id = npproduct_id)
WHEN NOT MATCHED THEN
INSERT
VALUES (npproduct_id npproduct_name
npcategory);
row merged
SQL> SELECT * FROM products;
PRODUCT_ID PRODUCT_NAME CATEGORY
VIVITAR MM ELECTRNCS
OLYMPUS IS ELECTRNCS
PLAY GYM TOYS
LAMAZE TOYS
HARRY POTTER DVD
WAIT INTERFACE BOOKS
帶條件的Updates和Inserts子句
你能夠添加WHERE子句到UPDATE或INSERT子句中去 來跳過update或insert操作對某些行的處理 下面例子根據表NEWPRODUCTS來更新表PRODUCTS數據 但必須字段CATEGORY也得同時匹配上:
SQL> MERGE INTO products p
USING newproducts np
ON (pproduct_id = npproduct_id)
WHEN MATCHED THEN
UPDATE
SET pproduct_name = npproduct_name
WHERE pcategory = npcategory;
rows merged
SQL> SELECT * FROM products;
PRODUCT_ID PRODUCT_NAME CATEGORY
VIVITAR MM ELECTRNCS
OLYMPUS CAMERA ELECTRNCS
PLAY GYM TOYS
LAMAZE TOYS
HARRY POTTER DVD
SQL>
SQL> rollback;
在這個例子中 產品ID為和匹配ON條件但是的category不匹配 因此MERGE命令只更新兩行數據 下面例子展示了在Updates和Inserts子句都使用WHERE子句:
SQL> MERGE INTO products p
USING newproducts np
ON (pproduct_id = npproduct_id)
WHEN MATCHED THEN
UPDATE
SET pproduct_name = npproduct_name
pcategory = npcategory
WHERE pcategory = DVD
WHEN NOT MATCHED THEN
INSERT
VALUES (npproduct_id npproduct_name npcategory)
WHERE npcategory != BOOKS
SQL> /
row merged
SQL> SELECT * FROM products;
PRODUCT_ID PRODUCT_NAME CATEGORY
VIVITAR MM ELECTRNCS
OLYMPUS IS ELECTRNCS
PLAY GYM TOYS
LAMAZE TOYS
HARRY POTTER TOYS
SQL>
注意由於有WHERE子句INSERT沒有插入所有不匹配ON條件的行到表PRODUCTS
無條件的Inserts
你能夠不用連接源表和目標表就把源表的數據插入到目標表中 這對於你想插入所有行到目標表時是非常有用的 Oracle g現在支持在ON條件中使用常量過濾謂詞 舉個常量過濾謂詞例子ON (=) 下面例子從源表插入行到表PRODUCTS 不檢查這些行是否在表PRODUCTS中存在:
SQL> MERGE INTO products p
USING newproducts np
ON (=)
WHEN NOT MATCHED THEN
INSERT
VALUES (npproduct_id npproduct_name npcategory)
WHERE npcategory = BOOKS
SQL> /
row merged
SQL> SELECT * FROM products;
PRODUCT_ID PRODUCT_NAME CATEGORY
VIVITAR MM ELECTRNCS
OLYMPUS IS ELECTRNCS
PLAY GYM TOYS
LAMAZE TOYS
HARRY POTTER DVD
WAIT INTERFACE BOOKS
rows selected
SQL>
新增加的DELETE子句
Oracle g中的MERGE提供了在執行數據操作時清除行的選項 你能夠在WHEN MATCHED THEN UPDATE子句中包含DELETE子句 DELETE子句必須有一個WHERE條件來刪除匹配某些條件的行匹配DELETE WHERE條件但不匹配ON條件的行不會被從表中刪除
下面例子驗證DELETE子句 我們從表NEWPRODUCTS中合並行到表PRODUCTS中 但刪除category為ELECTRNCS的行
SQL> MERGE INTO products p
USING newproducts np
ON (pproduct_id = npproduct_id)
WHEN MATCHED THEN
UPDATE
SET pproduct_name = npproduct_name
pcategory = npcategory
DELETE WHERE (pcategory = ELECTRNCS)
WHEN NOT MATCHED THEN
INSERT
VALUES (npproduct_id npproduct_name npcategory)
SQL> /
rows merged
SQL> SELECT * FROM products;
PRODUCT_ID PRODUCT_NAME CATEGORY
VIVITAR MM ELECTRNCS
PLAY GYM TOYS
LAMAZE TOYS
HARRY POTTER TOYS
WAIT INTERFACE BOOKS
SQL>
產品ID為的行從表PRODUCTS中被刪除 因為它同時匹配ON條件和DELETE WHERE條件 產品ID為的行匹配DELETE WHERE條件但不匹配ON條件 所以它沒有被刪除 產品ID為 的行不匹配ON條件 所以被插入表PRODUCTS 產品ID為和的行匹配ON條件但不匹配DELETE WHERE條件 所以被更新為表NEWPRODUCTS中的值
From:http://tw.wingwit.com/Article/program/Oracle/201311/18698.html
