MySQL實作類別似Oracle序列的函數

標籤：

drop table if exists sequence;
create table sequence (
seq_name VARCHAR(50) NOT NULL,
current_val INT NOT NULL,
increment_val INT NOT NULL DEFAULT 1,
PRIMARY KEY (seq_name)
);

drop function if exists currval;
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;

create function nextval(v_seq_name VARCHAR(50))
returns integer
begin
declare rowcount integer;

select count(1) into rowcount
from sequence
where seq_name = v_seq_name;

IF rowcount = 0 THEN
INSERT INTO sequence(seq_name,current_val)
VALUES(v_seq_name,0);
end IF;

update sequence
set current_val = current_val + increment_val
where seq_name = v_seq_name;
return currval(v_seq_name);

end;

MySQL實作類別似Oracle序列的函數