当前位置:  数据库>oracle

Oracle中merge into的使用

    来源: 互联网  发布时间:2017-04-18

    本文导语: 该命令使用一条语句从一个或者多个数据源中完成对表的更新和插入数据. Oracle 9i 中,使用此命令必须同时指定UPDATE 和INSERT 关键词,ORACLE 10g 做了如下改动。  1,insert 和update是可选的 2,UPDATE 和INSERT 后面可以跟WHERE 子句 3,在O...

该命令使用一条语句从一个或者多个数据源中完成对表的更新和插入数据. Oracle 9i 中,使用此命令必须同时指定UPDATE 和INSERT 关键词,ORACLE 10g 做了如下改动。
  1,insert 和update是可选的 2,UPDATE 和INSERT 后面可以跟WHERE 子句 3,在ON条件中可以使用常量来insert 所有的行到目标表中,不需要连接到源表和目标表 4,UPDATE 子句后面可以跟delete 来去除一些不需要的行。
举例:
create table PRODUCTS    
     (     
  PRODUCT_IDINTEGER,    
  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_IDINTEGER,    
  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;  
1,可省略的update 或者insert 
    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,    
  7 p.category = np.category;
  使用表newproducts中的product_name 和category字段来更新表products 中相同product_id的product_name 和category.
2,当条件不满足的时候把newproducts表中的数据INSERT 到表products中。 
MERGE INTO products p     
  USING newproducts np
      ON (p.product_id = np.product_id)    
      WHEN NOT MATCHED THEN   
      INSERT 
     VALUES

(np.product_id, np.product_name,    
  np.category);
3,带条件的insert 和update 
  insert 和update 都带有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 
     VALUES

(np.product_id, np.product_name, np.category)    
     WHERE np.category != 'BOOKS'   
  4,无条件的insert
MERGE INTO products p    
  USING newproducts np
   ON (1=0)  
  WHEN NOT MATCHED THEN   
    INSERT 
   VALUES

(np.product_id, np.product_name, np.category)    
    WHERE np.category = 'BOOKS'  
   5,delete 子句 
  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
  7  delete where category = 'macle1_cate';
  PRODUCT_ID PRODUCT_NAME         CATEGORY
  --------------------------------------- -------------------- --------------------
  1502 macle22              macle2_cate
  1503 macle3                macle2_cate
  1504 macle                  macle1_cate
  1505 macle5                macle5_cate
  1504 中的macle1_cate 满足delete where,但是不满足 on 中的条件,所以没有被删除。!!!!!!重点
  -----------------------------------------------
动机:
  想在Oracle中用一条SQL语句直接进行Insert/Update的操作。
说明:
  在进行SQL语句编写时,我们经常会遇到大量的同时进行Insert/Update的语句 ,也就是说当存在记录时,就更新(Update),不存在数据时,就插入(Insert)。
实战:
  接下来我们有一个任务,有一个表T,有两个字段a,b,我们想在表T中做Insert/Update,如果存在,则更新T中b的值,如果不存在,则插入一条记录。在Microsoft的SQL语法中,很简单的一句判断就可以了,SQL Server中的语法如下:
  if exists(select 1 from T where T.a='1001' ) update T set T.b=2 Where T.a='1001' else insert into T(a,b) values('1001',2);
  以上语句表明当T表中如果存在a='1001' 的记录的话,就把b的值设为2,否则就Insert一条a='100',b=2的记录到T中。
  但是接下来在Oracle中就遇到麻烦了,记得在Oracle 9i之后就有一条Merge into 的语句可以同时进行Insert 和Update的吗,Merge的语法如下:
MERGE INTO table_name alias1 
  USING (table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
     UPDATE table_name 
     SET col1 = col_val1, 
         col2     = col2_val 
WHEN NOT MATCHED THEN 
     INSERT (column_list) VALUES (column_values);  
上面的语法大家应该都容易懂吧,那我们按照以上的逻辑再写一次。
MERGE INTO T T1
  USING (SELECT a,b FROM T WHERE t.a='1001') T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
   UPDATE SET T1.b =  2 
WHEN NOT MATCHED THEN 
   INSERT (a,b) VALUES('1001',2); 
  以上的语句貌似很对是吧,实际上,该语句只能进行更新,而无法进行Insert,错误在哪里呢?
  其实在Oracle中Merge语句原先是用来进行整表的更新用的,也就是ETL工具比较常用的语法,重点是在Using上。
  用中文来解释Merge语法,就是:
  在alias2中Select出来的数据,每一条都跟alias1进行 ON (join condition)的比较,如果匹配,就进行更新的操作(Update),如果不匹配,就进行插入操作(Insert)。
因此,严格意义上讲,”在一个同时存在Insert和Update语法的Merge语句中,总共Insert/Update的记录数,就是Using语句中alias2的记录数。”
  以上这句话也就很好的解释了在上面写的语句为何只能进行Update,而不能进行Insert了,因为都Select不到数据,如何能进行Insert呢:)
  接下来要改成正确的语句就容易多了,如下:
MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
   UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN 
   INSERT (a,b) VALUES(T2.a,T2.b); 
查询结果,OK!
注意:
  如果不懂Merge语句的原理,Merge语句是一条比较危险的语句,特别是在您只想更新一条记录的时候,因为不经意间,你可能就把整表的数据都Update了一遍.....汗!!!
  我曾经犯过的一个错误如下所示,大家看出来是什么问题了吗?
MERGE INTO T T1
  USING (SELECT Count(*) cnt FROM T WHERE T.a='1001') T2
ON (T2.cnt>0)
WHEN MATCHED THEN
   UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN 
   INSERT (a,b) VALUES(T2.a,T2.b);






















































































































    
 
 

您可能感兴趣的文章:

  • linux下安装oracle后使用命令行启动的方法 linux启动oracle
  • Oracle9i 9.0.1的JDBC Drivers 可以给Oracle 8.0.5使用马?
  • Suse linux使用oracle问题
  • 请问:在使用oracle数据库作开发时,是使用pro*c作开发好些,还是使用库函数如oci等好一些啊?或者它们有什么区别或者优缺点啊?
  • jbuilder中使用oracle的问题
  • 求redhat linux 9.0下可以使用的oracle 10g或9i,还有redhat linux 9.0下可以使用的eclipse下载地址
  • Oracle事务!使用游标提交过程
  • 在Jbuilder7下,使用DbPilot.exe连接oracle,报错!请教高手!
  • AIX 64位系统上如何使用32位OCI oracle driver
  • Oracle中SQL语句连接字符串的符号使用介绍
  • 使用X manager连接oracle数据库的步骤
  • C++使用OCCI连Oracle10g的错误
  • 使用工具 plsqldev将Excel导入Oracle数据库
  • Linux下Oracle的sqlplus使用光标上下左右方法
  • Oracle 数据 使用游标
  • oracle使用sql脚本生成csv文件案例学习
  • ORACLE 修改表结构 之ALTER CONSTAINTS的使用
  • Linux中Oracle使用相关知识集锦
  • Oracle密码文件的使用和维护第1/3页
  • Oracle 触发器的使用小结
  • Oracle merge into用法及例子
  • Oracle MERGE INTO的用法示例介绍
  •  
    本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动!也就是让oracle那个服务自动启动,不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同?
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍
  • SCO unix下安装oracle,但没有光盘,请大家推荐一个oracle下载站点(unix版本的)。谢谢!!!!
  • oracle中如何把表中具有相同值列的多行数据合并成一行
  • 请问大家用oracle数据库, 用import oracle.*;下的东西么? 还是用标准库?
  • Oracle 数据库(oracle Database)性能调优技术详解
  • Linux /$ORACLE_HOME $ORACLE_HOME
  • ORACLE日期相关操作
  • Linux系统下Oracle的启动与Oracle监听的启动
  • ORACLE数据库常用字段数据类型介绍
  • 请问在solaris下安装ORACLE,用root用户和用oracle用户安装有什么区别么?
  • Oracle 12c的九大最新技术特性介绍
  • 网间Oracle的连接,远程连接Oracle服务器??


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3