mSQL for sequence Enhancements

CREATE TABLE Sequence (
Seq_name VARCHAR () not NULL COMMENT ' sequence name ',
Min_val INT UNSIGNED not NULL COMMENT ' minimum value ',
Max_val INT UNSIGNED not NULL COMMENT ' maximum value ',
If_cycle INT UNSIGNED not NULL COMMENT ' Loop ',
If_use INT UNSIGNED not NULL COMMENT ' is in use: 0: Unused, 1: Used ',
Current_val INT UNSIGNED not NULL COMMENT ' current value ',
Increment_val INT UNSIGNED not NULL DEFAULT 1 COMMENT ' stride (span) ',
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 (), 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;

mSQL for sequence Enhancements