9.oracle中merge into使用

merge into的使用方法
一：用途：
/*可以同时从1个或者多个源表对目标表进行更新、插入、删除数据，经常用于操作大量的数据，
即对于大批量的数据更新、插入时效率极高。*/

二、语法：
merge into table_name  alias1   --目标表 可以用别名  merge合并
using (table|view|sub_query) alias2      --数据源表 可以是表、视图、子查询   using--使用,运用
on (join condition [and join condition])  --关联条件()必须加
when matched then        --matched匹配      --当关联条件成立时 更新，删除为可选 where部分为可选 
  --更新
  update set  col1=colvalue  where……
  --删除  
  delete  where……
--可以只更新不删除 不可以只删除不更新。
--如果更新和删除同时存在，删除的条件 一定要在更新的条件内，否则数据不能删除。
when not matched then      --当关联条件不成立时  只能执行插入  
  --插入
  insert (col3) values (col3values)  where…… 
  when not matched  by source then      --当源表不存在，删除目标表存在的数据删除  SOURCE--来源，源
  delete
OUTPUT $action, inserted.*, deleted.*, updated.*; 
OUTPUT $ACTION AS [ACTION],Inserted.id as 插入的id,
    Inserted.[DESC] as 插入的DESC,
    deleted.id as 删除的id,
    deleted.[DESC] as 删除的DESC;
OUTPUT (output_column1, output_column2, ...)
INTO output_table;
  
三、语句讲解：
/*
1、on后面的关联条件成立时，可以update、delete。
2、on后面的关联条件不成立时，可以insert。
3、当源表中不存在数据，而目标表中存在的数据可以删除。
4.$action 子句：返回执行的操作类型（例如，UPDATE、INSERT、DELETE）。您可以在 $action 子句中使用 WHEN 子句来进一步限制要返回的操作类型。
  INSERTED 子句：仅当执行了插入操作时才返回。它包含新插入的行的所有列的值。
  DELETED 子句：仅当执行了删除操作时才返回。它包含已删除的行的所有列的值。
  UPDATED 子句：仅当执行了更新操作时才返回。它包含更新前的行和更新后的行的所有列的值
*/
四、注意事项：
/*
1、只会操作“操作表”，源表不会有任何变化。
2、不一定要把update，delete，insert 操作都写全，可以根据实际情况。
3、merge into效率很+高，强烈建议使用，尤其是在一次性提交事务中，可以先建一个临时表，更新完后，清空数据,这样update锁表的几率很小了。
4、Merge语句还有一个强大的功能是通过OUTPUT子句，可以将刚刚做过变动的数据进行输出。我们在上面的Merge语句后加入OUTPUT子句。
5、可以使用TOP关键字限制目标表被操作的行，如图8所示。在图2的语句基础上加上了TOP关键字，我们看到只有两行被更新。
6,delete操作一定要有update语句
7.更新操作的执行顺序优先于插入操作
8.DELETE语句删除的是满足matched关联on条件，同时也要是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 -- WHERE P.PRODUCT_ID=1502
DELETE WHERE 1=1 --P.PRODUCT_ID=1502
WHEN NOT MATCHED THEN 
INSERT (P.PRODUCT_ID,P.PRODUCT_NAME,P.CATEGORY)
VALUES(NP.PRODUCT_ID,NP.PRODUCT_NAME,NP.CATEGORY);
WHEN NOT MATCHED BY SOURCE THEN 
DELETE where 1<>1
OUTPUT $ACTION,INSERTED.*,UPDATED.*,INSERTED.*;


ROLLBACK;
SELECT * FROM PRODUCTS;
select * from newproducts;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
带有相关条件的更新删除操作；
--update
update 目标表 set 目标表.列=(sql查询源表语句) where 更新条件--条件常用跟 exists

update PRODUCTS A SET A.PRODUCT_NAME=(SELECT B.PRODUCT_NAME FROM newproducts B WHERE A.PRODUCT_NAME=B.PRODUCT_NAME);--可能出现更新空值

update PRODUCTS A SET A.PRODUCT_NAME=(SELECT B.PRODUCT_NAME FROM newproducts B WHERE A.PRODUCT_NAME=B.PRODUCT_NAME)
WHERE EXISTS (SELECT B.PRODUCT_NAME FROM newproducts B WHERE A.PRODUCT_NAME=B.PRODUCT_NAME);--避免出现关联不到更新空值

update PRODUCTS A SET (A.PRODUCT_NAME,A.CATEGORY)=
(SELECT B.PRODUCT_NAME,B.CATEGORY FROM newproducts B WHERE A.PRODUCT_NAME=B.PRODUCT_NAME);

--delete
DELETE from (select * from emp where deptno=10);
delete from emp where deptno=10;
delete from PRODUCTS a where exists (select 1 from newproducts b where b.product_id=a.product_id);