ITPub博客

首页 > 数据库 > Oracle > merge into操作的举例

merge into操作的举例

原创 Oracle 作者:bestpaydata 时间:2015-10-27 17:07:19 0 删除 编辑
通过merge你能够在一个SQL语句中对一个表同时执行inserts和updates操作
当然是update还是insert是依据于你的指定的条件判断的

ORACLE 9i中,使用此命令必须同时指定UPDATE和INSERT关键词,ORACLE 10g做了如下改动:
insert和update是可选的
UPDATE和INSERT 后面可以跟WHERE子句
在ON条件中可以使用常量来insert所有的行到目标表中,不需要连接到源表和目标表
UPDATE 子句后面可以跟delete来去除一些不需要的行

这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERT+UPDATE

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;



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
 WHEN NOT MATCHED THEN  
 INSERT VALUES (np.product_id,np.product_name,np.category);    

roll back;

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,p.product_id = np.product_id;
不能更新on关联条件中的字段
ORA-38104: Columns referenced in the ON Clause cannot be updated : "P"."PRODUCT_ID"

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;

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);
insert和update是可选的


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)   
 WHEN MATCHED THEN  
 UPDATE SET p.product_name = np.product_name,p.category = np.category where p.category='DVD' and np.category='TOYS'
  WHEN NOT MATCHED THEN  
 INSERT VALUES (np.product_id,np.product_name,np.category) where np.category!='ELECTRNCS';
UPDATE和INSERT 后面可以跟WHERE子句



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 in ('BOOKS','TOYS');  
在ON条件中可以使用常量来insert所有的行到目标表中,不需要连接到源表和目标表


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 (np.category = 'ELECTRNCS')  
DELETE WHERE (p.category = 'ELECTRNCS')  
WHEN NOT MATCHED THEN  
INSERT VALUES (np.product_id, np.product_name, np.category);
UPDATE 子句后面可以跟delete来去除一些不需要的行


xlj

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30109892/viewspace-1816732/,如需转载,请注明出处,否则将追究法律责任。

下一篇: oracle hints参考
请登录后发表评论 登录
全部评论

注册时间:2015-01-19

  • 博文量
    126
  • 访问量
    985769