ORACLE中meger的使用

原帖地址:http://blog.csdn.net/nsj820/article/details/5755685

Oracle9i引入了MERGE命令,你能夠在一個SQL語句中對一個表同時執行inserts和updates操作. MERGE命令從一個或多個資料來源中選擇行來updating或inserting到一個或多個表.在Oracle 10g中MERGE有如下一些改進：

1、UPDATE或INSERT子句是可選的

2、UPDATE和INSERT子句可以加WHERE子句

3、在ON條件中使用常量過濾謂詞來insert所有的行到目標表中,不需要串連源表和目標表

4、UPDATE子句後面可以跟DELETE子句來去除一些不需要的行

文法:

MERGEHINTINTOSCHEMA . TABLE T_ALIAS

USINGSCHEMA . { TABLE | VIEW | SUBQUERY } T_ALIAS

ON (CONDITION)

WHENMATCHEDTHEN MERGE_UPDATE_CLAUSE

WHENNOTMATCHEDTHEN MERGE_INSERT_CLAUSE / MERGE_DELETE_CLAUSE;

聯想:
mergeinto是特有的功能，相當於在 MSSQL中的

ifexists(...)

updatetable

else

Insertinto table.

mergeinto文法不僅沒有if exists文法囉嗦，而且比if exists 還要高效很多，常用來在oracle之間同步資料庫表。

例子：

1、 建立測試表及資料

  [c-sharp] view plain copy print ? 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、 匹配更新

  [c-sharp] view plain copy print ? 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、 不匹配插入

  [c-sharp] view plain copy print ? 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更新

  [c-sharp] view plain copy print ? 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更新、插入

  [c-sharp] view plain copy print ? 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常量運算式

  [c-sharp] view plain copy print ? 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、 匹配刪除、不匹配插入

  [c-sharp] view plain copy print ? 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、 源表為子查詢(自我聯結)

  [c-sharp] view plain copy print ? 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” 將報出一個莫名其妙的提示錯誤。