Oracle merge into 用法详解

2021-06-29 17:15:56

<div id="article_content" class="article_content clearfix">
<link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/ck_htmledit_views-1a85854398.css">
<div id="content_views" class="markdown_views prism-atom-one-dark">
<svg xmlns="http://www.w3.org/2000/svg" style="display: none;">
<path stroke-linecap="round" d="M5,0 0,2.5 5,5z" id="raphael-marker-block" style="-webkit-tap-highlight-color: rgba(0, 0, 0, 0);"></path>
</svg>

<div class="toc">
<h3><a name="t0"></a>文章目录</h3>
<ul><li><a href="#1__2" target="_self">1 概述</a></li><li><a href="#2__38" target="_self">2 语法</a></li></ul>
</div>

<h1><a name="t1"></a><a id="1__2"></a>1 概述</h1>
<pre class="prettyprint"><code class="prism language-sql has-numbering" οnclick="mdcp.copyCode(event)" style="position: unset;">1. 适用场景：'有则更新，无则插入'

2. 好处
(1) 执行 '效率高'
(2) 语法简洁

3. 如果不知道 merge into 这个语法，咱可能会这么写
select count(1)
into v_count
from ...;

if count(1) >= 1 then
update ...; -- 有则更新
else
insert ...; -- 无则插入
<div class="hljs-button {2}" data-title="复制" data-report-click="{"spm":"1001.2101.3001.4259"}"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li><li style="color: rgb(153, 153, 153);">6</li><li style="color: rgb(153, 153, 153);">7</li><li style="color: rgb(153, 153, 153);">8</li><li style="color: rgb(153, 153, 153);">9</li><li style="color: rgb(153, 153, 153);">10</li><li style="color: rgb(153, 153, 153);">11</li><li style="color: rgb(153, 153, 153);">12</li><li style="color: rgb(153, 153, 153);">13</li><li style="color: rgb(153, 153, 153);">14</li><li style="color: rgb(153, 153, 153);">15</li></ul></pre>
基础数据准备：
<pre class="prettyprint"><code class="prism language-sql has-numbering" οnclick="mdcp.copyCode(event)" style="position: unset;">create table source_table (
sno number(3),
sname varchar2(30),
sex varchar2(2)
);

insert into source_table(sno, sname, sex) values(1, '瑶瑶', '女');
insert into source_table(sno, sname, sex) values(2, '优优', '男');
insert into source_table(sno, sname, sex) values(3, '倩倩', '女');
commit;

-- 目标表（表结构）
create table target_table as select * from source_table where 1 = 2;
<div class="hljs-button {2}" data-title="复制" data-report-click="{"spm":"1001.2101.3001.4259"}"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li><li style="color: rgb(153, 153, 153);">6</li><li style="color: rgb(153, 153, 153);">7</li><li style="color: rgb(153, 153, 153);">8</li><li style="color: rgb(153, 153, 153);">9</li><li style="color: rgb(153, 153, 153);">10</li><li style="color: rgb(153, 153, 153);">11</li><li style="color: rgb(153, 153, 153);">12</li><li style="color: rgb(153, 153, 153);">13</li></ul></pre>
<h1><a name="t2"></a><a id="2__38"></a>2 语法</h1>
<pre class="prettyprint"><code class="prism language-sql has-numbering" οnclick="mdcp.copyCode(event)" style="position: unset;">merge into 目标表 b
using 源表 a
on (b.字段1 = a.字段1 and b.字段n = a.字段n) -- 必须带 '()'
when matched then -- 整体扫描，匹配时，执行此处
update 子句
when not matched then -- 整体扫描，不匹配时，执行此处
insert 子句;
<div class="hljs-button {2}" data-title="复制" data-report-click="{"spm":"1001.2101.3001.4259"}"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li><li style="color: rgb(153, 153, 153);">6</li><li style="color: rgb(153, 153, 153);">7</li></ul></pre>
实例：将源表 source_table 的数据同步至目标表 target_table
<pre class="prettyprint"><code class="prism language-sql has-numbering" οnclick="mdcp.copyCode(event)" style="position: unset;">merge into target_table b
using source_table a
on (b.sno = a.sno)
when matched then
update set b.sname = a.sname, b.sex = a.sex
when not matched then
insert (b.sno, b.sname, b.sex) values (a.sno, a.sname, a.sex);

-- commit; -- 记得提交哦
<div class="hljs-button {2}" data-title="复制" data-report-click="{"spm":"1001.2101.3001.4259"}"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li><li style="color: rgb(153, 153, 153);">6</li><li style="color: rgb(153, 153, 153);">7</li><li style="color: rgb(153, 153, 153);">8</li><li style="color: rgb(153, 153, 153);">9</li></ul></pre>
查询结果： <img src="https://www.icode9.com/i/ll/?i=20201113213058442.png#pic_left" alt="在这里插入图片描述">
提示：咱也可以改变源表 source_table 的记录，测试同步后目标表 target_table 的变化哦
<pre class="prettyprint"><code class="prism language-sql has-numbering" οnclick="mdcp.copyCode(event)" style="position: unset;">update source_table t set t.sname = 'aa' where t.sno = 1;
commit;
<div class="hljs-button {2}" data-title="复制" data-report-click="{"spm":"1001.2101.3001.4259"}"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li></ul></pre>
</div><div><div></div></div>
<link href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/markdown_views-d7a94ec6ab.css" rel="stylesheet">
<link href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/style-49037e4d27.css" rel="stylesheet">
</div>

码农公寓

相关文章