1.创建表

drop table if exists sequence;

create table sequence (

seq_name VARCHAR(50) NOT NULL, -- 序列名称

current_val INT NOT NULL DEFAULT 0, -- 当前值

increment_val INT NOT NULL DEFAULT 1, -- 步长(跨度)

PRIMARY KEY (seq_name) );

2. 插入数据

INSERT INTO sequence VALUES ('seq_test1_num1', '0', '1');

INSERT INTO sequence VALUES ('seq_test1_num2', '0', '2');

3.创建当前值

create function currval(v_seq_name VARCHAR(50))

returns integer

begin

declare value integer;

set value = 0;

select current_val into value from sequence where seq_name = v_seq_name;

return value;

end;

4.测试当前值

 

select currval('seq_test1_num1');

5.创建下一个值

create function nextval (v_seq_name VARCHAR(50))

returns integer

begin

update sequence set current_val = current_val + increment_val where seq_name = v_seq_name;

return currval(v_seq_name);

end;

6.测试下一个值

 

select nextval('seq_test1_num1');