MYSQL에서 ORACLE SEQUENCE 사용하기

Table Scheme

CREATE TABLE sequences ( name varchar(32), currval BIGINT UNSIGNED ) ENGINE=InnoDB;

procedure

DELIMITER $$

CREATE PROCEDURE `drop_sequence` (IN the_name text)

MODIFIES SQL DATA

DETERMINISTIC

BEGIN

    DELETE FROM sequences WHERE name=the_name;

END;

DELIMITER $$

CREATE PROCEDURE `create_sequence`(IN the_name text)

MODIFIES SQL DATA

DETERMINISTIC

BEGIN

    DELETE FROM sequences WHERE name=the_name;

    INSERT INTO sequences VALUES (the_name, 0);

END;    

function

DELIMITER $$

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;

DELIMITER $$

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;

사용 예)

call create_sequence('bar');

select nextval('bar'); #1

select currval('bar'); #1

select nextval('bar'); #2

select currval('bar'); #2