动机:
想在Oracle中用一条SQL语句直接进行Insert/Update的操作。
说明:
在进行SQL语句编写时,我们经常会遇到大量的同时进行Insert/Update的语句 ,也就是说当存在记录时,就更新(Update),不存在数据时,就插入(Insert)。
Oracle9i引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行inserts和updates操作.
MERGE命令从一个或多个数据源中选择行来updating或inserting到一个或多个表.在Oracle10g中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、 创建测试表及数据
- 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”将报出一个莫名其妙的提示错误。
http://www.cnblogs.com/highriver/archive/2011/08/02/2125043.html
分享到:
相关推荐
本书是专门为oracle开发人员而提供的编程指南 通过学习本书 读者不仅可以掌握编写sql语句和pl/sql块的基础知识 而且还可以掌握sql高级特征 正则表达式 flashback查询 merge语句 sql:1999连接 和pl/sql高级特征 ...
在研究一些被其他专门讨论Oracle SQL语言的参考书直接忽略的问题时,这种对Oracle数据库的长期钻研无疑是一个巨大的优势。 ——亚马逊读者评论 目录 第1章 SQL核心 1 1.1 SQL语言 1 1.2 数据库的接口 2 1.3 SQL*...
Karen Morton及其团队在本书中提供了专业的方案:先掌握语言特性,再学习Oracle为提升语言效率而加入的支持特性,进而将两者综合考虑并在工作中加以应用。作者通过总结各自多年的软件开发和教学培训经验,与大家...
通过学习《精通Oracle 10g SQL和PL/SQL》,读者不仅可以掌握SQL和PL/SQL的基础知识,而且还可以掌握SQL高级特征(正则表达式、Flashback查询、MERGE语句、SQL:1999连接)和PL/SQL高级特征(记录类型、集合类型、对象...
4.2 使用Oracle共享服务器体系结构 128 4.2.1 专用服务器体系结构的局限性 129 4.2.2 共享服务器体系结构 130 4.2.3 配置共享服务器 132 4.2.4 使用共享服务器的场合 132 4.3 本章知识点回顾 134 4.4 自测题 ...
达梦数据库sql语言手册,达梦数据库是国产数据库里最好使用的,可以跟oracle数据无缝替换的
oracle认证教程,从入门到高级,• 数据查询:select • DML(data manipulation language): insert、update、delete、merge • DDL(data definition language): create、alter、drop、rename、truncate • 事务控制...
4.2 使用Oracle共享服务器体系结构 128 4.2.1 专用服务器体系结构的局限性 129 4.2.2 共享服务器体系结构 130 4.2.3 配置共享服务器 132 4.2.4 使用共享服务器的场合 132 4.3 本章知识点回顾 134 4.4 自测题 ...
说明: 指定 Oracle 使用哪种日历系统作为日期格式。例如, 如果 NLS_CALENDAR 设置为 'Japanese Imperial', 那么日期格式为 'E YY-MM-DD'。即: 如果日期是 1997 年 5 月 15 日, 那么 SYSDATE 显示为 'H 09-05-15'。 ...
一种不是很中肯的说法是,mysql能支持千万级别的数据,oracle能支持上亿级别的数据,超过这个级别,可能会出现性能瓶颈。 但实际应用场景中,我们经常会遇到如下情况: 时间积累,表体积变大,业务要求不能迁移历史...
• 数据查询:select ...insert、update、delete、merge • DDL(data definition language): create、alter、drop、rename、truncate • 事务控制:commit、rollback、savepoint • DCL(数据控制语言):grant、revoke
第1章 Oracle服务器技术与关系范例 1.1 定位服务器技术 1.1.1 Oracle服务器的体系结构 1.1.2 OracleApplicationServer 1.1.3 OracleEnterpriseManager 1.1.4 网格计算 1.1.5 开发工具和语言 1.2 理解...
用matlab运行MD5代码utl_hash_merge_of_two_un-sorted_data_sets_with_some_common_variables 两个未排序数据集与一些公共变量的高效哈希合并。 关键词:sas sql join 合并大数据分析宏 oracle teradata mysql sas ...
Oracle,TELUS等在内的领先公司的信任→每天有100,000多个用户享用→超过1,461多个销售团队使用计划和定价14天免费试用ContactMonkey!这包括无限的电子邮件跟踪,延迟发送,邮件合并以及完整的Salesforce电子邮件...
结构化查询语言(Structured Query Language)简称SQL,是一种关系数据库查询语言,用于存取数据以及查询、更新和管理关系数据库系统。 1.2 语句结构 1.2.1 数据查询语言(DQL) 对数据库进行的信息查询,select。 ...
事实上针对单个对象的get/load/persist/save/update/merge/saveOrUpdate API和Criteria API本来就为一体,只不过是历史的原因被人为割裂成为两套数据库操作API罢了。 因此,对于关系型数据库而言——Entity和...