存储过程实现流水号
实现效果:SHHP201806080002
前缀编码--prefixStr 'SHHP'
后缀编码长度-- suffixCodeLength 长度4 (0002)
日期字符-- 20180608
先准备一张表 express_code
需要一个字段 product_code
CREATE PROCEDURE `generate_code`(IN prefixStr varchar(10), IN suffixCodeLength int(10), OUT newCode varchar(50))
BEGIN
DECLARE currentDate VARCHAR(14);
-- 当前日期字符串
DECLARE suffixCode INT DEFAULT 0;
-- 后缀编码
DECLARE oldCode VARCHAR(30) DEFAULT '';
-- 上一个编码
SELECT DATE_FORMAT(NOW(), '%Y%m%d') INTO currentDate ;
-- 格式化日期,如'20180608'
SELECT IFNULL(product_code, '') INTO oldCode
FROM express_info
WHERE SUBSTRING(product_code, 1, LENGTH(currentDate) + LENGTH(prefixStr)) = CONCAT(prefixStr,currentDate)
AND LENGTH(product_code) = LENGTH(prefixStr) + LENGTH(currentDate) + suffixCodeLength
ORDER BY product_code DESC LIMIT 1 ;
-- 查询上一个code
IF oldCode != ''
THEN
SET suffixCode = CONVERT(SUBSTRING(oldCode, -suffixCodeLength), DECIMAL) ;
END IF ;
-- 后缀编码转换为数字,如果前面有0005,则去除前面的0
SELECT CONCAT(prefixStr, currentDate, LPAD((suffixCode + 1), suffixCodeLength, '0')) INTO newCode ;
-- 后缀编码加1,并进行拼接
SELECT newCode ;
END
复制代码
只有一个输出参数时:
<select id="getExpressCode" resultType="string" statementType="CALLABLE">
call generate_productCode(?)
</select>
// mapper 接口
String getExpressCode();
// service
public String getExpressCode() {
String expressCode = expressInfoMapper.getExpressCode();
return expressCode;
}
带输入、输出参数时
// 参数封装到实体类
@Data
public class CodeRule {
private String prefixString;
private String code;
private int suffixCodeLength;
}
<select id="getExpressCode" resultType="string" parameterType="com.peng.express.entity.CodeRule" statementType="CALLABLE">
call generate_code(
)
</select>
String getExpressCode(CodeRule codeRule);
public String getExpressCode() {
CodeRule codeRule = new CodeRule();
codeRule.setPrefixString("XSBH");
codeRule.setSuffixCodeLength(4);
// 查询结束后,结果自动回填到codeRule的code属性
String expressCode = expressInfoMapper.getExpressCode(codeRule);
return expressCode;
}
复制代码