56,679
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE `applies` (
`apply_id` varchar(100) NOT NULL DEFAULT '',
`apply_no` varchar(100) NOT NULL DEFAULT '',
`district_cd` varchar(100) DEFAULT NULL,
`created` datetime DEFAULT NULL,
PRIMARY KEY (`apply_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `sequence` (
`district_cd` varchar(3) NOT NULL DEFAULT '',
`current_value` int(11) NOT NULL DEFAULT '0',
`apply_date` date NOT NULL DEFAULT '0000-00-00',
PRIMARY KEY (`district_cd`,`current_value`,`apply_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER ;;
CREATE DEFINER=`usr`@`%` FUNCTION `currval`(d VARCHAR(3)) RETURNS int(11)
DETERMINISTIC
BEGIN
DECLARE value INTEGER;
DECLARE needInitSequence INTEGER;
DECLARE today DATE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET needInitSequence = 1;
SET value = 0;
SET today = current_date();
SELECT `current_value` INTO value
FROM `sequence`
WHERE `district_cd` = d AND `apply_date` = today limit 1;
IF needInitSequence = 1 THEN
INSERT INTO `sequence` (`district_cd`, `current_value`, `apply_date`) VALUES (d, value, today);
END IF;
RETURN value;
END
;;
DELIMITER ;
DELIMITER ;;
CREATE DEFINER=`usr`@`%` FUNCTION `nextval`(d VARCHAR(3)) RETURNS varchar(16) CHARSET utf8
DETERMINISTIC
BEGIN
DECLARE value INTEGER;
SET value = currval(d);
UPDATE `sequence`
SET `current_value` = `current_value` + 1
WHERE `district_cd` = d AND `apply_date` = current_date();
RETURN concat(d, date_format(now(), '%Y%m%d'), LPAD(currval(d), 5, '0'));
END
;;
DELIMITER ;
DELIMITER ;;
CREATE TRIGGER `convert_long_no` BEFORE INSERT ON `applies` FOR EACH ROW BEGIN
IF ((SELECT LENGTH(NEW.apply_no)) >= 18) THEN
SET NEW.apply_no = (SELECT nextval(NEW.district_cd));
END IF;
END
;;
DELIMITER ;