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 ;