oracle存储过程实现两表对比更新、插入、删除

Question

求存储过程写法，想实现一个存储过程中两表对比，表结构一样，根据B表更新A表，比如如果人员编码，编码为唯一不重复值，AB表都有则根据B表更新A表，如果人员编码A表没有B表有则根据B表插入A表，如果人员编码A表有B表没有则删除A表的数据

Answer 1

直接把A表TRUNCATE，把B表数据插入A表不就行了？费那么大劲干嘛？

Answer 2

提供个思路：如果b表的所有字段等于a表的所有字段，则不执行任何操作；如果b表编码等于a表编码但是b表任一字段不等于a表对应字段，则删除a表该行，插入b表该行；如果a表编码有b表编码没有，则删除a表改行；如果a表编码没有b表有，则插入b表该行。

Answer 3

我写了存储过程如下：测试关系时间为固定时间，现在出现的问题是更新了前面两个，到了插入临时表发现没有起作用
create or replace procedure  vip_category22 (v_message out varchar2)
as
v_date date;
V_TRUNCATE_SQL  varchar2(500);begin
BEGIN
  v_message := '1';
 update gl_hy set NETCODE='' where  NETCODE is not null;
  --可以更新
  update gl_hy set  NETCODE='N'  where id IN (
    select DISTINCT A.vipid from vipbill a inner join gl_hy b on a.VIPID=b.id  where   (a.BILLDATE between TO_DATE('2018-01-10','YYYY-MM-DD')-181 and TO_DATE('2018-01-10','YYYY-MM-DD')-1)
     and (B.CREATEDATE between TO_DATE('2018-01-10','YYYY-MM-DD')-31 and TO_DATE('2018-01-10','YYYY-MM-DD')-1) 
      );
      commit;
      exception when others then
    v_message := sqlerrm;      END;
--可以更新
 BEGIN
   update gl_hy set  NETCODE='Z'  where id IN (
   select b.vipid 
     from ( select distinct a.vipid,max(to_char(a.BILLDATE,'yyyy-mm-dd')) rq from vipbill a where  a.BILLDATE between TO_DATE('2018-01-10','YYYY-MM-DD')-181 and TO_DATE('2018-01-10','YYYY-MM-DD')-1
             group by a.vipid) b 
    where ROUND(TO_DATE('2018-01-10','YYYY-MM-DD')-1-TO_DATE(b.RQ,'yyyy-mm-dd'))>=121
      );
      commit;
       exception when others then
    v_message := sqlerrm;      END;
 BEGIN
  --将信息插入临时表    
      INSERT INTO TEMPHYFJ(YHKH,JEFZ ,CS,QZFZ,TS,FZ )
       SELECT YHKH,JEFZ,CS,QZFZ,TS,
 (case when jefz+qzfz>=8 AND ts<=90  THEN 'A'  when jefz+qzfz>=5 and jefz+qzfz<8 AND ts<=90  THEN 'B' else 'C' end ) FZ
 FROM
 (
 select distinct a.yhkh, (case when A.JE>0 and A.JE<1000 then 1.2 when A.JE>=1000 and A.JE<3000 then 2.4 
   when A.JE>=3000 and A.JE<5000 then 3.6 when A.JE>=5000 and A.JE<10000 then 5.1
   when A.JE>=10000 then 6 else 0 end ) jefz, 
   A.CS cs,
   (case when a.cs>=1 and a.cs<=2 then 0.8 when a.cs>=3 and a.cs<=5 then 1.6 when a.cs>=6 and a.cs<=10 then 2.4
   when a.cs>=11 and a.cs<=15 then 3.4 when a.cs>16 then 4 else 0 end) qzfz,
    ROUND(TO_DATE('2018-01-10','YYYY-MM-DD')-1-TO_DATE(A.RQ,'yyyy-mm-dd')) ts
  FROM
    (
    select a.yhkh,max(to_char(a.kdrq,'yyyy-mm-dd')) rq,SUM(jshj) je,count(distinct lsh) cs from subfhd a
    where a.yhkh in (select distinct VIPID  from vipbill where  BILLDATE between TO_DATE('2018-01-10','YYYY-MM-DD')-121 and TO_DATE('2018-01-10','YYYY-MM-DD')-1)
    AND a.kdrq BETWEEN TO_DATE('2018-01-10','YYYY-MM-DD')-366 AND TO_DATE('2018-01-10','YYYY-MM-DD')-1
     group by a.yhkh
   ) A
   )FZ;
  
  update gl_hy set gl_hy.NETCODE=(SELECT TEMPHYFJ.FZ FROM TEMPHYFJ where TEMPHYFJ.YHKH=gl_hy.id);
  commit;
   exception when others then
    v_message := sqlerrm;      END;
  BEGIN
  V_TRUNCATE_SQL :='truncate table HYNTJ';
   execute immediate V_TRUNCATE_SQL;
   INSERT INTO HYNTJ (YHKH,NSLZD,NCSZD,NJEZD,NMLEZD,NMLLZD,GXDATE)
    select yhkh,
