Howto: Oracle Sequences as MySQL stored procedures.

[Michael Cole]

I don't want to rewrite my PHP ADODB Oracle application. The code pattern for sequences is different than autonumbers.

Instead, I'd like to duplicate the sequences functionality with the stored procedures and functions below. Is this a valid approach? It's a small application so I'm not worried much about performance (10-30 concurrent users).

Here's the code. See the bottom for tests.

Thanks!

Mike

<pre><code>
DROP TABLE IF EXISTS sequences;
CREATE TABLE sequences (name varchar(32), currval BIGINT UNSIGNED) ENGINE=InnoDB; #row-level locking

DELIMITER //
DROP PROCEDURE IF EXISTS drop_sequence//
CREATE PROCEDURE drop_sequence (IN the_name text)
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
DELETE FROM sequences WHERE name=the_name;
END;//


DROP PROCEDURE IF EXISTS create_sequence//
CREATE PROCEDURE create_sequence (IN the_name varchar(32))
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
DELETE FROM sequences WHERE name=the_name;
INSERT INTO sequences VALUES(the_name, 0);
END; //

DROP FUNCTION IF EXISTS nextval//
CREATE FUNCTION nextval (the_name varchar(32))
RETURNS BIGINT UNSIGNED
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
DECLARE ret BIGINT UNSIGNED;
UPDATE sequences SET currval=currval+1 WHERE name=the_name;
SELECT currval INTO ret FROM sequences WHERE name=the_name limit 1;
RETURN ret;
END; //

DROP FUNCTION IF EXISTS currval//
CREATE FUNCTION currval (the_name varchar(32))
RETURNS BIGINT UNSIGNED
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE ret BIGINT UNSIGNED;
SELECT currval INTO ret FROM sequences WHERE name=the_name limit 1;
RETURN ret;
END; //

DROP FUNCTION IF EXISTS setval//
CREATE FUNCTION setval (the_name varchar(32), the_val BIGINT UNSIGNED)
RETURNS BIGINT UNSIGNED
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
DECLARE ret BIGINT UNSIGNED;
UPDATE sequences SET currval=the_val WHERE name=the_name;
SELECT currval INTO ret FROM sequences WHERE name=the_name limit 1;
RETURN ret;
END; //

DELIMITER ;


# tests

# simple test
call create_sequence('bar'); # 0
select nextval('bar'); # 1
select currval('bar'); # 1
select nextval('bar'); # 2
select nextval('bar'); # 3

select nextval('null'); # null, sequence not created.

call create_sequence('foo'); # 0
SELECT COUNT(*) INTO @val FROM mysql.proc; # set to the number of mysql stored procedures.
select setval('foo', @val); # greater than 5
select currval('foo'); # same as previous

call drop_sequence('foo'); # ok.
select currval('foo'); # null

call create_sequence('foo');
select currval('foo'); # 0
</code></pre>



Edited 1 time(s). Last edit at 03/09/2008 12:23PM by Michael Cole.