赞
踩
-- 如果不知道 merge into 这个语法,咱可能会这么写
select count(1)
into v_count
from ...;
if count(1) >= 1 then
update ...; -- 有则更新
else
insert ...; -- 无则插入
-- 源表:source_table 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; -- 目标表:target_table(仅表结构) create table target_table as select * from source_table where 1 = 2; insert into target_table (sno, sname, sex) values(1, '瑶瑶', '男'); commit;
merge into 目标表 b
using 源表 a
on (b.字段1 = a.字段1 and b.字段n = a.字段n) -- 必须带 '()'
when matched then -- 整体扫描,匹配时,执行此处
update 子句
when not matched then -- 整体扫描,不匹配时,执行此处
insert 子句;
commit; -- 记得提交哦
实例: 将源表 source_table 的数据同步至目标表 target_table
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; -- 记得提交哦
查询结果:
select * from source_table t order by t.sno;
select * from target_table t order by t.sno;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。