分類  >  資料庫 >

Oracle merge into 下令

tags:    時間:2013-12-23 21:48:55
Oracle merge into 命令
原文:http://jayklin.iteye.com/blog/1669114

作用:merge into 解決用B表跟新A表數據,如果A表中沒有,則把B表的數據插入A表;當處理大數據量是,該方法的效率很高。

語法:
MERGE INTO [your table-name] [rename your table here]
USING ( [write your query here] )[rename your query-sql and using just like a table]
ON ([conditional expression here] AND [...]...)
WHEN MATHED THEN [here you can execute some update sql or something else ]
WHEN NOT MATHED THEN [execute something else here ! ]

copy:http://blog.csdn.net/nsj820/article/details/5755685


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、創建測試表及數據




[c-sharp] view plaincopyprint?
01.DROP TABLE PRODUCTS; 
02.DROP TABLE NEWPRODUCTS; 
03.create table PRODUCTS 
04.( 
05.PRODUCT_ID INTEGER, 
06.PRODUCT_NAME VARCHAR2(60), 
07.CATEGORY VARCHAR2(60) 
08.); 
09.insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS'); 
10.insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS'); 
11.insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS'); 
12.insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS'); 
13.insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD'); 
14.commit; 
15.create table NEWPRODUCTS 
16.( 
17.PRODUCT_ID INTEGER, 
18.PRODUCT_NAME VARCHAR2(60), 
19.CATEGORY VARCHAR2(60) 
20.); 
21.insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS'); 
22.insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS'); 
23.insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS'); 
24.insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS'); 
25.commit; 


2、匹配更新




[c-sharp] view plaincopyprint?
01.MERGE INTO PRODUCTS P 
02.USING NEWPRODUCTS NP 
03.ON (P.PRODUCT_ID = NP.PRODUCT_ID) 
04.WHEN MATCHED THEN 
05.  UPDATE 
06.     SET P.PRODUCT_NAME = NP.PRODUCT_NAME, 
07.         P.CATEGORY     = NP.CATEGORY; 
08. 
09.SELECT * FROM PRODUCTS; 
10.SELECT * FROM NEWPRODUCTS; 


3、不匹配插入




[c-sharp] view plaincopyprint?
01.MERGE INTO PRODUCTS P 
02.USING NEWPRODUCTS NP 
03.ON (P.PRODUCT_ID = NP.PRODUCT_ID) 
04.WHEN NOT MATCHED THEN 
05.  INSERT 
06.    (PRODUCT_ID 
07.    ,PRODUCT_NAME 
08.    ,CATEGORY) 
09.  VALUES 
10.    (NP.PRODUCT_ID 
11.    ,NP.PRODUCT_NAME 
12.    ,NP.CATEGORY); 
13. 
14.SELECT * FROM PRODUCTS; 
15.SELECT * FROM NEWPRODUCTS; 


4、匹配帶where/on更新




[c-sharp] view plaincopyprint?
01.MERGE INTO PRODUCTS P 
02.USING NEWPRODUCTS NP 
03.ON (P.PRODUCT_ID = NP.PRODUCT_ID) 
04.WHEN MATCHED THEN 
05.  UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME WHERE P.CATEGORY = NP.CATEGORY; 
06. 
07.MERGE INTO PRODUCTS P 
08.USING NEWPRODUCTS NP 
09.ON (P.PRODUCT_ID = NP.PRODUCT_ID AND P.CATEGORY = NP.CATEGORY) 
10.WHEN MATCHED THEN 
11.  UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME; 
12. 
13.SELECT * FROM PRODUCTS; 
14.SELECT * FROM NEWPRODUCTS; 
15. 
16.SELECT * 
17.  FROM PRODUCTS A 
18. INNER JOIN NEWPRODUCTS B 
19.    ON A.PRODUCT_ID = B.PRODUCT_ID 
20.   AND A.CATEGORY = B.CATEGORY; 


5、匹配帶where更新、插入




[c-sharp] view plaincopyprint?
01.MERGE INTO PRODUCTS P 
02.USING NEWPRODUCTS NP 
03.ON (P.PRODUCT_ID = NP.PRODUCT_ID) 
04.WHEN MATCHED THEN 
05.  UPDATE 
06.     SET P.PRODUCT_NAME = NP.PRODUCT_NAME, 
07.         P.CATEGORY     = NP.CATEGORY 
08.   WHERE P.CATEGORY = 'DVD' 
09.WHEN NOT MATCHED THEN 
10.  INSERT 
11.    (PRODUCT_ID 
12.    ,PRODUCT_NAME 
13.    ,CATEGORY) 
14.  VALUES 
15.    (NP.PRODUCT_ID 
16.    ,NP.PRODUCT_NAME 
17.    ,NP.CATEGORY) WHERE NP.CATEGORY != 'BOOKS'; 
18. 
19.SELECT * FROM PRODUCTS; 
20.SELECT * FROM NEWPRODUCTS; 


6、ON常量表達式




