merge into :匹配则更新不匹配则插入
--语法
merge into 目标表
using (增量)
on (匹配字段)
where matched then update set --update和sel直接不需要加表名
when not matched then insert values--insert和values之间不需要加into 表名
例子
create or replace procedure sp_ods_partition_emp_bak(
p_start_time varchar2,
p_end_time varchar2
)
IS
v_start_time varchar2(30) := p_start_time;
v_end_time varchar2(30) := p_end_time;
BEGIN
merge into ods_merge_emp_target t
using (select * from ods_merge_emp)s
on (s.empno=t.empno)
where matched then update set
--t.empno=s.empno,(匹配字段不能更新)
t.ename=s.ename,
t.job=s.job,
t.mgr=s.mgr,
t.sal=s.sal,
t.comm=s.comm,
t.deptno=s.deptno
when not matched then insert values(
s.empno,
s.ename,
s.job,
s.mgr,
s.sal,
s.comm,
s.deptno);
COMMIT;--增删改必须提交代码
end;
create or replace procedure sp_ods_partition_emp_bak(
p_start_time varchar2,
p_end_time varchar2
)
IS
v_start_time varchar2(30) := to_date(p_start_time),'yyyymmdd');
v_end_time varchar2(30) := to_date(p_end_time),'yyyymmdd');
BEGIN
merge into ods_merge_emp_target t
using (select * from ods_merge_emp where create_time=v_start_time)s
on (s.empno=t.empno)
where matched then update set
--t.empno=s.empno,(匹配字段不能更新)
t.ename=s.ename,
t.job=s.job,
t.mgr=s.mgr,
t.sal=s.sal,
t.comm=s.comm,
t.deptno=s.deptno,
t.create_time=s.create_time
when not matched then insert values(
s.empno,
s.ename,
s.job,
s.mgr,
s.sal,
s.comm,
s.deptno
s.creat_time);
COMMIT;--增删改必须提交代码
end;