MySQL Forums
Forum List  »  Oracle

Re: How to generate sequence in MySql
Posted by: Steve Pham
Date: December 14, 2008 07:08AM

following code can be a simple implementation for sequence.

create table seqs (seq_name varchar(30),start int, increment int, curval int,flag char(1));
insert into seqs values('test',1,1,1,'A');
flag is for multiuser enviroment
flag:A means available to update
flag:N means other session holding lock on sequence, not available to update/increase sequence value.

CREATE FUNCTION `nextval`(v_seq_name varchar(30))
RETURNS INT
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
begin
declare v_flag char(1);
declare v_nextval int;
set v_flag='N';
while (v_flag='N') do
select flag into v_flag from seqs where seq_name=v_seq_name;
end while;

update seqs set flag='N' where seq_name=v_seq_name;
select curval+INCREMENT into v_nextval from seqs where seq_name=v_seq_name;
update seqs set flag='A',curval=v_nextval where seq_name=v_seq_name;
return v_nextval;
end

CREATE FUNCTION `curval`(v_seq_name varchar(30))
RETURNS INT
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
begin
declare v_curval int;

select curval into v_curval from seqs where seq_name=v_seq_name;

return v_curval;
end

CREATE PROCEDURE `seq_reset`(v_seq_name varchar(30))
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
update seqs set curval=start,flag='A' where seq_name=v_seq_name;
END;

Options: ReplyQuote


Subject
Views
Written By
Posted
166438
March 13, 2007 08:05AM
52615
March 26, 2007 09:31PM
30490
April 04, 2007 03:00AM
26104
November 21, 2007 03:50AM
17322
February 06, 2008 12:16PM
18013
October 15, 2008 01:03PM
12061
November 05, 2008 09:50AM
13860
November 10, 2008 06:32AM
Re: How to generate sequence in MySql
14904
December 14, 2008 07:08AM
17683
January 23, 2009 05:38PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.