把A表的数据复制到B表 事务处理

CREATE OR REPLACE PROCEDURE proc_xop_test(v_supplier_id in number,updatesupplier_id in number) as

  V_SQL      VARCHAR2(4000);
  V_ERRCODE  NUMBER(20);
  V_ERRMSG   VARCHAR2(200);
  v_GOODS_ID varchar2(1000);

BEGIN
  select wm_concat(goods_id)
    into v_GOODS_ID
    from xiu_mop.X_MOP_GOODS t
   where t.supplier_id = v_supplier_id;

  V_SQL := '
insert into x_xop_goods
 
  select *
    from xiu_mop.X_MOP_GOODS
   where supplier_id = ' || v_supplier_id || '
     and Goods_id not in
         (select Goods_id from x_xop_goods where supplier_id = ' ||
           v_supplier_id || ') ';
  EXECUTE IMMEDIATE (V_SQL);
  
  
  V_SQL := 'update x_xop_goods  set supplier_id=' || updatesupplier_id || '  where goods_id in( select goods_id from  x_xop_goods t  WHERE supplier_id in ' ||v_supplier_id || ') ';
  
  EXECUTE IMMEDIATE (V_SQL);
  
  V_SQL := 'insert into X_XOP_GOODS_DETAIL select *
    from xiu_mop.X_MOP_GOODS_DETAIL
   where Goods_id in (' || v_GOODS_ID || ')
     and Goods_id not in (select Goods_id from X_XOP_GOODS_DETAIL) ';
  EXECUTE IMMEDIATE (V_SQL);

  V_SQL := 'insert into X_XOP_GOODS_IMG
  select *
    from xiu_mop.X_MOP_GOODS_IMG
   where  Goods_id in (' || v_GOODS_ID || ')
     and IMG_ID not in (select IMG_ID from X_XOP_GOODS_IMG)';
  EXECUTE IMMEDIATE (V_SQL);
 

  
  V_SQL := 'insert into X_XOP_GOODS_SKU
  select *
    from xiu_mop.x_mop_goods_sku
   where Goods_id in (' || v_GOODS_ID || ')
     and OBJ_ID not in (select OBJ_ID from X_XOP_GOODS_SKU)';

  EXECUTE IMMEDIATE (V_SQL);

  V_SQL := 'update X_XOP_GOODS_SKU  set supplier_id=' || updatesupplier_id || '  where goods_id in( select goods_id from  X_XOP_GOODS_SKU t  WHERE supplier_id in ' ||v_supplier_id || ') '; 
    
  EXECUTE IMMEDIATE (V_SQL);
 
  V_SQL := 'insert into X_XOP_GOODS_UPDATE_TEMP
  select *
    from  xiu_mop.X_MOP_GOODS_UPDATE_TEMP
    where
    Goods_id in (' || v_GOODS_ID || ')
     and  ID not in (select ID from X_XOP_GOODS_UPDATE_TEMP)';

  EXECUTE IMMEDIATE (V_SQL);
  
  
  V_SQL := 'update X_XOP_GOODS_UPDATE_TEMP  set supplier_id=' || updatesupplier_id || '  where goods_id in( select goods_id from  X_XOP_GOODS_UPDATE_TEMP t  WHERE supplier_id in ' ||v_supplier_id || ') '; 
  
  

  EXECUTE IMMEDIATE (V_SQL);
  COMMIT;
EXCEPTION
  --事物回滚后继续执行
  WHEN OTHERS THEN
    ROLLBACK;
    V_ERRCODE := SQLCODE;
    V_ERRMSG  := SUBSTR(SQLERRM, 1, 200);
    INSERT INTO QUERY_ERR_LOG
      (OPDATE, FUNCNAME, ERRCODE, ERRMSG, ERRSQL)
    VALUES
      (SYSDATE, 'proc_xop', V_ERRCODE, V_ERRMSG, V_SQL);
    COMMIT;
END proc_xop_test;