MySQL模拟Oracle序列sequence

DROP TABLE IF EXISTS sys_sequence ;

CREATE TABLE sys_sequence (
  seq_name VARCHAR (50) NOT NULL,
  curr_value BIGINT NOT NULL DEFAULT 0,
  increment_by INT NOT NULL DEFAULT 1,
  PRIMARY KEY (seq_name)
) ENGINE = INNODB ;

INSERT INTO sys_sequence VALUES ('SEQ_TEST_NO',10000,1);



DELIMITER $$DROP FUNCTION IF EXISTS currval$$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    FUNCTION currval(v_seq_name VARCHAR (50))
    RETURNS BIGINT
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
    DECLARE v_currval BIGINT;
      SET v_currval = 1 ;
      SELECT
        curr_value INTO v_currval
      FROM
        sys_sequence
      WHERE seq_name = v_seq_name ;
      RETURN v_currval ;
    END $$DELIMITER ; -- SELECT `currval`('SEQ_TEST_NO'); DELIMITER$$
DROP FUNCTION IF EXISTS `nextval` $$CREATE FUNCTION `nextval` (`v_seq_name` VARCHAR (50)) RETURNS BIGINT (20) CONTAINS SQL BEGIN   UPDATE     sys_sequence   SET     `curr_value` = last_insert_id(`curr_value` + `increment_by`)   WHERE `seq_name` = v_seq_name ;   RETURN last_insert_id(); END$$

DELIMITER ;