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 DROPFUNCTIONIFEXISTScurrval
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 ;
ENDDELIMITER;−−SELECT‘currval‘(′SEQTESTNO′);DELIMITER
DROP FUNCTION IF EXISTS `nextval` CREATEFUNCTION‘nextval‘(‘vseqname‘VARCHAR(50))RETURNSBIGINT(20)CONTAINSSQLBEGINUPDATEsyssequenceSET‘currvalue‘=lastinsertid(‘currvalue‘+‘incrementby‘)WHERE‘seqname‘=vseqname;RETURNlastinsertid();END
DELIMITER ;
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 DROPFUNCTIONIFEXISTScurrval
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 ;
ENDDELIMITER;−−SELECT‘currval‘(′SEQTESTNO′);DELIMITER
DROP FUNCTION IF EXISTS `nextval` CREATEFUNCTION‘nextval‘(‘vseqname‘VARCHAR(50))RETURNSBIGINT(20)CONTAINSSQLBEGINUPDATEsyssequenceSET‘currvalue‘=lastinsertid(‘currvalue‘+‘incrementby‘)WHERE‘seqname‘=vseqname;RETURNlastinsertid();END
DELIMITER ;