81,092
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE `sequence_pricing` (
name VARCHAR(40) NOT NULL, -- 编号名称
current_value INT NOT NULL DEFAULT 1, -- 当前值
increment INT NOT NULL DEFAULT 1, -- 步长(每次取值的增量)
PRIMARY KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE sequence_pricing ADD UNIQUE (name); -- 添加唯一索引
DELIMITER $$
DROP PROCEDURE IF EXISTS `nextval_pricing`$$
CREATE PROCEDURE `nextval_pricing`(IN seq_name VARCHAR(40),OUT result VARCHAR(40) )
BEGIN
DECLARE tsValue VARCHAR(40);
DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
START TRANSACTION;
-- 在同一个事物中,执行了update语句之后就会启动锁
UPDATE sequence_pricing SET current_value=current_value WHERE name=seq_name;
SELECT current_value INTO tsValue FROM sequence_pricing WHERE name=seq_name;
-- 因子表中没有记录,插入初始值
IF tsValue IS NULL THEN
insert into sequence_pricing(name,current_value,increment) values(seq_name, 1,1);
ELSE
UPDATE sequence_pricing SET current_value = current_value + increment WHERE name = seq_name;
END IF;
SELECT current_value INTO result FROM sequence_pricing WHERE name=seq_name;
IF t_error =1 THEN
ROLLBACK;
SET result = 'Error';
ELSE
COMMIT;
END IF;
SELECT result ;
END$$
DELIMITER ;