轉自http://blog.chinaunix.net/space.php?uid=16981447&do=blog&cuid=430716做了簡單的格式整理，加入了一點點原創的東西。Oracle9i引入了MERGE命令,你能夠在一個SQL語句中對一個表同時執行inserts和updates操作. MERGE命令從一個或多個數據源中選擇行來updating或inserting到一個或多個表.在Oracle 10g中MERGE有如下一些改進：?

1、UPDATE或INSERT子句是可選的?
2、UPDATE和INSERT子句可以加WHERE子句?
3、在ON條件中使用常量過濾謂詞來insert所有的行到目標表中,不需要連接源表和目標表?(可以using dual)
4、UPDATE子句后面可以跟DELETE子句來去除一些不需要的行?


首先創建示例表：?
create table PRODUCTS
??? (
??? PRODUCT_ID INTEGER,
??? PRODUCT_NAME VARCHAR2(60),
??? CATEGORY VARCHAR2(60)
??? );

??? insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
??? insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
??? insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
??? insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');
??? insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
??? commit;

??? create table NEWPRODUCTS
??? (
??? PRODUCT_ID INTEGER,
??? PRODUCT_NAME VARCHAR2(60),
??? CATEGORY VARCHAR2(60)
??? );

??? insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
??? insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
??? insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
??? insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
??? commit;?

1、可省略的UPDATE或INSERT子句?

在Oracle 9i, MERGE語句要求你必須同時指定INSERT和UPDATE子句.而在Oracle 10g, 你可以省略UPDATE或INSERT子句中的一個. 下面的例子根據表NEWPRODUCTS的PRODUCT_ID字段是否匹配來updates表PRODUCTS的信息:

SQL> MERGE INTO products p
??? 2 USING newproducts np
??? 3 ON (p.product_id = np.product_id)
??? 4 WHEN MATCHED THEN
??? 5 UPDATE
??? 6 SET p.product_name = np.product_name,
??? 7 p.category = np.category;

??? 3 rows merged.

??? SQL> SELECT * FROM products;

??? PRODUCT_ID PRODUCT_NAME CATEGORY
??? ---------- -------------------- ----------
??? 1501 VIVITAR 35MM ELECTRNCS
??? 1502 OLYMPUS CAMERA ELECTRNCS
??? 1600 PLAY GYM TOYS
??? 1601 LAMAZE TOYS
??? 1666 HARRY POTTER TOYS
??? SQL>
??? SQL> ROLLBACK;
??? Rollback complete.
??? SQL>?


在上面例子中, MERGE語句影響到是產品id為1502, 1601和1666的行. 它們的產品名字和種 類被更新為表newproducts中的值. 下面例子省略UPDATE子句, 把表NEWPRODUCTS中新的PRODUCT_ID插入到表PRODUCTS中, 對于在兩個表中能夠匹配上PRODUCT_ID的數據不作任何處理. 從這個例子你能看到PRODUCT_ID=1700的行被插入到表PRODUCTS中.?


SQL> MERGE INTO products p
??? 2 USING newproducts np
??? 3 ON (p.product_id = np.product_id)
??? 4 WHEN NOT MATCHED THEN
??? 5 INSERT
??? 6 VALUES (np.product_id, np.product_name,
??? 7 np.category);

??? 1 row merged.

??? SQL> SELECT * FROM products;

??? PRODUCT_ID PRODUCT_NAME CATEGORY
??? ---------- -------------------- ----------
??? 1501 VIVITAR 35MM ELECTRNCS
??? 1502 OLYMPUS IS50 ELECTRNCS
??? 1600 PLAY GYM TOYS
??? 1601 LAMAZE TOYS
??? 1666 HARRY POTTER DVD
??? 1700 WAIT INTERFACE BOOKS?

2、帶條件的Updates和Inserts子句?

你能夠添加WHERE子句到UPDATE或INSERT子句中去, 來跳過update或insert操作對某些行的處理. 下面例子根據表NEWPRODUCTS來更新表PRODUCTS數據, 但必須字段CATEGORY也得同時匹配上:?

SQL> MERGE INTO products p
??? 2 USING newproducts np
??? 3 ON (p.product_id = np.product_id)
??? 4 WHEN MATCHED THEN
??? 5 UPDATE
??? 6 SET p.product_name = np.product_name
??? 7 WHERE p.category = np.category;

??? 2 rows merged.

??? SQL> SELECT * FROM products;

??? PRODUCT_ID PRODUCT_NAME CATEGORY
??? ---------- -------------------- ----------
??? 1501 VIVITAR 35MM ELECTRNCS
??? 1502 OLYMPUS CAMERA ELECTRNCS
??? 1600 PLAY GYM TOYS
??? 1601 LAMAZE TOYS
??? 1666 HARRY POTTER DVD
??? SQL>
??? SQL> rollback;?

在這個例子中, 產品ID為1502,1601和1666匹配ON條件但是1666的category不匹配. 因此MERGE命令只更新兩行數據. 下面例子展示了在Updates和Inserts子句都使用WHERE子句:?


