Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1355391
  • 博文数量: 239
  • 博客积分: 5909
  • 博客等级: 大校
  • 技术积分: 2715
  • 用 户 组: 普通用户
  • 注册时间: 2010-07-24 20:19
文章分类

全部博文(239)

文章存档

2014年(4)

2013年(22)

2012年(140)

2011年(14)

2010年(59)

我的朋友

分类: Oracle

2013-05-17 15:06:18

Oracle9i引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行insertsupdates操作. MERGE命令从一个或多个数据源中选择行来updatinginserting到一个或多个表.Oracle10gMERGE有如下一些改进

1UPDATEINSERT子句是可选的

2UPDATEINSERT子句可以加WHERE子句

3、在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表

4UPDATE子句后面可以跟DELETE子句来去除一些不需要的行

语法:

MERGE  INTO SCHEMA .TABLE T_ALIAS

USING SCHEMA . {TABLE | VIEW |SUBQUERY } T_ALIAS

ON (CONDITION)

WHEN MATCHED THEN MERGE_UPDATE_CLAUSE

WHEN NOT MATCHED THEN MERGE_INSERT_CLAUSE / MERGE_DELETE_CLAUSE;

联想:
merge
into是特有的功能,相当于在 MSSQL中的

ifexists(...)

updatetable

else

Insertinto table.

mergeinto语法不仅没有if exists语法啰嗦,而且比if exists还要高效很多,常用来在oracle之间同步数据库表。

例子:

