/*
MySQL Data Transfer
Source Host: localhost
Source Database: gemian
Target Host: localhost
Target Database: gemian
Date: 2012/12/6 16:04:51
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for getsn_config
-- ----------------------------
CREATE TABLE `getsn_config` (
`CATEGORY` varchar(50) NOT NULL,
`PREFIX` varchar(10) NOT NULL default '''''',
`POSTFIX` varchar(10) NOT NULL default '''''',
`PREFIX_DATESTR_LEN` tinyint(4) NOT NULL,
`CYCLE_WAY` varchar(20) NOT NULL,
`CYCLE_VALUE` varchar(10) NOT NULL default '1',
`LENGTH` tinyint(4) NOT NULL,
`SN` bigint(20) NOT NULL default '0',
`RESULT` varchar(50) NOT NULL default '''''',
PRIMARY KEY (`CATEGORY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `getsn_config` VALUES ('SellerSN', 'Sel', '', '12', 'MINUTE', '4', '20', '14', 'Sel20121206160400014');
---------------------------------
DELIMITER //
#获取流水号
DROP PROCEDURE IF EXISTS GETSN;
CREATE PROCEDURE GETSN(category VARCHAR(20), out out_sn VARCHAR(20) )
BEGIN
declare DATESTR VARCHAR(20);
declare LEN INT;
declare VAL VARCHAR(20);
START TRANSACTION;
SELECT left(date_format(NOW(),'%Y%m%d%H%i%s'),PREFIX_DATESTR_LEN),
LENGTH-LENGTH(PREFIX)-LENGTH(POSTFIX)-PREFIX_DATESTR_LEN,
case CYCLE_WAY
WHEN 'YEAR' THEN YEAR(NOW())
WHEN 'MONTH' THEN MONTH(NOW())
WHEN 'DAY' THEN DAY(NOW())
WHEN 'HOUR' THEN HOUR(NOW())
WHEN 'MINUTE' THEN MINUTE(NOW())
WHEN 'WEEK' THEN WEEK(NOW())
ELSE '' END
INTO DATESTR, LEN,VAL
FROM GETSN_CONFIG WHERE CATEGORY=category;
UPDATE GETSN_CONFIG SET
SN=(CASE WHEN CYCLE_VALUE=VAL THEN SN+1 ELSE 1 END), CYCLE_VALUE=VAL,
RESULT=CONCAT(PREFIX,DATESTR,POSTFIX,RIGHT(CONCAT('00000000000000000000',SN),LEN))
WHERE CATEGORY=category;
/*
// UPDATE GETSN_CONFIG SET
// RESULT=CONCAT(PREFIX,DATESTR,POSTFIX,RIGHT(CONCAT('00000000000000000000',SN),LEN))
// WHERE CATEGORY=category;
*/
SELECT RESULT INTO out_sn FROM GETSN_CONFIG
WHERE CATEGORY=category;
COMMIT;
END
//
DELIMITER ;
-------------------
- 1
- 2
前往页