MAX( DECODE ( ZSL,1, SPXX||','||'数量:'||SL )) NSLZD,
MAX( DECODE ( ZPZ,1, SPXX||','||'次数:'||PZ )) NCSZD,
MAX( DECODE ( ZJE,1, SPXX||','||'金额:'||JE )) NJEZD,
MAX( DECODE ( ZML,1, SPXX||','||'毛利额:'||MLE )) NMLEZD,
MAX( DECODE ( ZMLL,1, SPXX||','||'毛利率:'||MLL||'%' )) NMLLZD,
SYSDATE GXDATE
from
( 
--统计并打标签
select yhkh, SPXX,SL,JE,(JE-SL*CBE) MLE,ROUND(( CASE WHEN JE=0 THEN 0 ELSE 100*((JE-sl*CBE ) /JE) END),2) MLL,COUNT(SPXX) PZ,row_number() OVER(PARTITION BY YHKH order by SL desc) ZSL,row_number() OVER(PARTITION BY YHKH order by je desc) ZJE,row_number() OVER(PARTITION BY YHKH order by COUNT(SPXX) desc) ZPZ
,row_number() OVER(PARTITION BY YHKH order by (JE-SL*CBE) desc) ZML,row_number() OVER(PARTITION BY YHKH order by ROUND(( CASE WHEN JE=0 THEN 0 ELSE 100*((JE-sl*CBE ) /JE) END),2) desc) ZMLL
from
( SELECT a.YHKH,(CASE WHEN ZL.NAME='ZZ' THEN 'ZZ' ELSE a.HH||'-'||b.pm END) SPXX,SUM(a.SL) SL ,SUM(a.JSHJ) JE,SUM(A.BatchPrice) CBE,A.LSH 
FROM SUBFHD a left join yw_kck b on a.hh=b.hh LEFT JOIN GL_HY C ON A.YHKH=C.ID
left join (Select Code,Name From Dataitem Where Dataitemtypeguid='313') zl on zl.code=b.FZFLSX2
WHERE  a.KDRQ BETWEEN TO_DATE('2018-01-10','YYYY-MM-DD')-365 AND TO_DATE('2018-01-10','YYYY-MM-DD') 
and C.NETCODE IS NOT NULL AND B.ISCOURTESYCARD !=1 AND A.HH != 'JFDX'
GROUP BY a.YHKH,(CASE WHEN ZL.NAME='ZZ' THEN 'ZZ' ELSE a.HH||'-'||b.pm END),LSH
) HZ group by yhkh, SPXX, SL, JE,(JE-SL*CBE)
) zd where  ZSL=1 OR ZPZ=1 OR ZJE=1 OR ZML=1 OR ZMLL=1 GROUP BY YHKH,SYSDATE;
COMMIT;
     exception when others then
    v_message := sqlerrm;      END;
    END;

Answer 4

-- 大概伪代码如下-- 删除 A 中存在，但表 B 中不存在的行
delete a where not exists(select * from B where a.id = b.id)
-- 更新 或 插入 B 的行
merge into A
using b (on a.id = b.id)
when matched then
  update set A.c1 = b.c1 , a.c2 = b.c2
when not matched then
  insert values(b.id, b.c1, b.c2)