点击(此处)折叠或打开

  1. 1、创建测试表及数据

  2.     DROP TABLE PRODUCTS;
  3.     DROP TABLE NEWPRODUCTS;
  4.     create table PRODUCTS
  5.     (
  6.     PRODUCT_ID INTEGER,
  7.     PRODUCT_NAME VARCHAR2(60),
  8.     CATEGORY VARCHAR2(60)
  9.     );
  10.     insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
  11.     insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
  12.     insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
  13.     insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');
  14.     insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
  15.     commit;
  16.     create table NEWPRODUCTS
  17.     (
  18.     PRODUCT_ID INTEGER,
  19.     PRODUCT_NAME VARCHAR2(60),
  20.     CATEGORY VARCHAR2(60)
  21.     );
  22.     insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
  23.     insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
  24.     insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
  25.     insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
  26.     commit;

  27. 2、匹配更新


  28.     MERGE INTO PRODUCTS P
  29.     USING NEWPRODUCTS NP
  30.     ON (P.PRODUCT_ID = NP.PRODUCT_ID)
  31.     WHEN MATCHED THEN
  32.       UPDATE
  33.          SET P.PRODUCT_NAME = NP.PRODUCT_NAME,
  34.              P.CATEGORY = NP.CATEGORY;
  35.       
  36.     SELECT * FROM PRODUCTS;
  37.     SELECT * FROM NEWPRODUCTS;

  38. 3、不匹配插入


  39.     MERGE INTO PRODUCTS P
  40.     USING NEWPRODUCTS NP
  41.     ON (P.PRODUCT_ID = NP.PRODUCT_ID)
  42.     WHEN NOT MATCHED THEN
  43.       INSERT
  44.         (PRODUCT_ID
  45.         ,PRODUCT_NAME
  46.         ,CATEGORY)
  47.       VALUES
  48.         (NP.PRODUCT_ID
  49.         ,NP.PRODUCT_NAME
  50.         ,NP.CATEGORY);
  51.       
  52.     SELECT * FROM PRODUCTS;
  53.     SELECT * FROM NEWPRODUCTS;

  54. 4、匹配带where/on更新

  55.     MERGE INTO PRODUCTS P
  56.     USING NEWPRODUCTS NP
  57.     ON (P.PRODUCT_ID = NP.PRODUCT_ID)
  58.     WHEN MATCHED THEN
  59.       UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME WHERE P.CATEGORY = NP.CATEGORY;
  60.       
  61.     MERGE INTO PRODUCTS P
  62.     USING NEWPRODUCTS NP
  63.     ON (P.PRODUCT_ID = NP.PRODUCT_ID AND P.CATEGORY = NP.CATEGORY)
  64.     WHEN MATCHED THEN
  65.       UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME;
  66.       
  67.     SELECT * FROM PRODUCTS;
  68.     SELECT * FROM NEWPRODUCTS;
  69.       
  70.     SELECT *
  71.       FROM PRODUCTS A
  72.      INNER JOIN NEWPRODUCTS B
  73.         ON A.PRODUCT_ID = B.PRODUCT_ID
  74.        AND A.CATEGORY = B.CATEGORY;

  75. 5、匹配带where更新、插入

  76.     MERGE INTO PRODUCTS P
  77.     USING NEWPRODUCTS NP
  78.     ON (P.PRODUCT_ID = NP.PRODUCT_ID)
  79.     WHEN MATCHED THEN
  80.       UPDATE
  81.          SET P.PRODUCT_NAME = NP.PRODUCT_NAME,
  82.              P.CATEGORY = NP.CATEGORY
  83.        WHERE P.CATEGORY = 'DVD'
  84.     WHEN NOT MATCHED THEN
  85.       INSERT
  86.         (PRODUCT_ID
  87.         ,PRODUCT_NAME
  88.         ,CATEGORY)
  89.       VALUES
  90.         (NP.PRODUCT_ID
  91.         ,NP.PRODUCT_NAME
  92.         ,NP.CATEGORY) WHERE NP.CATEGORY != 'BOOKS';
  93.       
  94.     SELECT * FROM PRODUCTS;
  95.     SELECT * FROM NEWPRODUCTS;

  96. 6、ON常量表达式

  97.     MERGE INTO PRODUCTS P
  98.     USING NEWPRODUCTS NP
  99.     ON (1 = 0)
  100.     WHEN NOT MATCHED THEN
  101.       INSERT
  102.         (PRODUCT_ID
  103.         ,PRODUCT_NAME
  104.         ,CATEGORY)
  105.       VALUES
  106.         (NP.PRODUCT_ID
  107.         ,NP.PRODUCT_NAME
  108.         ,NP.CATEGORY) WHERE NP.CATEGORY = 'BOOKS';
  109.       
  110.     SELECT * FROM PRODUCTS;
  111.     SELECT * FROM NEWPRODUCTS;

  112. 7、匹配删除、不匹配插入

  113.     MERGE INTO PRODUCTS P
  114.     USING NEWPRODUCTS NP
  115.     ON (P.PRODUCT_ID = NP.PRODUCT_ID)
  116.     WHEN MATCHED THEN
  117.       UPDATE
  118.          SET P.PRODUCT_NAME = NP.PRODUCT_NAME,
  119.              P.CATEGORY = NP.CATEGORY DELETE
  120.        WHERE (P.CATEGORY = 'ELECTRNCS')
  121.     WHEN NOT MATCHED THEN
  122.       INSERT
  123.         (PRODUCT_ID
  124.         ,PRODUCT_NAME
  125.         ,CATEGORY)
  126.       VALUES
  127.         (NP.PRODUCT_ID
  128.         ,NP.PRODUCT_NAME
  129.         ,NP.CATEGORY);
  130.       
  131.     SELECT * FROM PRODUCTS;
  132.     SELECT * FROM NEWPRODUCTS;

  133. 8、源表为子查询(自联接)

  134.     MERGE INTO PRODUCTS P
  135.     USING (SELECT COUNT(*) CO FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = 1501) B
  136.     ON (B.CO <> 0)
  137.     WHEN MATCHED THEN
  138.       UPDATE SET P.PRODUCT_NAME = 'KEBO' WHERE P.PRODUCT_ID = 1501
  139.     WHEN NOT MATCHED THEN
  140.       INSERT
  141.         (PRODUCT_ID
  142.         ,PRODUCT_NAME
  143.         ,CATEGORY)
  144.       VALUES
  145.         (1501
  146.         ,'KEBO'
  147.         ,'NBA');
  148.       
  149.     MERGE INTO PRODUCTS P
  150.     USING (SELECT COUNT(*) CO FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = 1508) B
  151.     ON (B.CO <> 0)
  152.     WHEN MATCHED THEN
  153.       UPDATE SET P.PRODUCT_NAME = 'KEBO' WHERE P.PRODUCT_ID = 1508
  154.     WHEN NOT MATCHED THEN
  155.       INSERT
  156.         (PRODUCT_ID
  157.         ,PRODUCT_NAME
  158.         ,CATEGORY)
  159.       VALUES
  160.         (1508
  161.         ,'KEBO'
  162.         ,'NBA');
  163.       
  164.     SELECT * FROM PRODUCTS;

9.带delete的merge

  1. 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, P.CATEGORY = NP.CATEGORY  
  7.    --WHERE (P.CATEGORY = 'ELECTRNCS')   
  8.   DELETE   
  9.    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; 

优点:

 避免了分开更新

 提高性能并易于使用

 在数据仓库应用中十分有用

 使用merge比传统的先判断再选择插入或更新快很多

需要注意的地方:

1、从语法条件上看(ON (join condition)),merge into也是一个危险的语法。如果不慎重使用,会将源表全部覆盖到目标表,既危险又浪费效率,违背了增量同步的原则。所以在设计表结构时,一般每条记录都有更新时间的字段,用目标表最大更新时间判断源表数据是否有更新和新增的信息。

2、更新的字段,不允许有关联条件的字段(join condition)。比如条件是 A.ID=B.ID,那么使用SET A.ID=B.ID”将报出一个莫名其妙的提示错误。



转自: http://blog.csdn.net/nsj820/article/details/5755685
阅读(641) | 评论(0) | 转发(0) |
0

上一篇:Linux中的SSH

下一篇:Oracle中的正则表达式

给主人留下些什么吧!~~