使用merge into时,when matched后面是不是不能跟and条件啊?
我在使用类似下列语法是总是报错
请高手指教merge into table1 a using( select name,age,sex from table2 )b on (a.age = b.age)
when matched and b.age=20
then ...
when matched and b.name='张三' and not ( a.age=b.age )
then ...
when not matched
then insert ...
我在使用类似下列语法是总是报错
请高手指教merge into table1 a using( select name,age,sex from table2 )b on (a.age = b.age)
when matched and b.age=20
then ...
when matched and b.name='张三' and not ( a.age=b.age )
then ...
when not matched
then insert ...
when matched
then
update ...
where b.age=20
when matched and b.name='张三' and not ( a.age=b.age )
then ...
when not matched
then insert ...
when matched and b.age=20
then ...
when matched and b.name='张三' and not ( a.age=b.age )
then ...
when not matched
then insert ...
-- 很多事情,自己要先把逻辑想好,想好了,写代码就只是技术问题啦!
-- 自己都不知道自己要做什么,就来问问题,就更别说能得到别人的正确答案啦!-- 给个merge into 的例子给你吧:
/*************************************
* 添加好友
*************************************/
procedure add_friends(
v_mobile in varchar2,
v_list in varchar2) as
begin
-- v_list 传入的格式: 手机号:名称\n手机号:名称\n手机号:名称\n....
if v_mobile is not null and lengthb(v_mobile)<=20 then
insert into add_friends_mem(mobile, frendmobile, frendname)
with a as (
select substr(chr(10)||v_list||chr(10),instr(chr(10)||v_list||chr(10),chr(10),1,level)+1,
instr(chr(10)||v_list||chr(10),chr(10),1,level+1)-instr(chr(10)||v_list||chr(10),chr(10),1,level)-1)||':' frendmobile
from dual
connect by
level <= length(chr(10)||v_list||chr(10))-length(replace(chr(10)||v_list||chr(10),chr(10),''))-1),
b as (select substr(a.frendmobile,1,instr(a.frendmobile,':',1,1)-1) as frendmobile,
substr(a.frendmobile,instr(a.frendmobile,':',1,1)+1,
instr(a.frendmobile,':',1,2)-instr(a.frendmobile,':',1,1)-1) as frendname
from a )
select trim(v_mobile), trim(b.frendmobile), trim(b.frendname) frendname -- 考虑手机号前后带有空格的情况
from b
where lengthb(trim(b.frendmobile))<=20 and (lengthb(b.frendname)<=100 or b.frendname is null)
and b.frendmobile is not null
and trim(v_mobile) is not null;
-- and b.frendmobile<>trim(v_mobile); -- 不能将自己添加为自己的好友 -- 去除重复数据:
delete from add_friends_mem a1
where rowid not in (select max(rowid) from add_friends_mem a2 group by a2.frendmobile); merge into MobileFrends a
using add_friends_mem b on (a.mobile=b.mobile and a.frendmobile=b.frendmobile)
when matched then
update set a.frendname=nvl(b.frendname,a.frendname),a.udate=decode(b.frendname,null,a.udate,sysdate)
where a.frendname<>b.frendname or a.frendname is null
when not matched then
insert (mobile,frendmobile,cdate,udate,frendname)
values (b.mobile,b.frendmobile,sysdate,sysdate,b.frendname);
commit;
end if;
exception when others then
rollback;
end;
-- 去判断原表的数据,
-- 如果在原表中找到相关数据与预处理的数据条件相符(这个条件,你自己的业务去定义),
-- 则更新;否则插入!
我是要根据两个符合的条件(两个when matched)分别做update,最后才做insert
merge into table1 a using( select name,age,sex from table2 )b on (a.age = b.age)
when matched and b.age=20
then update set sex='1'
when matched and b.name='张三' and not ( a.age=b.age )
then update set sex='2'
when not matched
then insert ...上面这个写法是从db2上得到的
我想在oracle上实现
但如果直接用这写法,oracle会报错“缺少关键字”
我现在是想知道,那我该如何修改上面这个sql的写法才正确