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中的值
