简单存储过程

导入数据时,判断格式错误,重复数据

 

CREATE DEFINER=`root`@`%` PROCEDURE `p_impt_order`(IN `p_uid` int,OUT `out_rst` int)
BEGIN
    
    set out_rst = 0;

  start TRANSACTION;
  -- 导入失败
    update eph_ispc_order_temp set impt_flg = 1
    where crt_uid = p_uid
    and LENGTH(mchnt_cd) > 15;
commit;

start TRANSACTION;
  -- 导入重复
  update eph_ispc_order_temp a set impt_flg = 2
where crt_uid = p_uid
  and CONCAT(IFNULL(mchnt_cd,''),IFNULL(term_id,'')) IN
(select CONCAT(IFNULL(mchnt_cd,''),IFNULL(term_id,''))
   from eph_ispc_order_his b where a.crt_dept_cd = b.crt_dept_cd);
commit;

start TRANSACTION;
 update eph_ispc_order_temp set impt_flg = 3
    where crt_uid = p_uid
    and impt_flg is null;
commit;
 set out_rst = 1;

END