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里取值的;