SQL> MERGE INTO products p
??? 2 USING newproducts np
??? 3 ON (p.product_id = np.product_id)
??? 4 WHEN MATCHED THEN
??? 5 UPDATE
??? 6 SET p.product_name = np.product_name,
??? 7 p.category = np.category
??? 8 WHERE p.category = 'DVD'
??? 9 WHEN NOT MATCHED THEN
??? 10 INSERT
??? 11 VALUES (np.product_id, np.product_name, np.category)
??? 12 WHERE np.category != 'BOOKS'
??? SQL> /

??? 1 row merged.

??? SQL> SELECT * FROM products;

??? PRODUCT_ID PRODUCT_NAME CATEGORY
??? ---------- -------------------- ----------
??? 1501 VIVITAR 35MM ELECTRNCS
??? 1502 OLYMPUS IS50 ELECTRNCS
??? 1600 PLAY GYM TOYS
??? 1601 LAMAZE TOYS
??? 1666 HARRY POTTER TOYS

??? SQL>?


注意由于有WHERE子句INSERT沒有插入所有不匹配ON條件的行到表PRODUCTS.?

3、無條件的Inserts?

你能夠不用連接源表和目標表就把源表的數據插入到目標表中. 這對于你想插入所有行到目標表時是非常有用的. Oracle 10g現在支持在ON條件中使用常量過濾謂詞. 舉個常量過濾謂詞例子ON (1=0). 下面例子從源表插入行到表PRODUCTS, 不檢查這些行是否在表PRODUCTS中存在:

SQL> MERGE INTO products p
??? 2 USING newproducts np
??? 3 ON (1=0)
??? 4 WHEN NOT MATCHED THEN
??? 5 INSERT
??? 6 VALUES (np.product_id, np.product_name, np.category)
??? 7 WHERE np.category = 'BOOKS'
??? SQL> /

??? 1 row merged.

??? SQL> SELECT * FROM products;

??? PRODUCT_ID PRODUCT_NAME CATEGORY
??? ---------- -------------------- ----------
??? 1501 VIVITAR 35MM ELECTRNCS
??? 1502 OLYMPUS IS50 ELECTRNCS
??? 1600 PLAY GYM TOYS
??? 1601 LAMAZE TOYS
??? 1666 HARRY POTTER DVD
??? 1700 WAIT INTERFACE BOOKS
??? 6 rows selected.
??? SQL>?using 后面可以用dual這個特殊的表，可參考下面的例子（這部分是原創）PROCEDURE UPDATE_PR_DATA(p_rec REFD_SECMASTER_BBGPRICE_TMP%ROWTYPE) IS
v_date DATE;
v_now DATE;
BEGIN?
????v_now := SYSDATE;
????v_date := trunc(v_now);

????MERGE INTO REFD_SECMASTER_BBGPRICE p
????USING dual
???? ON (p.fj_secmaster_id = p_rec.fj_secmaster_id
???????????? AND p.fj_price_date = v_date)
????WHEN MATCHED THEN
????UPDATE?
???????? SET p.FJ_MODIFIED_TMS = v_now,
???????????????? ......
????WHEN NOT MATCHED THEN
????INSERT?
???????? (FJ_SECMASTER_ID,
????????????...)
???? VALUES
???????? (p_rec.FJ_SECMASTER_ID,
????????????...
???????? );
END;

4、新增加的DELETE子句?

Oracle 10g中的MERGE提供了在執行數據操作時清除行的選項. 你能夠在WHEN MATCHED THEN UPDATE子句中包含DELETE子句. DELETE子句必須有一個WHERE條件來刪除匹配某些條件的行.匹配DELETE WHERE條件但不匹配ON條件的行不會被從表中刪除.?


下面例子驗證DELETE子句. 我們從表NEWPRODUCTS中合并行到表PRODUCTS中, 但刪除category為ELECTRNCS的行.?


SQL> MERGE INTO products p
??? 2 USING newproducts np
??? 3 ON (p.product_id = np.product_id)
??? 4 WHEN MATCHED THEN
??? 5 UPDATE
??? 6 SET p.product_name = np.product_name,
??? 7 p.category = np.category
??? 8 DELETE WHERE (p.category = 'ELECTRNCS')
??? 9 WHEN NOT MATCHED THEN
??? 10 INSERT
??? 11 VALUES (np.product_id, np.product_name, np.category)
??? SQL> /

??? 4 rows merged.

??? SQL> SELECT * FROM products;

??? PRODUCT_ID PRODUCT_NAME CATEGORY
??? ---------- -------------------- ----------
??? 1501 VIVITAR 35MM ELECTRNCS
??? 1600 PLAY GYM TOYS
??? 1601 LAMAZE TOYS
??? 1666 HARRY POTTER TOYS
??? 1700 WAIT INTERFACE BOOKS
??? SQL>?

產品ID為1502的行從表PRODUCTS中被刪除, 因為它同時匹配ON條件和DELETE WHERE條件. 產品ID為1501的行匹配DELETE WHERE條件但不匹配ON條件, 所以它沒有被刪除. 產品ID為1700 的行不匹配ON條件, 所以被插入表PRODUCTS. 產品ID為1601和1666的行匹配ON條件但不匹配DELETE WHERE條件, 所以被更新為表NEWPRODUCTS中的值.?

Published by Wiz

轉載于:https://www.cnblogs.com/teamleader/archive/2011/10/11/2206792.html

總結

以上是生活随笔為你收集整理的Oracle中的MERGE语句的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。