mysql存储过程 缓存_mysql存储过程怎么清除缓存以及 java for循环调用存储过程缓存机制...

mybatis里调用存储过程产生缓存

DELIMITER $$

USE `pnc_newmes`$$

DROP PROCEDURE IF EXISTS `pr_creatembarcode`$$

CREATE DEFINER=`devmes`@`%` PROCEDURE `pr_creatembarcode`(

woCode VARCHAR(40),

curData DATETIME,

OUT prCode VARCHAR(100),

OUT errorCode VARCHAR(100)

)

BEGIN

DECLARE WoCodes VARCHAR(40);

DECLARE MaVerGds VARCHAR(100);

DECLARE LineGds VARCHAR(100);

DECLARE LastLevels INT(11);

DECLARE LastModifyTimes DATETIME;

DECLARE SerialRuleGds VARCHAR(100);

DECLARE datas DATE;

DECLARE F1s VARCHAR(255) DEFAULT NULL;

DECLARE F2s VARCHAR(255) DEFAULT NULL;

DECLARE F3s VARCHAR(255) DEFAULT NULL;

DECLARE F4s VARCHAR(255)DEFAULT NULL;

DECLARE LineCodes VARCHAR(40);

DECLARE ys VARCHAR(10);

DECLARE ms VARCHAR(10);

DECLARE ds VARCHAR(10);

DECLARE WOGDs VARCHAR(100);

DECLARE t_error INTEGER DEFAULT 0;

SET @WoCodes=NULL,@t_error=0;

SELECT woCode INTO @WoCodes;

START TRANSACTION;

SET @MaVerGds=NULL,@WOGDs=NULL,prCode=NULL;

SELECT t1.MaVerGd,t1.Guid INTO @MaVerGds,@WOGDs FROM tpm_WoInfo t1  WHERE t1.woCode=@WoCodes;

SET @LineGds=NULL;

SELECT t2.LineGd INTO @LineGds FROM tpm_OrderLineInfo t2 WHERE t2.WOGD=@WOGDs;

IF @MaVerGds='' OR @MaVerGds IS NULL THEN

SELECT '物料版本不存在' INTO errorCode;

ELSEIF @LineGds='' OR @LineGds IS NULL THEN

SELECT '线体不存在' INTO errorCode;

ELSE

SET @F1s=NULL,@F2s=NULL,@F3s=NULL,@F4s=NULL;

SELECT t3.F1,t3.F2,t3.F3,t3.F4 INTO @F1s,@F2s,@F3s,@F4s FROM tpm_MaPropertyInfo t3 WHERE t3.MaVerGd=@MaVerGds;

SET @LastLevels=NULL,@LastModifyTimes=NULL,@SerialRuleGds=NULL;

SELECT t6.LastLevel,t6.LastModifyTime,t5.SerialRuleGd INTO @LastLevels,@LastModifyTimes,@SerialRuleGds

FROM tpm_MaVerInfo t5,tpm_SerialRuleInfo t6 WHERE  t5.Guid=@MaVerGds AND t6.Guid=t5.SerialRuleGd;

SET @LineCodes=NULL;

SELECT t4.LineCode INTO @LineCodes FROM tpm_LineInfo t4 WHERE t4.Guid=@LineGds;

SET @datas=NULL,@ys=NULL,@ms=NULL,@ds=NULL;

SET @datas=CURRENT_DATE;

SET @ys=DATE_FORMAT(@datas,'%y')+18;

SET @ms=DATE_FORMAT(@datas,'%m')+18;

SET @ds=DATE_FORMAT(@datas,'%d')+18;

SET @LastModifyTimes=DATE_FORMAT(@LastModifyTimes,'%Y-%m-%d');

IF @F1s='' OR @F1s IS NULL THEN

SELECT '晶硅类型及特殊电池制造技术不存在' INTO errorCode;

ELSEIF @F2s='' OR @F2s IS NULL THEN

SELECT '电池尺寸不存在' INTO errorCode;

ELSEIF @F3s='' OR @F3s IS NULL THEN

SELECT '导电芯板类型不存在' INTO errorCode;

ELSE

CASE @F4s

WHEN '00' THEN SET @F4s='T';

WHEN '01' THEN SET @F4s='X';

WHEN '02' THEN SET @F4s='Y';

WHEN '03' THEN SET @F4s='Z';

ELSE SET @F4s='0';

END CASE;

IF @LastModifyTimes!=@datas OR @LastModifyTimes IS NULL THEN

UPDATE tpm_SerialRuleInfo SET LastModifyTime=NOW(),LastLevel=1 WHERE Guid=@SerialRuleGds;

SET @LastLevels=0;

ELSE

UPDATE tpm_SerialRuleInfo SET LastModifyTime=NOW(),LastLevel=@LastLevels+1 WHERE Guid=@SerialRuleGds;

END IF;

SET @LastLevels=@LastLevels+1;

IF @LastLevels<=9 THEN

SELECT CONCAT(@F1s,@F2s,@F3s,@LineCodes,@ys,@ms,@ds,@F4s,'00',@LastLevels) INTO prCode;

SET @t_error=1;

ELSEIF @LastLevels<=99 THEN

SELECT CONCAT(@F1s,@F2s,@F3s,@LineCodes,@ys,@ms,@ds,@F4s,'0',@LastLevels) INTO  prCode;

SET @t_error=1;

ELSE

SELECT CONCAT(@F1s,@F2s,@F3s,@LineCodes,@ys,@ms,@ds,@F4s,@LastLevels) INTO prCode;

SET @t_error=1;

END IF;

END IF;

END IF;

IF @t_error =1 THEN

SELECT '1' INTO errorCode  ;

SELECT prCode,errorCode;

COMMIT;

ELSE

SELECT prCode,errorCode;

ROLLBACK;

END IF;

END$$

DELIMITER ;

可以看到每当给变量赋值的时候,先给变量赋值为null;不然传入参数错误,也会返回值,原因就是变量会存储之前调用的值;

还可以看到我在传入参数里有个curData,这个就是方便java循环调用的时候传入一个当前时间因为当前时间在不停的变动,而java循环用mybatis调用存储过程时,传入参数不变不管循环多少次,只会拿第一次调用的值,因为传入参数一样,mybatis只会调用一次存储过程,后续调用都会直接在sqlSession里取值的;