剑破冰山笔记之第三章探索Merge方法

anddyhua

于 2021-05-02 22:12:55 发布

阅读量143

点赞数

分类专栏： # Note for SQL&PLSQL

本文链接：https://blog.csdn.net/anddyhua/article/details/116357491

版权

Note for SQL&PLSQL 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

merge的使用

业务上用t1表去更新t2表，匹配的更新，不匹配的插入

	drop table t1;
	create table t1(name varchar2(20),money number);
	insert into t1 values('A',10);
	insert into t1 values('B',10);
	drop table t2;
	create table t2 (name varchar2(20),money number);
	insert into t2 values('A',30);
	insert into t2 values('C',20);
	commit;

下面的t2表被更新，只有A匹配，A将被更新为40，C将插入到t2

	merge into t2
	using t1 on(t1.name=t2.name)
	when matched then
	update set t2.money=t1.money+t2.money--没有分号
	when not matched then
	insert values(t1.name,t1.money);--没有表名


	rollback；
	set autot on
	/

Plan hash value: 2414655244

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |      |     2 |   152 |     5  (20)| 00:00:01 |
|   1 |  MERGE               | T2   |       |       |            |          |
|   2 |   VIEW               |      |       |       |            |          |
|*  3 |    HASH JOIN OUTER   |      |     2 |   124 |     5  (20)| 00:00:01 |  //可以看出是用外连接去匹配
|   4 |     TABLE ACCESS FULL| T1   |     2 |    50 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2   |     3 |   111 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."NAME"="T2"."NAME"(+))

加条件的merge语句的用发比较下面两者的区别，可以看出merge的过程是dml过程

	merge into t2
	using t1 on(t1.name=t2.name)
	when matched then
	update set t2.money=t1.money+t2.money  //没有分号
	when not matched then
	insert values(t1.name,t1.money) where t1.name='A'
	/
	SQL> select * from t2;
	
	NAME                      MONEY
	-------------------- ----------
	A                            40
	C                            20

加条件的merge语句的用法

	merge into t2
	using t1 on(t1.name=t2.name)
	when matched then
	update set t2.money=t1.money+t2.money  //没有分号
	when not matched then
	insert values(t1.name,t1.money) where t1.name='B'
	
	SQL> SELECT * FROM T2;
	
	NAME                      MONEY
	-------------------- ----------
	A                            40
	C                            20
	B                            10

merge+rowid方式

	merge into test using
	（
	with t as
	（select 1 id,(select name from test where id=2) name from dual
	union
	 select 2,(select name from test where id=1) name from dual
	）
	select test.id,test.rowid as rn ,t.name from test,t
	where test.id=t.id）n
	on(test.rowid=n.rn)
	when matched then
	update set test.name=n.name