merge的使用演示

merge的使用演示
1.首先建立目标表score(id,score)
create table score(id varchar(10),score number(3));
向目标中插入数据
insert into score values(1000,88);
insert into score values(1001,78);
insert into score values(1002,50);
insert into score values(1003,48);
2.创建源表 student
create table student(id varchar(10),name varchar(4),score number(3));
insert into student values(1002,'jack',55);
insert into student values(1003,'chen',78);
insert into student values(1004,'www',54);
insert into student values(1005,'peng',95);
insert into student values(1006,'sana',66);
3.执行merge之前查看目标表
select * from score;
SQL> select * from score;

ID                        SCORE
-------------------- ----------
1001                         78
1002                         50
1003                         48

4.执行merge之前查看源表
select * from student;
SQL> select * from student;

ID                   NAME          SCORE
-------------------- -------- ----------
1003                 chen             78
1004                 www              54
1005                 peng             95
1006                 sana             66
5.执行merge操作 
MERGE INTO score s
USING student st --using指定数据源，数据源可以来自一个查询，一个表，也可以来自视图
  on (s.id=st.id) -- 指定一个条件，此条件决定是执行更新操作还是插入操作
WHEN MATCHED THEN --只有满足on的条件才被更新
UPDATE SET
 s.score=st.score
WHEN NOT MATCHED THEN --不满足on的条件的记录才被插入
insert (s.id,s.score) values (st.id,st.score);

SQL> MERGE INTO score s
  2  USING student st
  3    on (s.id=st.id)
  4  WHEN MATCHED THEN
  5  UPDATE SET
  6   s.score=st.score
  7  WHEN NOT MATCHED THEN
  8  insert (s.id,s.score) values (st.id,st.score);

4 行已合并。

6.查看执行merge之后的目标表
SQL> select * from score;

ID                        SCORE
-------------------- ----------
1001                         78
1002                         50
1003                         78
1004                         54
1005                         95
1006                         66

已选择6行。
ID为1003的这条记录被update了，ID为1004,1005,1006被insert到目标里了