同步增删改存储过程

当表B更新或者新增时调用该存储过程，同步至表A

create or replace procedure sync(p_ID in NUMBER) is

begin
  MERGE INTO A t1
USING (select ID,NAME from B WHERE ID=p_ID) t2
ON (t1.ID=t2.ID)
WHEN MATCHED THEN
    UPDATE
    SET t1.NAME = t2.NAME
   
WHEN NOT MATCHED THEN
    INSERT (ID,NAME) VALUES (t2.ID,t2.NAME);
             commit;

end sync;

当表B删除记录时，先记录下ID，并同步删除A里的记录

create or replace procedure deleted(p_ID in NUMBER) is
begin
  MERGE INTO ZJ_JC_FXGLML t1
USING (select id from ZJ_JC_FXGLML WHERE ID=p_ID) t2
ON (t1.id=t2.id)
WHEN MATCHED THEN
    UPDATE
    SET t1.name = ''//delete只能在update之后调用，且删除范围在update的数据之内
    delete where name is null;
             commit;
end deleted;

mybatis执行存储过程

<!-- 调用存储过程同步 -->
    <update id="sync" statementType="CALLABLE">
    <![CDATA[
    {call sync(#{id,mode=IN})}
]]>
    </update>
    
    <!-- 调用存储过程删除同步 -->
    <delete id="deleted" statementType="CALLABLE">
    <![CDATA[
    {call deleted(#{id,mode=IN})}
]]>
    </delete>