[c-sharp] view plaincopyprint?
01.MERGE INTO PRODUCTS P 
02.USING NEWPRODUCTS NP 
03.ON (1 = 0) 
04.WHEN NOT MATCHED THEN 
05.  INSERT 
06.    (PRODUCT_ID 
07.    ,PRODUCT_NAME 
08.    ,CATEGORY) 
09.  VALUES 
10.    (NP.PRODUCT_ID 
11.    ,NP.PRODUCT_NAME 
12.    ,NP.CATEGORY) WHERE NP.CATEGORY = 'BOOKS'; 
13. 
14.SELECT * FROM PRODUCTS; 
15.SELECT * FROM NEWPRODUCTS; 


7、匹配刪除、不匹配插入




[c-sharp] view plaincopyprint?
01.MERGE INTO PRODUCTS P 
02.USING NEWPRODUCTS NP 
03.ON (P.PRODUCT_ID = NP.PRODUCT_ID) 
04.WHEN MATCHED THEN 
05.  UPDATE 
06.     SET P.PRODUCT_NAME = NP.PRODUCT_NAME, 
07.         P.CATEGORY     = NP.CATEGORY DELETE 
08.   WHERE (P.CATEGORY = 'ELECTRNCS') 
09.WHEN NOT MATCHED THEN 
10.  INSERT 
11.    (PRODUCT_ID 
12.    ,PRODUCT_NAME 
13.    ,CATEGORY) 
14.  VALUES 
15.    (NP.PRODUCT_ID 
16.    ,NP.PRODUCT_NAME 
17.    ,NP.CATEGORY); 
18. 
19.SELECT * FROM PRODUCTS; 
20.SELECT * FROM NEWPRODUCTS; 


8、源表為子查詢(自聯接)




[c-sharp] view plaincopyprint?
01.MERGE INTO PRODUCTS P 
02.USING (SELECT COUNT(*) CO FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = 1501) B 
03.ON (B.CO <> 0) 
04.WHEN MATCHED THEN 
05.  UPDATE SET P.PRODUCT_NAME = 'KEBO' WHERE P.PRODUCT_ID = 1501 
06.WHEN NOT MATCHED THEN 
07.  INSERT 
08.    (PRODUCT_ID 
09.    ,PRODUCT_NAME 
10.    ,CATEGORY) 
11.  VALUES 
12.    (1501 
13.    ,'KEBO' 
14.    ,'NBA'); 
15. 
16.MERGE INTO PRODUCTS P 
17.USING (SELECT COUNT(*) CO FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = 1508) B 
18.ON (B.CO <> 0) 
19.WHEN MATCHED THEN 
20.  UPDATE SET P.PRODUCT_NAME = 'KEBO' WHERE P.PRODUCT_ID = 1508 
21.WHEN NOT MATCHED THEN 
22.  INSERT 
23.    (PRODUCT_ID 
24.    ,PRODUCT_NAME 
25.    ,CATEGORY) 
26.  VALUES 
27.    (1508 
28.    ,'KEBO' 
29.    ,'NBA'); 
30. 
31.SELECT * FROM PRODUCTS; 




優點:

—避免了分開更新

—提高性能並易於使用

—在數據倉庫應用中十分有用

—使用merge比傳統的先判斷再選擇插入或更新快很多

需要注意的地方:

1、從語法條件上看(ON (join condition)),merge into也是一個危險的語法。如果不慎重使用,會將源表全部覆蓋到目標表,既危險又浪費效率,違背了增量同步的原則。所以在設計表結構時,一般每條記錄都有「更新時間」的欄位,用目標表「最大更新時間」判斷源表數據是否有更新和新增的信息。

2、更新的欄位,不允許有關聯條件的欄位(join condition)。比如條件是A.ID=B.ID,那麼使用「SET A.ID=B.ID」將報出一個莫名其妙的提示錯誤。



缺少一個帶delete語句選項的示例,暫為補上:



[sql] view plaincopyprint?
01.MERGE INTO PRODUCTS P 
02.USING NEWPRODUCTS NP 
03.ON (P.PRODUCT_ID = NP.PRODUCT_ID) 
04.WHEN MATCHED THEN 
05.  UPDATE 
06.     SET P.PRODUCT_NAME = NP.PRODUCT_NAME, P.CATEGORY = NP.CATEGORY 
07.   --WHERE (P.CATEGORY = 'ELECTRNCS')  
08.  DELETE  
09.   WHERE (P.CATEGORY = 'ELECTRNCS') 
10.WHEN NOT MATCHED THEN 
11.  INSERT 
12.    (PRODUCT_ID, PRODUCT_NAME, CATEGORY) 
13.  VALUES 
14.    (NP.PRODUCT_ID, NP.PRODUCT_NAME, NP.CATEGORY); 
15. 
16.SELECT * FROM PRODUCTS; 
17.SELECT * FROM NEWPRODUCTS; 


說明:DELETE語句刪除的是滿足matched關聯on條件,同時也要是update更新內容的子集,否則不會刪除任何內容。

更多

推薦閱讀文章

Bookmark the permalink ,來源:互聯網