怎么做一个手机网站,医疗ppt模板下载免费完整版,怎么查网站关键词排名,网店运营具体做什么在Oracle中#xff0c;常规的DML语句只能完成单一功能#xff0c;#xff0c;例如insert/delete/update只能三选一#xff0c;而merge into语句可以同时对一张表进行更新/插入/删除。 目录 一、基本语法 二、用法示例 2.1 同时更新和插入 2.2 where子句 2.3 delete子句 2.4…在Oracle中常规的DML语句只能完成单一功能例如insert/delete/update只能三选一而merge into语句可以同时对一张表进行更新/插入/删除。 目录 一、基本语法 二、用法示例 2.1 同时更新和插入 2.2 where子句 2.3 delete子句 2.4 记录同步错误 一、基本语法 merge into常用在数据同步的场景它会选定一张基表作为数据源然后与目标表的记录进行匹配根据匹配的结果可以对目标表同时进行更新/插入/删除操作。 语法 MERGE INTO target_table
USING source_table ON (join_condition)
WHEN MATCHED THEN UPDATE SET … [WHERE …] [DELETE … WHERE …]
WHEN NOT MATCHED THEN INSERT (column_list) VALUES (value_list) [WHERE …]
LOG ERRORS INTO … REJECT LIMIT [integer|UNLIMITED] target_table 是要更新的表source_table 是参考表USING … ON () 是两个表的连接条件用于判断记录是否匹配WHEN MATCHED 对于满足匹配条件的记录进行的操作可以更新或删除WHEN NOT MATCHED 对于不满足匹配条件的记录可以插入LOG ERRORS INTO 可以将匹配错误的记录记录到日志表中 二、用法示例 先创建2张表一张源表一张目标表 create table src_table(
id number(6),
name varchar2(32),
salary number(6),
bonus number(6));insert into src_table values(1,Vincent,1000,100);
insert into src_table values(2,Victor,2000,200);
insert into src_table values(3,Grace,3000,300);create table tgt_table(
id number(6),
name varchar2(32),
age number(6),
salary number(6),
bonus number(6));insert into tgt_table values(1,someone,1,0,0);
insert into tgt_table values(3,someone,3,0,0);
insert into tgt_table values(4,someone,4,0,0);
commit; 两张表的数据如下 Select * from src_table;
Select * from tgt_table; 2.1 同时更新和插入 下面是最基本的用法利用源表对目标表同时进行更新和插入 merge into tgt_table t
using src_table s on (t.ids.id)
when matched then update set t.names.name, t.salarys.salary, t.age10
when not matched then insert values(s.id,s.name,10,s.salary,s.bonus50);commit;select * from tgt_table; When matched 为匹配存在的记录id为13更新了name, salary, age个字段When not match 为目标表不存在的记录id为2插入了该记录同时bouns字段加50Matched 和 not matched的子句是独立的可以任意选择一项或同时出现目标表中id为4的记录在源表中不存在不满足连接条件因此不会涉及 2.2 where子句 对于匹配的记录可以使用where子句进一步限制范围 merge into tgt_table t
using src_table s on (t.ids.id)
when matched then update set t.bonuss.bonus where s.id2;Select * from tgt_table order by id; 这里对匹配的记录bonus字段进行更新同时进一步限制id2的记录才更新Where 子句的条件可以通过源表或者目标表指定这里是通过源表的s.id指定 2.3 delete子句 在update子句后还可以跟上delete … where …子句对匹配上的记录进行删除操作 merge into tgt_table t
using src_table s on (t.ids.id)
when matched then update set t.bonuss.bonus50 where s.id3 delete where id2;select * from tgt_table order by id; delete where id2 指定将ID大于等于2的记录删除但注意id为2的记录并未被删除只有3被删除了因为delete只会在update匹配的到记录范围内删除update子句有个where s.id3delete也会受到这个条件的限制实际执行的效果是 delete where s.id3 and id2只有id为3的记录满足这个条件id为4的记录不在匹配范围内不受merge into语句的影响也不会被删除 2.4 记录同步错误 数据同步的时源表和目标表的结构/数据类型/约束可能并不一致这就导致数据同步可能部分失败现在我们修改tgt表限制salary字段值不能超过3000即插入超过3000的数字将失败 Alter table tgt_table modify salary number(6) check(salary3000); 首先调用dbms_errlog.create_error_log为tgt_table创建一张错误日志表表名为errlog exec dbms_errlog.create_error_log(tgt_table, errlog); 第一个参数指定要创建错误日志的基表第二个参数是错误日志表名 然后在执行merge into 语句时在最后跟上log errors into子句如果语句执行过程中遇到错误则会将错误记录到错误日志中方便后期排查和修复这里将tgt_table清空尝试将src_table的3条数据同步进去同时salary增加1000 truncate table tgt_table;merge into tgt_table t
using src_table s on (t.ids.id)
when not matched then insert values(s.id,s.name,10,s.salary1000,s.bonus)
log errors into errlog(something is wrong.);select * from tgt_table; 这里将tgt_table表清空然后尝试将src_table中的3条记录同步进来同步过程中我们将salary增加了1000因为tgt_table表salary有约束不能超过3000因此语句回滚一条记录都没同步进来errlog中的something is wrong. 是用户定义的错误标记可以帮助识别是哪个语句导致的错误 查询errlog表可以看到导致失败的原因id为3的记录salary在增加1000后为4000违反了目标表的约束check salary3000 select * from errlog; 如果我们不想让出现错误的时候语句就回滚可以在后面跟上一个reject limit N子句限制只有出现N个以上的错误时才回滚语句 merge into tgt_table t
using src_table s on (t.ids.id)
when not matched then insert values(s.id,s.name,10,s.salary1000,s.bonus)
log errors into errlog(Allow one error.) reject limit 1;select * from tgt_table; 我们在上面的语句后面增加了1个reject limit 1子句当出现1个及以下的错误时并不会回滚因此id为1和2的记录成功插入语句并未报错errlog的用户标记修改为Allow one error. 再次查询errlog select * from errlog; 通过Allow one error标签我们发现ID为3的记录因违反约束没有被插入但这此有reject limit 1子句语句允许出现1个及以下错误因此满足条件的记录被成功插入如果选择reject limit unlimited则不限制错误数量
