Oracle9i引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行inserts和updates操作. MERGE命令从一个或多个数据源中选择行来updating或inserting到一个或多个表.在Oracle10g中MERGE有如下一些改进：
1、UPDATE或INSERT子句是可选的
2、UPDATE和INSERT子句可以加WHERE子句
3、在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表
4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行
语法:
MERGEHINTINTO SCHEMA .TABLE T_ALIAS
USINGSCHEMA . {TABLE | VIEW |SUBQUERY } T_ALIAS
ON (CONDITION)
WHENMATCHEDTHEN MERGE_UPDATE_CLAUSE
WHENNOTMATCHED THEN MERGE_INSERT_CLAUSE / MERGE_DELETE_CLAUSE;
联想:
merge into是特有的功能，相当于在 MSSQL中的
ifexists(...)
updatetable
else
Insertinto table.
mergeinto语法不仅没有if exists语法啰嗦，而且比if exists还要高效很多，常用来在oracle之间同步数据库表。
例子：
1、创建测试表及数据
- DROP TABLE PRODUCTS;
- DROP TABLE NEWPRODUCTS;
- 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;
2、匹配更新
- MERGE INTO PRODUCTS P
- USING NEWPRODUCTS NP
- ON (P.PRODUCT_ID = NP.PRODUCT_ID)
- WHEN MATCHED THEN
- UPDATE
- SET P.PRODUCT_NAME = NP.PRODUCT_NAME,
- P.CATEGORY = NP.CATEGORY;
- SELECT * FROM PRODUCTS;
- SELECT * FROM NEWPRODUCTS;
3、不匹配插入
- MERGE INTO PRODUCTS P
- USING NEWPRODUCTS NP
- ON (P.PRODUCT_ID = NP.PRODUCT_ID)
- WHEN NOT MATCHED THEN
- INSERT
- (PRODUCT_ID
- ,PRODUCT_NAME
- ,CATEGORY)
- VALUES
- (NP.PRODUCT_ID
- ,NP.PRODUCT_NAME
- ,NP.CATEGORY);
- SELECT * FROM PRODUCTS;
- SELECT * FROM NEWPRODUCTS;
4、匹配带where/on更新
- MERGE INTO PRODUCTS P
- USING NEWPRODUCTS NP
- ON (P.PRODUCT_ID = NP.PRODUCT_ID)
- WHEN MATCHED THEN
- UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME WHERE P.CATEGORY = NP.CATEGORY;
- MERGE INTO PRODUCTS P
- USING NEWPRODUCTS NP
- ON (P.PRODUCT_ID = NP.PRODUCT_ID AND P.CATEGORY = NP.CATEGORY)
- WHEN MATCHED THEN
- UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME;
- SELECT * FROM PRODUCTS;
- SELECT * FROM NEWPRODUCTS;
- SELECT *
- FROM PRODUCTS A
- INNER JOIN NEWPRODUCTS B
- ON A.PRODUCT_ID = B.PRODUCT_ID
- AND A.CATEGORY = B.CATEGORY;
5、匹配带where更新、插入
- MERGE INTO PRODUCTS P
- USING NEWPRODUCTS NP
- ON (P.PRODUCT_ID = NP.PRODUCT_ID)
- WHEN MATCHED THEN
- UPDATE
- SET P.PRODUCT_NAME = NP.PRODUCT_NAME,
- P.CATEGORY = NP.CATEGORY
- WHERE P.CATEGORY = 'DVD'
- WHEN NOT MATCHED THEN
- INSERT
- (PRODUCT_ID
- ,PRODUCT_NAME
- ,CATEGORY)
- VALUES
- (NP.PRODUCT_ID
- ,NP.PRODUCT_NAME
- ,NP.CATEGORY) WHERE NP.CATEGORY != 'BOOKS';
- SELECT * FROM PRODUCTS;
- SELECT * FROM NEWPRODUCTS;
6、ON常量表达式
- MERGE INTO PRODUCTS P
- USING NEWPRODUCTS NP
- ON (1 = 0)
- WHEN NOT MATCHED THEN
- INSERT
- (PRODUCT_ID
- ,PRODUCT_NAME
- ,CATEGORY)
- VALUES
- (NP.PRODUCT_ID
- ,NP.PRODUCT_NAME
- ,NP.CATEGORY) WHERE NP.CATEGORY = 'BOOKS';
- SELECT * FROM PRODUCTS;
- SELECT * FROM NEWPRODUCTS;
7、匹配删除、不匹配插入
- MERGE INTO PRODUCTS P
- USING NEWPRODUCTS NP
- ON (P.PRODUCT_ID = NP.PRODUCT_ID)
- WHEN MATCHED THEN
- UPDATE
- SET P.PRODUCT_NAME = NP.PRODUCT_NAME,
- P.CATEGORY = NP.CATEGORY DELETE
- WHERE (P.CATEGORY = 'ELECTRNCS')
- WHEN NOT MATCHED THEN
- INSERT
- (PRODUCT_ID
- ,PRODUCT_NAME
- ,CATEGORY)
- VALUES
- (NP.PRODUCT_ID
- ,NP.PRODUCT_NAME
- ,NP.CATEGORY);
- SELECT * FROM PRODUCTS;
- SELECT * FROM NEWPRODUCTS;
8、源表为子查询(自联接)
- MERGE INTO PRODUCTS P
- USING (SELECT COUNT(*) CO FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = 1501) B
- ON (B.CO <> 0)
- WHEN MATCHED THEN
- UPDATE SET P.PRODUCT_NAME = 'KEBO' WHERE P.PRODUCT_ID = 1501
- WHEN NOT MATCHED THEN
- INSERT
- (PRODUCT_ID
- ,PRODUCT_NAME
- ,CATEGORY)
- VALUES
- (1501
- ,'KEBO'
- ,'NBA');
- MERGE INTO PRODUCTS P
- USING (SELECT COUNT(*) CO FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = 1508) B
- ON (B.CO <> 0)
- WHEN MATCHED THEN
- UPDATE SET P.PRODUCT_NAME = 'KEBO' WHERE P.PRODUCT_ID = 1508
- WHEN NOT MATCHED THEN
- INSERT
- (PRODUCT_ID
- ,PRODUCT_NAME
- ,CATEGORY)
- VALUES
- (1508
- ,'KEBO'
- ,'NBA');
- SELECT * FROM PRODUCTS;
优点：
—避免了分开更新
—提高性能并易于使用
—在数据仓库应用中十分有用
—使用merge比传统的先判断再选择插入或更新快很多
需要注意的地方：
1、从语法条件上看（ON (join condition)），merge into也是一个危险的语法。如果不慎重使用，会将源表全部覆盖到目标表，既危险又浪费效率，违背了增量同步的原则。所以在设计表结构时，一般每条记录都有“更新时间”的字段，用目标表“最大更新时间”判断源表数据是否有更新和新增的信息。
2、更新的字段，不允许有关联条件的字段（join condition）。比如条件是 A.ID=B.ID，那么使用“SET A.ID=B.ID”将报出一个莫名其妙的提示错误。
缺少一个带delete语句选项的示例，暂为补上：
- MERGE INTO PRODUCTS P
- USING NEWPRODUCTS NP
- ON (P.PRODUCT_ID = NP.PRODUCT_ID)
- WHEN MATCHED THEN
- UPDATE
- SET P.PRODUCT_NAME = NP.PRODUCT_NAME, P.CATEGORY = NP.CATEGORY
- --WHERE (P.CATEGORY = 'ELECTRNCS')
- DELETE
- WHERE (P.CATEGORY = 'ELECTRNCS')
- WHEN NOT MATCHED THEN
- INSERT
- (PRODUCT_ID, PRODUCT_NAME, CATEGORY)
- VALUES
- (NP.PRODUCT_ID, NP.PRODUCT_NAME, NP.CATEGORY);
- SELECT * FROM PRODUCTS;
- SELECT * FROM NEWPRODUCTS;
说明：DELETE语句删除的是满足matched关联on条件，同时也要是update更新内容的子集，否则不会删除任何内容。
SQL> select * from subs;
MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0 531
905320001 1 532
905330001 2 533
SQL> select * from acct;
MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0 531
905320001 1 532
905330001 2 533
SQL>
SQL> merge into acct a
2 using subs b on (a.msid=b.msid)
3 when MATCHED then
4 update set a.areacode=b.areacode
5 delete where (b.ms_type!=0);
Done
SQL> select * from acct;
MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0 531
