merge into 用法

使用例子

merge into user_role a
using (
    select t1.*,t2.belongorg,to_char(sysdate, 'yyyy/mm/dd hh24:mm:ss') as curtime,'taj' as curuser
    from (select t1.roleid,'add' as operation,'test1' as userid from awe_role_info t1 where t1.roleid in('3107','3109','3450')
        union all
        select t1.roleid,'del' as operation,'test1' as userid from awe_role_info t1 where t1.roleid in('3110','3111','3201','3400')
    ) t1 join user_info t2 on t2.userid=t1.userid
) b on (a.userid=b.userid and a.roleid=b.roleid)
when matched then
    update set a.grantor=a.grantor where b.operation = 'del'
    delete where b.operation = 'del'
when not matched then
    insert(userid,roleid,grantor,begintime,status,inputuser,inputorg,inputtime)
    values(b.userid,b.roleid,b.curuser,b.curtime,'1',b.curuser,b.belongorg,b.curtime)
    where b.operation = 'add';