Monday, October 2, 2017

how to get sequence int value by name in mysql

DELIMITER $$

CREATE FUNCTION `seqval` (`seq_name` VARCHAR(100))
RETURNS BIGINT(20) NOT DETERMINISTIC
BEGIN
    DECLARE cur_val bigint(20);

SELECT intval INTO cur_val FROM seqcontract
WHERE name = seq_name;

IF cur_val IS NULL THEN
SET cur_val = 1;

INSERT seqcontract(name, intval)
VALUES(seq_name, cur_val);
ELSE
SET cur_val = cur_val + 1;

UPDATE seqcontract
SET intval = cur_val
WHERE name = seq_name;
END IF;

RETURN cur_val;
END$$

No comments: