all,
I have need to simulate the somewhat unique behavior/properties of an Oracle
sequence object in MySQL (obviously for porting legacy code).
I have come up w/the following solution which seems to work but wanted to
run it by the collective to see if I failed to consider anything:
create table sequence_name (
currval bigint unsigned
)
engine=myisam
;
insert into sequence_name values(0);
#add insert/delete triggers to prevent insert/delete additional rows - not
worried about that for testing
delimiter //
create function sequence_name (i_pseudo char(7))
returns bigint unsigned
modifies sql data
deterministic
begin
declare o_return bigint unsigned;
declare v_lock tinyint unsigned;
select get_lock('sequence_name', 10) into v_lock;
if i_pseudo='nextval' then
update sequence_name set currval=currval+1;
end if;
select currval into o_return from sequence_name limit 1;
select release_lock('sequence_name') into v_lock;
return o_return;
end;
//
delimiter ;
select sequence_name('nextval');
#returns sequence_name.currval++ - tested/works
select sequence_name('currval');
#returns sequence_name.currval - tested/works
can see any potential bugs w/this approach?
thanks!
