Oracle9i引入了MERGE命令,你能夠在一個SQL語句中對一個表同時(shí)執(zhí)行inserts和updates操作. MERGE命令從一個或多個數(shù)據(jù)源中選擇行來updating或inserting到一個或多個表.在Oracle 10g中MERGE有如下一些改進(jìn)： 1、UPDATE或INSERT子句是可選的 2、UPDATE和INSERT子句可以加WHERE子句 3、在ON條件中使用常量過濾謂詞來insert所有的行到目標(biāo)表中,不需要連接源表和目標(biāo)表 4、UPDATE子句后面可以跟DELETE子句來去除一些不需要的行 首先創(chuàng)建示例表：
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語句要求你必須同時(shí)指定INSERT和UPDATE子句.而在Oracle 10g, 你可以省略UPDATE或INSERT子句中的一個. 下面的例子根據(jù)表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語句影響到是產(chǎn)品id為1502, 1601和1666的行. 它們的產(chǎn)品名字和種 類被更新為表newproducts中的值. 下面例子省略UPDATE子句, 把表NEWPRODUCTS中新的PRODUCT_ID插入到表PRODUCTS中, 對于在兩個表中能夠匹配上PRODUCT_ID的數(shù)據(jù)不作任何處理. 從這個例子你能看到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操作對某些行的處理. 下面例子根據(jù)表NEWPRODUCTS來更新表PRODUCTS數(shù)據(jù), 但必須字段CATEGORY也得同時(shí)匹配上:
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; 在這個例子中, 產(chǎn)品ID為1502,1601和1666匹配ON條件但是1666的category不匹配. 因此MERGE命令只更新兩行數(shù)據(jù). 下面例子展示了在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 你能夠不用連接源表和目標(biāo)表就把源表的數(shù)據(jù)插入到目標(biāo)表中. 這對于你想插入所有行到目標(biāo)表時(shí)是非常有用的. Oracle 10g現(xiàn)在支持在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> 4、新增加的DELETE子句 Oracle 10g中的MERGE提供了在執(zhí)行數(shù)據(jù)操作時(shí)清除行的選項(xiàng). 你能夠在WHEN MATCHED THEN UPDATE子句中包含DELETE子句. DELETE子句必須有一個WHERE條件來刪除匹配某些條件的行.匹配DELETE WHERE條件但不匹配ON條件的行不會被從表中刪除.
下面例子驗(yàn)證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> 產(chǎn)品ID為1502的行從表PRODUCTS中被刪除, 因?yàn)樗瑫r(shí)匹配ON條件和DELETE WHERE條件. 產(chǎn)品ID為1501的行匹配DELETE WHERE條件但不匹配ON條件, 所以它沒有被刪除. 產(chǎn)品ID為1700 的行不匹配ON條件, 所以被插入表PRODUCTS. 產(chǎn)品ID為1601和1666的行匹配ON條件但不匹配DELETE WHERE條件, 所以被更新為表NEWPRODUCTS中的值.
