问题描述

mysql执行存储过程时自动停止

DELIMITER $$

DROP PROCEDURE IF EXISTS generatorDataCopy $$

CREATE PROCEDURE generatorDataCopy(inpid VARCHAR(50),OUT msg VARCHAR(50))
BEGIN

DECLARE err INT DEFAULT 0;
-- 如果出现sql异常，则将err设置为1后继续执行后面的操作
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err=1; -- 出错处理  

SET autocommit = 0;
START TRANSACTION;

INSERT INTO tbl_outhosroom_sendchdom(SCID,PLID,MEID,ECIPUCODE,OUTUCODE,AMOUNT,CURRENTINHOSMARK,DOSE,DOSEUNIT,ECIPEOPERATEDATE,PAYMONEY,UNITPRICE,OUTTIME) SELECT DISTINCT rec.SCID,rec.PLID,rec.MEID,rec.ECIPUCODE,rec.OUTUCODE,rec.AMOUNT,rec.CURRENTINHOSMARK,rec.DOSE,rec.DOSEUNIT,rec.ECIPEOPERATEDATE,rec.PAYMONEY,rec.UNITPRICE,rec.OUTTIME FROM tbl_room_sendchdom rec LEFT JOIN tbl_inhos_medord_exec exec ON rec.meid = exec.meid LEFT JOIN tbl_inhos_medord med ON exec.medordid = med.medordid WHERE med.pid=inpid;
INSERT INTO tbl_outhosroom_recedechdom(RCID,SCID,MEID,PLID,UCODE,OPERUCODE,AMOUNT,CURRENTINHOSMARK,DOSE,DOSEUNIT,PAYMONEY,UNITPRICE,OUTTIME) SELECT DISTINCT rec.RCID,rec.SCID,rec.MEID,rec.PLID,rec.UCODE,rec.OPERUCODE,rec.AMOUNT,rec.CURRENTINHOSMARK,rec.DOSE,rec.DOSEUNIT,rec.PAYMONEY,rec.UNITPRICE,rec.OUTTIME FROM tbl_room_recedechdom rec LEFT JOIN tbl_room_sendchdom send ON rec.scid = send.scid LEFT JOIN tbl_inhos_medord_exec exec ON send.meid = exec.meid LEFT JOIN tbl_inhos_medord med ON exec.medordid = med.medordid WHERE med.pid=inpid;

下面是若干条insert语句......

DELETE rec FROM tbl_inhos_refund_record rec LEFT JOIN tbl_inhos_medord_exec exec ON rec.meid = exec.meid LEFT JOIN tbl_inhos_medord med ON exec.medordid = med.medordid WHERE med.pid=inpid;    

DELETE melist FROM tbl_inhos_medord_list melist LEFT JOIN tbl_inhos_medord med ON melist.medordid = med.medordid WHERE med.pid=inpid;    

下面是若干条delete语句......

IF err=1 THEN
    ROLLBACK;
    SET msg = 'fail';
ELSE
    COMMIT;
    SET msg = 'succ';
END IF;

END$$

DELIMITER ;

这是一个数据搬移的存储过程，因为涉及的表比较多，每个人的数据只能通过人的主键去关联的搬移相应的数据。有些表的数据量比较大，所以在连连续搬移的时候就会出现错误码：2013这个错误。我想问的是需要调整那些参数能解决这个问题啊？