merge into操作的举例

通过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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30109892/viewspace-1816732/