msql 实现sequence功能增强
create table sequence (
seq_name VARCHAR(50) NOT NULL COMMENT '序列名称',
min_val INT UNSIGNED NOT NULL COMMENT '最小值',
max_val INT UNSIGNED NOT NULL COMMENT '最大值',
if_cycle INT UNSIGNED NOT NULL COMMENT '是否循环',
if_use INT UNSIGNED NOT NULL COMMENT '是否使用中:0:未使用,1:已使用',
current_val INT UNSIGNED NOT NULL COMMENT '当前值',
increment_val INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '步长(跨度)',
PRIMARY KEY (seq_name)
);
delimiter $$
create function currval(v_seq_name VARCHAR(50))
returns integer
begin
declare v_curr_val integer;
set v_curr_val := 0;
select current_val into v_curr_val
from sequence
where seq_name = v_seq_name;
return v_curr_val;
end$$
create function nextval(v_seq_name VARCHAR(50))
returns INTEGER
begin
declare v_curr_val INTEGER;
declare v_increment_val INTEGER;
declare v_if_cycle INTEGER;
declare v_next_val INTEGER;
declare v_min_val INTEGER;
declare v_max_val INTEGER;
update sequence set if_use = 1 where seq_name = v_seq_name;
select current_val, min_val, max_val, increment_val, if_cycle
into v_curr_val, v_min_val, v_max_val, v_increment_val, v_if_cycle from sequence where seq_name = v_seq_name;
set v_next_val := v_curr_val + v_increment_val;
if (v_next_val > v_max_val) and (v_if_cycle = 0) then
set v_next_val := -1;
else
set v_next_val := mod(v_next_val, v_max_val + 1);
IF v_next_val < v_min_val then
set v_next_val := v_min_val;
end if;
update sequence set current_val = v_next_val where seq_name = v_seq_name;
end if;
update sequence set if_use = 0 where seq_name = v_seq_name;
return v_next_val;
end$$
create function setval(v_seq_name VARCHAR(50), v_new_val INTEGER)
returns integer
begin
declare v_min_val int;
declare v_max_val int;
update sequence set if_use = 1 where seq_name = v_seq_name;
select min_val, max_val into v_min_val, v_max_val from sequence where seq_name = v_seq_name;
if (v_new_val > v_max_val) or (v_new_val < v_min_val) then
set v_new_val := -1;
else
update sequence set current_val = v_new_val where seq_name = v_seq_name;
end if;
update sequence set if_use = 0 where seq_name = v_seq_name;
return v_new_val;
end$$
delimiter ;